SQL IS NULL

SQL WHERE IS NULL Explained

WHERE IS NULL tests if a column has a NULL value.

NULL is a special value that signifies no value.

Testing for NULL with the = operator is not possible.

Instead, use WHERE IS NULL or WHERE IS NOT NULL.

Problem: List customers that have not placed any orders.

copy

copied to clipboard

SELECT Customer.Id, FirstName, LastName, TotalAmount
  FROM Customer
  LEFT JOIN [Order] ON Customer.Id = [Order].CustomerId
 WHERE TotalAmount IS NULL

SELECT Customer.Id, FirstName, LastName, TotalAmount
  FROM Customer
  LEFT JOIN [Order] ON Customer.Id = [Order].CustomerId
 WHERE TotalAmount IS NULL

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

copy

copied to clipboard

SELECT Id, CompanyName, Phone, Fax 
  FROM Supplier
 WHERE Fax IS NULL

SELECT Id, CompanyName, Phone, Fax 
  FROM Supplier
 WHERE Fax IS NULL

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

copy

copied to clipboard

SELECT Id, CompanyName, Phone, Fax 
  FROM Supplier
 WHERE Fax IS NOT NULL

SELECT Id, CompanyName, Phone, Fax 
  FROM Supplier
 WHERE Fax IS NOT NULL

• Our Sql WHERE Reference
• Our Sql WHERE IN Reference
• Our Sql WHERE EXISTS Reference
• Our Sql SELECT COUNT, SUM, AVG Reference