INNER JOIN Excludes NULL Foreign Keys

intermediate

Show how orders with NULL customer_id are silently excluded from INNER JOIN results.

SELECT

o.id AS order_id,

o.customer_id,

c.name AS customer_name,

o.total

FROM orders o

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

ORDER BY o.id;

customers:

id | name

1 | John Smith

2 | Jane Doe

orders:

id | customer_id | total

101 | 1 | 100.00

102 | 2 | 200.00

103 | NULL | 150.00

104 | 999 | 300.00

order_id | customer_id | customer_name | total

101 | 1 | John Smith | 100.00

102 | 2 | Jane Doe | 200.00

Excluded:

- Order 103: customer_id is NULL

- Order 104: customer_id 999 doesn't exist

INNER JOIN excludes order 103 (NULL customer_id) and order 104 (invalid customer_id). NULL = NULL returns NULL (not TRUE), so no match occurs. Order 103 vanishes from report without error. This is why reports sometimes show fewer rows than expected.

All

graph LR

A["Order 101<br/>customer_id=1"] -->|"Match"| B["Customer 1<br/>John Smith"]

C["Order 102<br/>customer_id=2"] -->|"Match"| D["Customer 2<br/>Jane Doe"]

E["Order 103<br/>customer_id=NULL"] -."NULL never matches".-> F["❌ Excluded"]

G["Order 104<br/>customer_id=999"] -."No customer 999".-> H["❌ Excluded"]

style B fill:#90EE90

style D fill:#90EE90

style F fill:#ffcccc

style H fill:#ffcccc

LEFT JOIN Preserves NULL Foreign Keys

intermediate