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