Joins · Easy
Explain INNER, LEFT, RIGHT, FULL OUTER and CROSS joins.
Short answer
An INNER JOIN keeps only rows that match on both sides. A LEFT JOIN keeps every row from the left table and fills NULLs where the right side has no match; RIGHT is the mirror image. FULL OUTER keeps unmatched rows from both sides. A CROSS JOIN pairs every row with every row, producing a Cartesian product.
Detailed answer
Think of joins as answering "which rows survive when there is no partner?"
| Join | Unmatched left rows | Unmatched right rows |
|---|---|---|
| INNER | dropped | dropped |
| LEFT | kept (right columns NULL) | dropped |
| RIGHT | dropped | kept |
| FULL OUTER | kept | kept |
A CROSS JOIN has no condition; 40 customers × 25 products gives 1,000 rows. It is useful for building a complete grid (every month × every category) that you then LEFT JOIN facts onto, so missing combinations show as zero instead of disappearing.
-- INNER vs LEFT: how many customers survive each join?
SELECT
(SELECT COUNT(DISTINCT c.customer_id)
FROM customers c JOIN orders o ON o.customer_id = c.customer_id) AS inner_join_customers,
(SELECT COUNT(DISTINCT c.customer_id)
FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id) AS left_join_customers;
The LEFT JOIN count is higher because customers who have never ordered are kept.
Interview tip: RIGHT JOIN is rarely written in practice; swap the table order and use LEFT JOIN so every query reads "start from the thing I am reporting on". FULL OUTER JOIN is supported in SQLite from version 3.39 and in PostgreSQL, but not in MySQL, where you emulate it with a LEFT JOIN UNION a RIGHT JOIN.