What each join keeps
- INNER JOIN: only the pairs that match.
- LEFT JOIN: every row of the left table; NULLs where the right table has no match.
- RIGHT JOIN: every row of the right table; NULLs where the left table has no match.
- FULL OUTER JOIN: everything from both sides, matched where possible.
- CROSS JOIN: every pair, with no condition: rows × rows.
How many rows?
- A row that matches three rows on the other side appears three times: joins can multiply rows.
- INNER is never more than LEFT or RIGHT, and FULL = LEFT + RIGHT − INNER.
NULL never matches
In SQL, NULL = NULL is not true, so a NULL key never joins, not even to another NULL. That's why a student whose dept_id is NULL never gets a department, even in a LEFT JOIN: they appear, but with NULLs on the right.
Asked in interviews
- Students with no department?
LEFT JOIN, thenWHERE d.id IS NULL. This is called an anti-join. - ON or WHERE in a LEFT JOIN? A condition on the right table in
WHEREremoves the NULL rows and turns it into an inner join. Put it inONto keep every left row. - FULL OUTER JOIN in MySQL? MySQL doesn't have it: combine a
LEFT JOINand aRIGHT JOINwithUNION.
More: the DBMS questions, answered, and the lesson on database indexes.