-- Checking foreign key instead of primary key

SELECT c.customer_name

FROM customers c

LEFT JOIN orders o ON c.customer_id = o.customer_id

WHERE o.customer_id IS NULL; -- Wrong! Checking foreign key

-- Check right table PRIMARY KEY for NULL

SELECT c.customer_name

FROM customers c

LEFT JOIN orders o ON c.customer_id = o.customer_id

WHERE o.order_id IS NULL; -- Correct! Checking primary key

Always check right table PRIMARY KEY (o.order_id) for NULL, not foreign key (o.customer_id). Foreign key can be NULL for other reasons.

Checking foreign key instead of primary key gives wrong results

-- Using INNER JOIN for anti-join pattern

SELECT c.customer_name

FROM customers c

INNER JOIN orders o ON c.customer_id = o.customer_id

WHERE o.order_id IS NULL; -- Returns nothing!

-- Use LEFT JOIN to preserve unmatched rows

SELECT c.customer_name

FROM customers c

LEFT JOIN orders o ON c.customer_id = o.customer_id

WHERE o.order_id IS NULL;

INNER JOIN only returns matching rows. LEFT JOIN preserves left table rows, allowing WHERE IS NULL to find unmatched ones.

INNER JOIN eliminates unmatched rows, making anti-join impossible

-- NOT IN with potential NULL values

SELECT customer_name

FROM customers

WHERE customer_id NOT IN (

SELECT customer_id FROM orders -- May contain NULL

);

-- Use NOT EXISTS or handle NULLs explicitly

SELECT customer_name

FROM customers c

WHERE NOT EXISTS (

SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id

);

NOT IN with NULL values returns empty result set. Use NOT EXISTS or add WHERE column IS NOT NULL to subquery.

NOT IN returns no results if subquery contains NULL values