Discussion on:
View:
Show:
I can see why the confusions arises. Just how does one express an "empty column/field" instead of a column/field with an unknown value. Does "unknown" mean that the field has something in it, but just not known? I am sure the Access online help(less) file totally ignores this problem and, certainly, does not give examples in an attempt to clarify all the verbiage that is given.
You have to adopt a strategy and employ it consistently. Either don't permit nulls from the get-go or be sure to accommodate the possibility of null values in all expressions and queries. I'm sure there are pros and cons to both strategies.
Think of it this way, all fields have a value. NULL is not an unknown value. It has the value of NULL which is an exact value. This is where #6 is very important. SQL is an industry standard (each RDBMS has their own dialect) and it is clearer if you can understand it. e.g.
select *
from stuff
where order_quantity is null
Depending upon the DBMS the "where" clause might be:
where order_quantity = null
What this is saying is retrieve all fields in the table named "stuff" where the order quantity is null. This is not 0 (zero) it has an exact value of null. 0 as a value has an exact mathematical value of 0 and therefore not null. A 0 (zero) as a character has an code value depending upon which character set you are using. e.g. ASCII "0" has a value of hexadecimal 30, decimal 48. A " " (space) has an ASCII value of 20 hex. An empty field is the only one that has what is close to "unknown value" but it really has not yet been determined but for some RDBMSes, you can select where a particular field is empty.
Perhaps the only confusing thing is that ASCII code 0 which is defined as the NULL character, is not the same as the value of NULL. The easiest way to remember is that an ASCII NULL (code 0) can be printed (it will print nothing and I think this is where the confusion lies), but an ASCII NULL is not the RDBMS NULL. So if you insert an ASCII NULL (code 0) into a previously NULL or empty field, it is no longer NULL nor is it empty. It will have a value of 0.
select *
from stuff
where order_quantity is null
Depending upon the DBMS the "where" clause might be:
where order_quantity = null
What this is saying is retrieve all fields in the table named "stuff" where the order quantity is null. This is not 0 (zero) it has an exact value of null. 0 as a value has an exact mathematical value of 0 and therefore not null. A 0 (zero) as a character has an code value depending upon which character set you are using. e.g. ASCII "0" has a value of hexadecimal 30, decimal 48. A " " (space) has an ASCII value of 20 hex. An empty field is the only one that has what is close to "unknown value" but it really has not yet been determined but for some RDBMSes, you can select where a particular field is empty.
Perhaps the only confusing thing is that ASCII code 0 which is defined as the NULL character, is not the same as the value of NULL. The easiest way to remember is that an ASCII NULL (code 0) can be printed (it will print nothing and I think this is where the confusion lies), but an ASCII NULL is not the RDBMS NULL. So if you insert an ASCII NULL (code 0) into a previously NULL or empty field, it is no longer NULL nor is it empty. It will have a value of 0.
A very handy tool for data integrity is indexes. Construct additional compound indices (using multiple fields, not just one) and make the index unique. (Duplicates not allowed)
For example, a names list might have several [lastname] = Smith and several [firstname] = John, but with a compound index you can make sure there is only one "John Q. Smith" using [firstname] [middlename] [lastname] as an additional unique index.
For example, a names list might have several [lastname] = Smith and several [firstname] = John, but with a compound index you can make sure there is only one "John Q. Smith" using [firstname] [middlename] [lastname] as an additional unique index.
- Keyboard Shortcuts:
- Prev
- Next
- Toggle
