NULL handling

NULL means unknown or missing—not zero, not empty string. Three-valued logic (TRUE, FALSE, UNKNOWN) makes NULL comparisons a frequent interview topic and a source of silent bugs.

Comparisons fail silently

SELECT name, email
FROM customers
WHERE email = NULL;  -- wrong: never matches

SELECT name, email
FROM customers
WHERE email IS NULL;

SELECT name
FROM customers
WHERE email IS NOT NULL;

Practice: Run in SQLite, DB Fiddle, or Postgres. Create the customers/orders sample from the relational-model lesson if needed.

COALESCE and IFNULL

SELECT name,
       COALESCE(email, 'no-email@placeholder.local') AS email_display
FROM customers;

COALESCE(a, b, ...) returns the first non-NULL argument—standard ANSI. SQLite also offers IFNULL.

Aggregates and NULL

SELECT COUNT(*) AS all_rows,
       COUNT(email) AS rows_with_email
FROM customers;

COUNT(column) ignores NULLs; COUNT(*) counts all rows.

Important interview questions and answers

1. Q: NULL = NULL?
   A: Unknown—not TRUE. Use IS NULL for tests.
2. Q: COALESCE vs CASE?
   A: COALESCE is concise for first-non-null; CASE handles richer conditional logic.

Self-check

1. Which predicate finds rows where email is missing?
2. Does COUNT(email) include NULL emails?

Pitfall: WHERE col = NULL never matches—use IS NULL.