**Finding NULL Foreign Keys:**

-- WRONG: = NULL doesn't work

SELECT * FROM orders WHERE customer_id = NULL;

-- Returns 0 rows

-- CORRECT: Use IS NULL

SELECT * FROM orders WHERE customer_id IS NULL;

-- Returns orders with NULL customer_id

**INNER JOIN Excludes NULL:**

SELECT o.id, c.name

FROM orders o

INNER JOIN customers c ON o.customer_id = c.id;

-- Orders with NULL customer_id excluded

**LEFT JOIN Preserves NULL:**

SELECT o.id, o.customer_id, c.name

FROM orders o

LEFT JOIN customers c ON o.customer_id = c.id;

-- All orders included

-- c.name is NULL for orders with NULL customer_id

**Distinguishing NULL Types:**

SELECT

o.id,

o.customer_id,

c.name,

CASE

WHEN o.customer_id IS NULL THEN 'No customer'

WHEN c.id IS NULL THEN 'Invalid customer'

ELSE 'Valid'

END AS status

FROM orders o

LEFT JOIN customers c ON o.customer_id = c.id;

**Handling NULL with COALESCE:**

SELECT

o.id,

COALESCE(c.name, 'Unknown Customer') AS customer_name,

COALESCE(c.email, 'no-email@example.com') AS customer_email

FROM orders o

LEFT JOIN customers c ON o.customer_id = c.id;

-- Provides default values for NULL

**Counting with NULL:**

-- Count all orders