This MySQL tutorial explains how to use the MySQL IS NULL condition with syntax and examples.
Description
The MySQL IS NULL Condition is used to test for a NULL value in a SELECT, INSERT, UPDATE, or DELETE statement.
Syntax
The syntax for the IS NULL Condition in MySQL is:
expression IS NULLParameters or Arguments
expressionThe value to test if it is a NULL value.Note
- If expression is a NULL value, the condition evaluates to TRUE.
- If expression is not a NULL value, the condition evaluates to FALSE.
Example - With SELECT Statement
Let's look at an example of how to use MySQL IS NULL in a SELECT statement:
SELECT * FROM contacts WHERE last_name IS NULL;This MySQL IS NULL example will return all records from the contacts table where the last_name contains a NULL value.
Example - With INSERT Statement
Next, let's look at an example of how to use MySQL IS NULL in an INSERT statement:
INSERT INTO contacts (contact_id, contact_name) SELECT account_no, supplier_name FROM suppliers WHERE category IS NULL;This MySQL IS NULL example will insert records into the contacts table where the category contains a NULL value.
Example - With UPDATE Statement
Next, let's look at an example of how to use MySQL IS NULL in an UPDATE statement:
UPDATE contacts SET last_name = 'TBD' WHERE last_name IS NULL;This MySQL IS NULL example will update records in the contacts table where the last_name contains a NULL value.
Example - With DELETE Statement
Next, let's look at an example of how to use MySQL IS NULL in a DELETE statement:
DELETE FROM contacts WHERE last_name IS NULL;This MySQL IS NULL example will delete all records from the contacts table where the last_name contains a NULL value.
This MySQL tutorial explains how to use the MySQL IS NOT NULL condition with syntax and examples.
Description
The MySQL IS NOT NULL condition is used to test for a NOT NULL value in a SELECT, INSERT, UPDATE, or DELETE statement.
Syntax
The syntax for the IS NOT NULL Condition in MySQL is:
expression IS NOT NULLParameters or Arguments
expressionThe value to test if it is a not NULL value.Note
- If expression is NOT a NULL value, the condition evaluates to TRUE.
- If expression is a NULL value, the condition evaluates to FALSE.
Example - With SELECT Statement
Here is an example of how to use the MySQL IS NOT NULL condition in a SELECT statement:
SELECT * FROM contacts WHERE last_name IS NOT NULL;This MySQL IS NOT NULL example will return all records from the contacts table where the last_name does not contain a null value.
Example - With INSERT Statement
Here is an example of how to use the MySQL IS NOT NULL condition in an INSERT statement:
INSERT INTO contacts (contact_id, contact_name) SELECT account_no, supplier_name FROM suppliers WHERE category IS NOT NULL;This MySQL IS NOT NULL example will insert records into the contacts table where the category does not contain a null value.
Example - With UPDATE Statement
Here is an example of how to use the MySQL IS NOT NULL condition in an UPDATE statement:
UPDATE contacts SET status = 'completed' WHERE last_name IS NOT NULL;This MySQL IS NOT NULL example will update records in the contacts table where the last_name does not contain a null value.
Example - With DELETE Statement
Here is an example of how to use the MySQL IS NOT NULL condition in a DELETE statement:
DELETE FROM contacts WHERE last_name IS NOT NULL;This MySQL IS NOT NULL example will delete all records from the contacts table where the last_name does not contain a null value.
i've got a table "bla" like this:
[id] [name] [fk] 1 test 4 2 foo 5 3 bar NULLif i do the sql query
SELECT * FROM bla WHERE fk <> 4i only get the record with the id 2. i don't get the record with id 3 where fk is null. I thought NULL != 4. Seems that this is wrong.
Why is this so?
asked Jul 26, 2011 at 8:02
0
NULL doesn't compare equal to anything. You'll need to accept nulls explicitly:
where fk <> 4 or fk is null;See Working with NULL for more information about NULL handling.
answered Jul 26, 2011 at 8:04
MatMat
198k40 gold badges385 silver badges400 bronze badges
Because NULL stands for UNKNOWN, and when you compare a value with UNKNOWN, the result will always be false.
Take a look at this comparisons -
NULL = NULL -- false, since both are unknown, so the truth value of this expression can't be determined. NULL = 4 -- false 4 = 4 -- true, since both values are known.If you want to fetch the records containing NULL, you need to re-write your query this way -
where fk <> 4 OR fk is null;For more information, see Wikipedia.
answered Jul 26, 2011 at 8:04
MD Sayem AhmedMD Sayem Ahmed
28.2k26 gold badges109 silver badges177 bronze badges
NULL is not a value, but rather the unknown absence of a value. If you'd like to test for NULL, you have to do so explicitly by using IS NULL and IS NOT NULL. For example, NULL will test FALSE even against NULL itself. So, working with NULL is only done with the aforementioned functions (and ISNULL()). Your query could be rewritten as
SELECT * FROM bla WHERE fk <> 4 OR fk IS NULLanswered Jul 26, 2011 at 8:06
NaltharialNaltharial
2,07213 silver badges19 bronze badges
NULL is special in that it represents an "unknown" value. This can't be compared to numbers (or any other value for that matter), hence the result -
Is NULL <> 4? The answer is - don't know. Is 4 different from an unknown value?
Try this instead:
SELECT * FROM bla WHERE fk <> 4 OR FK IS NULLanswered Jul 26, 2011 at 8:05
OdedOded
481k98 gold badges873 silver badges1002 bronze badges
How about
SELECT * FROM bla WHERE NOT (fk = 4)Logic:
NULL = 4 --false 5 = 4 --false 4 = 4 --true NOT (NULL = 4) --true NOT (5 = 4) --true NOT (4 = 4) --falseanswered Jul 26, 2011 at 8:09
AlexAlex
13.9k5 gold badges40 silver badges58 bronze badges
Following statement should help:
SELECT * FROM bla WHERE COALESCE(fk,0) <> 4
answered Dec 12, 2016 at 7:28