SQL.How does NULL affect join conditions, and why can filtering the right-hand table in WHERE remove unmatched rows from a LEFT JOIN?

In an equality join, `NULL` does not match any value—even another `NULL`—because the comparison evaluates to **UNKNOWN**. Join conditions match only when **TRUE**.

A `LEFT JOIN` preserves unmatched left-hand rows and fills the right-hand columns with `NULL`.

```sql
SELECT *
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.status = 'PAID';
```

For unmatched users, `o.status` is `NULL`, so the filter evaluates to `UNKNOWN` and removes them. This query effectively behaves like an `INNER JOIN`.

To preserve all users while joining only paid orders, put the filter in `ON`:

```sql
SELECT *
FROM users u
LEFT JOIN orders o
    ON o.user_id = u.id AND o.status = 'PAID';
```

**Interview answer:** “ON determines which rows match; WHERE filters the joined result. A WHERE condition that rejects NULL values from the right-hand table removes unmatched rows from a LEFT JOIN.”