Find NULL Values

basic

Find customers without email addresses

SELECT customer_id, name, email

FROM customers

WHERE email IS NULL

ORDER BY name;

customer_id | name | email

3 | Charlie | NULL

4 | Dave | NULL

IS NULL correctly identifies NULL values. WHERE email = NULL would return no rows.

All

Find Non-NULL Values

basic

Find customers with email addresses

SELECT customer_id, name, email

FROM customers

WHERE email IS NOT NULL

ORDER BY name;

customer_id | name | email

1 | Alice | alice@email.com

2 | Bob | bob@email.com

IS NOT NULL finds non-NULL values. WHERE email != NULL would return no rows.

All

COALESCE for Default Values

intermediate

Find products with discount (treating NULL as 0)

SELECT product_id, product_name, discount

FROM products

WHERE COALESCE(discount, 0) > 0

ORDER BY discount DESC;

product_id | product_name | discount

1 | Laptop | 100.00

2 | Mouse | 5.00

COALESCE replaces NULL with 0, allowing comparison. Products with NULL discount treated as 0.

All

NULL-Safe Optional Filtering

advanced

Filter by status, including NULL as valid

SELECT order_id, customer_id, status, total