What is the impact of NULL values in join conditions?

Suppose a users table and an orders table, where some orders.user_id values are NULL:

SELECT u.id, o.id

FROM users u

LEFT JOIN orders o ON u.id = o.user_id;

• Users without orders will appear, with o.id as NULL.

SELECT u.id, o.id

FROM users u

INNER JOIN orders o ON u.id = o.user_id;

• Users without orders (i.e., no matching user_id) are excluded from the result set.

• Comparisons with NULL always evaluate to UNKNOWN, which is treated as FALSE in JOIN conditions.

• Be cautious when join columns may contain NULLs; the join type determines whether those rows are preserved.

• Use COALESCE or IS NULL checks if you want to include or transform NULL values explicitly.