What is a NULL Value?
A field with a NULL value is a field with no value.
If a field in a table is optional, it is possible to insert a new record or
update a record without adding a value to this field. Then, the field will be
saved with a NULL value.
Note:
A NULL value is different from a zero value or a field that
contains spaces. A field with a NULL value is one that has been left blank
during record creation!
How to Test for NULL Values?
It is not possible to test for NULL values with comparison operators, such as
=, <, or <>.
We will have to use the
IS NULL
and
IS NOT NULL
operators instead.
IS NULL Syntax
SELECT
column_names
FROM
table_name
WHERE
column_name
IS NULL;
IS NOT NULL Syntax
SELECT
column_names
FROM
table_name
WHERE
column_name
IS NOT NULL;
Demo Database
Below is a selection from the
Customers
table used in the examples:
CustomerID
CustomerName
ContactName
Address
PostalCode
Country
Alfreds Futterkiste
Maria Anders
Obere Str. 57
Berlin
12209
Germany
Ana Trujillo Emparedados y helados
Ana Trujillo
Avda. de la Constitución 2222
México D.F.
05021
Mexico
Antonio Moreno Taquería
Antonio Moreno
Mataderos 2312
México D.F.
05023
Mexico
Around the Horn
Thomas Hardy
120 Hanover Sq.
London
WA1 1DP
Berglunds snabbköp
Christina Berglund
Berguvsvägen 8
Luleå
S-958 22
Sweden
The IS NULL Operator
The
IS NULL
operator is used to test for empty values (NULL values).
The following SQL lists all customers with a NULL value in the "Address" field:
Example
SELECT CustomerName, ContactName, Address
FROM Customers
WHERE Address
IS NULL;
Try it Yourself »
Tip:
Always use IS NULL to look for NULL values.
The IS NOT NULL Operator
The
IS NOT NULL
operator is used to test for non-empty values (NOT NULL
values).
The following SQL lists all customers with a value in the "Address" field:
Example
SELECT CustomerName, ContactName, Address
FROM Customers
WHERE Address
IS NOT NULL;
Try it Yourself »
Contact Sales
If you want to use W3Schools services as an educational institution, team or enterprise, send us an e-mail:
[email protected]
Report Error
If you want to report an error, or if you want to make a suggestion, send us an e-mail:
[email protected]
W3Schools is optimized for learning and training. Examples might be simplified to improve reading and learning.
Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness
of all content. While using W3Schools, you agree to have read and accepted our
terms of use
,
cookie and privacy policy
.
W3Schools is Powered by W3.CSS
.
