Detailed explanation
A SQL JOIN combines rows from table references using a condition, usually a relationship between primary and foreign keys. Consider employees(id, name, dept_id, manager_id) containing Asha in department 10 and Ravi with no department, and departments(id, name) containing 10/Sales and 20/HR. The key question is which unmatched rows must survive. Joins matter because normalised databases store related facts separately; an incorrect join can drop records or multiply rows unexpectedly.
INNER JOIN returns only matching pairs: SELECT e.name, d.name AS department FROM employees e INNER JOIN departments d ON e.dept_id = d.id; returns Asha/Sales. LEFT JOIN preserves every left-side row: SELECT e.name, d.name AS department FROM employees e LEFT JOIN departments d ON e.dept_id = d.id; also returns Ravi/NULL. RIGHT JOIN preserves every right-side row: SELECT e.name, d.name AS department FROM employees e RIGHT JOIN departments d ON e.dept_id = d.id; returns Asha/Sales and NULL/HR. FULL OUTER JOIN preserves unmatched rows from both sides: SELECT e.name, d.name AS department FROM employees e FULL OUTER JOIN departments d ON e.dept_id = d.id; returns all three pairs. Unmatched columns are filled with NULL; ordinary equality does not match NULL values.
CROSS JOIN produces every possible pair: SELECT e.name, d.name AS department FROM employees e CROSS JOIN departments d; produces four rows here, without a matching condition. SELF JOIN means referencing the same table twice, not a separate preservation rule: SELECT e.name, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.id; lists employees with their managers while retaining employees without a matching manager. Aliases distinguish the two roles.
Under the hood, optimisers may choose nested-loop, hash, or merge joins depending on indexes, row counts, and the engine. Interviewers judge whether you understand preservation, one-to-many multiplication, and filtering: WHERE d.name = 'Sales' after a LEFT JOIN removes NULL-extended rows, unlike placing that condition in ON. Equi-joins and non-equi joins describe equality versus other matching conditions; semi-joins and anti-joins are commonly expressed using EXISTS and NOT EXISTS. NATURAL JOIN implicitly matches same-named columns, making explicit conditions safer. Syntax support varies: MySQL has no native FULL OUTER JOIN.
Key points to remember
- INNER JOIN keeps matching pairs; LEFT and RIGHT JOIN also preserve unmatched rows from the specified side.
- FULL OUTER JOIN retains matches and unmatched rows from both tables, padding missing columns with NULL.
- CROSS JOIN returns m × n rows for inputs containing m and n rows.
- SELF JOIN uses separate aliases for the same table and can be an inner or outer join.
- Each matching pair produces a row, so joining on a non-unique key can multiply results.
Common mistakes candidates make
- Putting a right-table filter in WHERE after a LEFT JOIN and unintentionally removing unmatched left-side rows.
- Assuming a join returns one row per employee without checking whether the other side has multiple matches.
- Using = NULL instead of IS NULL, or expecting two NULL join keys to match under ordinary equality.
Likely follow-up questions
How do you find employees without a matching department?
Use SELECT e.* FROM employees e LEFT JOIN departments d ON d.id = e.dept_id WHERE d.id IS NULL; assuming d.id is a non-nullable primary key. Alternatively, use NOT EXISTS with the same matching condition.
How can you emulate FULL OUTER JOIN in MySQL?
Combine a LEFT JOIN with a second query returning only unmatched right-side rows, using UNION ALL. For this example, the second query can use a RIGHT JOIN followed by WHERE e.id IS NULL, assuming e.id is non-nullable.
What is the difference between an INNER JOIN and EXISTS?
An INNER JOIN returns a row for every matching pair and can select columns from both tables. EXISTS tests whether at least one match exists, so multiple matches do not multiply each qualifying outer row.
Interview tip: Use one small dataset to explain matching rows, unmatched rows, and NULL padding, then demonstrate the employee–manager self join.