NULL has special behavior in joins that catches many developers by surprise: NULL never matches NULL. When you write ON orders.customer_id = customers.id, if customer_id is NULL, it won't match anything - not even another NULL. This means rows with NULL foreign keys are excluded from INNER JOIN results. Understanding this prevents silent data loss in reports.

**The Fundamental Rule: NULL = NULL Returns NULL (Not TRUE)**

In SQL, NULL represents "unknown" or "missing value". When you compare NULL to anything (even another NULL), the result is NULL - which is treated as FALSE in join conditions.

NULL = NULL → NULL (not TRUE)

NULL = 5 → NULL (not FALSE)

NULL <> 5 → NULL (not TRUE)

Since join conditions need TRUE to match rows, NULL comparisons never match.

**How This Affects Joins:**

INNER JOIN orders to customers:

- Order with customer_id = 1: Matches customer with id = 1 ✓

- Order with customer_id = 2: Matches customer with id = 2 ✓

- Order with customer_id = NULL: Matches nothing ✗ (excluded from result)

The NULL customer_id order disappears from your report.

**Why NULL Foreign Keys Exist:**

1. **Optional Relationships:**

Employee might not have a manager (CEO has NULL manager_id)

Product might not have a category (uncategorized has NULL category_id)

2. **Data Quality Issues:**

Missing data during import

User didn't fill required field

Reference data deleted but foreign key not updated

3. **Incomplete Data Entry:**

Order created but customer not assigned yet

Draft records with incomplete information

**INNER JOIN Behavior with NULL:**

INNER JOIN excludes rows with NULL foreign keys:

SELECT o.id, c.name

FROM orders o

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

Result:

- Orders with valid customer_id: Included

- Orders with NULL customer_id: Excluded (silently dropped)

- Orders with invalid customer_id (no matching customer): Excluded

You lose data without any error or warning.

**LEFT JOIN Behavior with NULL:**

LEFT JOIN preserves rows with NULL foreign keys:

SELECT o.id, c.name

FROM orders o

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

Result: