A common confusion for newcomers to SQL is that they think that NULL is the same things as a empty string ". This is not the case! For example the following statements are compleatly different:
INSERT INTO my_table (name,phone) values ("my",NULL);
INSERT INTO my_table (name,phone) values ("my","");
The first inserts a NULL value into address and the second inserts an empty string into address. The first can be regarded as 'phone number is not known' and the second can be regarded as 'she has no phone'.
In SQL NULL is a value that is always false when comparing to all other values, even NULL. An expression that contains NULL will always produce a NULL value if nothing else is mentioned. All following columns return NULL:
SELECT NULL,1+NULL,CONCAT('Invisible',NULL);
If you want to search for a column that has NULL, you must use the IS NULL test. The following shows how to find the NULL phone number and the empty phone number:
SELECT * from my_table where phone IS NULL; SELECT * from my_table where phone = "";
In MySQL, as in many other SQL servers, you can't index
columns that can have NULL values. You have to declare such columns
NOT NULL.
When reading data with LOAD DATA INFILE empty columns are updated
with ". If you want a NULL value in a column you should use NULL or \N in
the textfile.
When using ORDER BY NULL values are presented first (if not using DESC).
When using GROUP BY all NULL values are regarded as equal.
To help with NULL handling you can use the functions: IS NULL,
IS NOT NULL and IFNULL()
Go to the first, previous, next, last section, table of contents.