In PostgreSQL 18, table emp has 3 rows with dept_id values 10, 20 and NULL, and table dept_dup has 3 rows: (10, 'north'), (10, 'north-2') and (20, 'south'). How many rows does SELECT * FROM emp e LEFT JOIN dept_dup d ON d.id = e.dept_id; return?

Explanation

The left row with dept_id 10 matches two right rows and is therefore duplicated, the row with dept_id 20 matches once, and the row with a null dept_id matches nothing and is null-extended: 2 + 1 + 1. Answering 3 relies on the common belief that a LEFT JOIN returns exactly one row per left row, whereas the guarantee is only at least one.