Skip to content

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.