SQL Interview Question: Explain the Different Types of JOIN

Short answer: joins combine rows from two tables based on a condition. They differ in what happens to rows that have no match.

-- customers: (1, Asha), (2, Ravi), (3, Meera)
-- orders:    (101, customer 1), (102, customer 1), (103, customer 9)

INNER JOIN: only matches

SELECT c.name, o.id FROM customers c
INNER JOIN orders o ON o.customer_id = c.id;
-- Asha 101, Asha 102

LEFT JOIN: all rows from the left, matches or NULLs

SELECT c.name, o.id FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;
-- Asha 101, Asha 102, Ravi NULL, Meera NULL

RIGHT JOIN and FULL OUTER JOIN

• RIGHT JOIN - all rows from the right table (usually rewritten as a LEFT JOIN with tables swapped).
• FULL OUTER JOIN - all rows from both sides; here it adds order 103 with a NULL customer. Not supported directly in MySQL (use a UNION of LEFT and RIGHT joins).

CROSS JOIN: every combination

SELECT s.size, c.color FROM sizes s CROSS JOIN colors c;   -- all product variants

Self join

-- Employees and their managers, from one table
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;

Classic follow-up: rows with no match

-- Customers who never ordered
SELECT c.* FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

-- Equivalent and often clearer
SELECT * FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

Watch out: putting a condition on the right table in WHERE (for example o.status = 'paid') turns a LEFT JOIN into an inner join. Put it in the ON clause to keep unmatched rows.