SQL IS NULL Clause
What is NULL and how do I query for NULL values?
NULL is a special value that signifies 'no value'.
Comparing a column to NULL using the = operator is undefined.
Instead, use WHERE IS NULL or WHERE IS NOT NULL.
The SQL WHERE IS NULL syntax
The general IS NULL syntax is
SELECT column-names FROM table-name WHERE column-name IS NULL
The general IS NOT NULL syntax is:
SELECT column-names FROM table-name WHERE column-name IS NOT NULL
SUPPLIER |
---|
Id |
CompanyName |
ContactName |
City |
Country |
Phone |
Fax |
SQL WHERE IS NULL Examples
Problem: List all suppliers that have no fax number
SELECT Id, CompanyName, Phone, Fax FROM Supplier WHERE Fax IS NULL
Result: 16 records
Id | CompanyName | Phone | Fax |
---|---|---|---|
1 | Exotic Liquids | (171) 555-2222 | NULL |
2 | New Orleans Cajun Delights | (100) 555-4822 | NULL |
4 | Tokyo Traders | (03) 3555-5011 | NULL |
5 | Cooperativa de Quesos 'Las Cabras' | (98) 598 76 54 | NULL |
6 | Mayumi's | (06) 431-7877 | NULL |
![]() |
SUPPLIER |
---|
Id |
CompanyName |
ContactName |
City |
Country |
Phone |
Fax |
Problem: List all suppliers that do have a fax number
SELECT Id, CompanyName, Phone, Fax FROM Supplier WHERE Fax IS NOT NULL
Result: 13 records
Id | CompanyName | Phone | Fax |
---|---|---|---|
3 | Grandma Kelly's Homestead | (313) 555-5735 | (313) 555-3349 |
7 | Pavlova, Ltd. | (03) 444-2343 | (03) 444-6588 |
9 | PB Knäckebröd AB | 031-987 65 43 | 031-987 65 91 |
13 | Nord-Ost-Fisch Handelsgesellschaft mbH | (04721) 8713 | (04721) 8714 |
14 | Formaggi Fortini s.r.l. | (0544) 60323 | (0544) 60603 |
18 | Aux joyeux ecclésiastiques | (1) 03.83.00.68 | (1) 03.83.00.62 |
19 | New England Seafood Cannery | (617) 555-3267 | (617) 555-3389 |
21 | Lyngbysild | 43844108 | 43844115 |
22 | Zaanse Snoepfabriek | (12345) 1212 | (12345) 1210 |
24 | G'day, Mate | (02) 555-5914 | (02) 555-4873 |
26 | Pasta Buttini s.r.l. | (089) 6547665 | (089) 6547667 |
28 | Gai pâturage | 38.76.98.06 | 38.76.98.58 |
29 | Forêts d'érables | (514) 555-2955 | (514) 555-2921 |