On Tue, 27 Aug 2002, Will K. wrote:
> hello,
>
> Please dont tell me to RTFM on this one (cause I am already doing that), but
> for clarity's sake... can someone tell me what it means when you use NULL
> and NOT NULL in a query (specifically CREATE TABLE)? Also, when should I
> use them?
Will,
let's consider this:
an UNSIGNED TINYINT may have any value from 0 ... 255
setting it to NULL is different from 0. It is sort of a 256th possible
state. This is normally used to indicate that no value at all has been
assigned, not even '0'. For strings it is the same.
Thomas
--
BTW: (from the manual)
`NULL' Values
.............
The `NULL' value means "no data" and is different from values such as
`0' for numeric types or the empty string for string types. *Note
Problems with `NULL': Problems with NULL.
`NULL' may be represented by `\N' when using the text file import or
export formats (`LOAD DATA INFILE', `SELECT ... INTO OUTFILE'). *Note
`LOAD DATA': LOAD DATA.
Problems with `NULL' Values
---------------------------
The concept of the `NULL' value is a common source of confusion for
newcomers to SQL, who often think that `NULL' is the same thing as an
empty string `""'. This is not the case! For example, the following
statements are completely different:
mysql> INSERT INTO my_table (phone) VALUES (NULL);
mysql> INSERT INTO my_table (phone) VALUES ("");
Both statements insert a value into the `phone' column, but the first
inserts a `NULL' value and the second inserts an empty string. The
meaning of the first can be regarded as "phone number is not known" and
the meaning of the second can be regarded as "she has no phone".
In SQL, the `NULL' value is always false in comparison to any other
value, even `NULL'. An expression that contains `NULL' always produces
a `NULL' value unless otherwise indicated in the documentation for the
operators and functions involved in the expression. All columns in the
following example return `NULL':
mysql> SELECT NULL,1+NULL,CONCAT('Invisible',NULL);
If you want to search for column values that are `NULL', you cannot use
the `=NULL' test. The following statement returns no rows, because
`expr = NULL' is FALSE, for any expression:
mysql> SELECT * FROM my_table WHERE phone = NULL;
To look for `NULL' values, you must use the `IS NULL' test. The
following shows how to find the `NULL' phone number and the empty phone
number:
mysql> SELECT * FROM my_table WHERE phone IS NULL;
mysql> SELECT * FROM my_table WHERE phone = "";
Note that you can only add an index on a column that can have `NULL'
values if you are using MySQL Version 3.23.2 or newer and are using the
`MyISAM' or `InnoDB' table type. In earlier versions and with other
table types, you must declare such columns `NOT NULL'. This also means
you cannot then insert `NULL' into an indexed column.
When reading data with `LOAD DATA INFILE', empty columns are updated
with `'''. If you want a `NULL' value in a column, you should use `\N'
in the text file. The literal word `'NULL'' may also be used under some
circumstances. *Note `LOAD DATA': LOAD DATA.
When using `ORDER BY', `NULL' values are presented first. If you sort
in descending order using `DESC', `NULL' values are presented last.
When using `GROUP BY', all `NULL' values are regarded as equal.
To help with `NULL' handling, you can use the `IS NULL' and `IS NOT
NULL' operators and the `IFNULL()' function.
For some column types, `NULL' values are handled specially. If you
insert `NULL' into the first `TIMESTAMP' column of a table, the current
date and time is inserted. If you insert `NULL' into an
`AUTO_INCREMENT' column, the next number in the sequence is inserted.
Working with `NULL' Values
..........................
The `NULL' value can be surprising until you get used to it.
Conceptually, `NULL' means missing value or unknown value and it is
treated somewhat differently than other values. To test for `NULL',
you cannot use the arithmetic comparison operators such as `=', `<', or
`<>'. To demonstrate this for yourself, try the following query:
mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;
+----------+-----------+----------+----------+
| 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL |
+----------+-----------+----------+----------+
| NULL | NULL | NULL | NULL |
+----------+-----------+----------+----------+
Clearly you get no meaningful results from these comparisons. Use the
`IS NULL' and `IS NOT NULL' operators instead:
Clearly you get no meaningful results from these comparisons. Use the
`IS NULL' and `IS NOT NULL' operators instead:
mysql> SELECT 1 IS NULL, 1 IS NOT NULL;
+-----------+---------------+
| 1 IS NULL | 1 IS NOT NULL |
+-----------+---------------+
| 0 | 1 |
+-----------+---------------+
Note that two `NULL' are compared as equal is when you do an `GROUP BY'.
In MySQL, 0 or `NULL' means false and anything else means true. The
default truth value from a boolean operation is 1.
When doing an `ORDER BY', `NULL' values are always sorted first, even
if you are using `DESC'.
This special treatment of `NULL' is why, in the previous section, it
was necessary to determine which animals are no longer alive using
`death IS NOT NULL' instead of `death <> NULL'.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
