SQL.How does NULL affect IN and NOT IN, and why can NOT EXISTS be a safer choice when a subquery returns nullable values?

`IN` behaves like comparisons joined by `OR`:

```sql
3 IN (1, NULL)  -- FALSE OR UNKNOWN → UNKNOWN
1 IN (1, NULL)  -- TRUE OR UNKNOWN  → TRUE
```

`NOT IN` negates that result:

```sql
3 NOT IN (1, NULL) -- NOT UNKNOWN → UNKNOWN
1 NOT IN (1, NULL) -- NOT TRUE    → FALSE
```

**Pitfall:** If the subquery returns any `NULL`, `NOT IN` cannot evaluate to TRUE, so `WHERE` returns no rows.

Use `NOT EXISTS` to check for the absence of matching rows:

```sql
SELECT *
FROM users u
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.id
);
```

A `NULL` in `orders.user_id` does not match `u.id` and does not affect other comparisons.

**Interview answer:** “NULL can make IN and NOT IN evaluate to UNKNOWN. NOT EXISTS avoids this trap by checking whether matching rows exist, so nullable values in the subquery do not invalidate the result.”