Is null in mysql in where clause

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 NULL

Parameters or Arguments

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 NULL

Parameters or Arguments

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       NULL

if i do the sql query

SELECT * FROM bla WHERE fk <> 4

i 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 NULL

answered 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 NULL

answered 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)    --false

answered 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

Is NULL in WHERE condition?

WHERE condition is NULL in MySQL?

IS NULL clause in MySQL?

IS NULL check in WHERE clause in SQL Server?