JOINs (Inner, Left, Right, Full, Self)
Intermediate⏱️ 25 mins read
What You'll Learn
JOINs combine rows from two or more tables based on a related column. INNER JOIN: only matching rows from both tables. LEFT JOIN: all left rows + matching right rows (NULL if no match). RIGHT JOIN: all right rows + matching left rows. FULL JOIN: all rows from both. SELF JOIN: a table joined to itself.
Syntax
SELECT cols FROM t1
INNER JOIN t2 ON t1.id = t2.fk;
SELECT cols FROM t1
LEFT JOIN t2 ON t1.id = t2.fk;Example
-- INNER JOIN: only matched orders
SELECT o.id, c.name, o.total
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;
-- LEFT JOIN: all customers, even without orders
SELECT c.name, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.name;
-- Find customers with NO orders
SELECT c.name
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.id IS NULL;
-- SELF JOIN: employees and their managers
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;Common Mistakes
Forgetting the ON condition — this creates a Cartesian product (every row combined with every row), which can produce billions of rows. Also: MySQL doesn't support FULL OUTER JOIN — use LEFT JOIN UNION RIGHT JOIN.
Interview Tips
The 'find rows in A with no match in B' pattern is very common: LEFT JOIN + WHERE b.id IS NULL. Draw Venn diagrams. Know that INNER JOIN can be written as just JOIN. Most analytics questions involve LEFT JOINs.
Practice
Find all products that have never been ordered. Then find all customers who placed at least one order in 2024. Finally, write a self-join to find pairs of employees in the same department.