SQL IS NULL Clause

  • 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 definitive guide
for data professionals


Order today!
 See 2 min video



The SQL WHERE IS NULL syntax


The general syntax is:
SELECT column-names
  FROM table-name
 WHERE column-name IS NULL

The general not null syntax is:
SELECT column-names
  FROM table-name
  WHERE column-name IS NOT NULL




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


Results: 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





Problem: List all suppliers that do have a fax number
SELECT Id, CompanyName, Phone, Fax 
  FROM Supplier
 WHERE Fax IS NOT NULL


Results: 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