Short answer
An INNER JOIN returns rows with matches in both tables. A LEFT JOIN returns every row from the left table and matching rows from the right, filling gaps with NULL; a RIGHT JOIN is the mirror image. A FULL OUTER JOIN keeps unmatched rows from both sides, and a CROSS JOIN returns every combination of rows.
Example
sql-- Customers and their orders, including customers with no orders
SELECT c.name, o.id AS order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;
-- Customers who have never ordered
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;Details interviewers check
- Filtering the right table in WHERE turns a LEFT JOIN into an INNER JOIN; put the condition in ON instead.
- Joining on non-unique keys multiplies rows — watch for duplicated totals.
- A self join relates rows in the same table, for example employees and managers.
How to answer it in an interview
- Sketch the Venn-style mental model, then say it breaks down with duplicates.
- Solve “find rows without a match” with LEFT JOIN … IS NULL or NOT EXISTS.