Topic 20 of 52 · Intermediate
JOINs
Combine tables with INNER, LEFT, RIGHT, CROSS and SELF joins.
Concept
A JOIN combines rows from two tables on a matching condition. INNER JOIN keeps only matching rows from both sides.
LEFT JOIN keeps every row from the left table and fills NULL where the right side has no match — perfect for 'customers with no orders'.
A SELF JOIN joins a table to itself (e.g. employee → manager). CROSS JOIN returns every combination. MySQL has no FULL OUTER JOIN; emulate it with LEFT JOIN UNION RIGHT JOIN.
Cheat sheet
| Join | Returns |
|---|---|
| INNER | Only matching rows |
| LEFT | All left rows + matches |
| RIGHT | All right rows + matches |
| CROSS | Every pair (m × n rows) |
| SELF | Table joined with itself |
Code (SQL · MySQL 8)
-- Customers who never ordered
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;
-- Employee with manager name
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;Remember
- LEFT JOIN + WHERE right.id IS NULL = anti-join.
- Filtering the right table in WHERE turns a LEFT JOIN into an INNER JOIN — put it in ON instead.
- Joining on non-unique keys multiplies rows.
Interview questions
Find employees earning more than their manager.
SELECT e.name FROM employees e JOIN employees m ON e.manager_id = m.id WHERE e.salary > m.salary;
How do you do a FULL OUTER JOIN in MySQL?
SELECT ... FROM a LEFT JOIN b ON ... UNION SELECT ... FROM a RIGHT JOIN b ON ...
Lesson test
Take an 8-question test on “JOINs” — concepts, output prediction, bug spotting and interview questions.