← SQL roadmap

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

JoinReturns
INNEROnly matching rows
LEFTAll left rows + matches
RIGHTAll right rows + matches
CROSSEvery pair (m × n rows)
SELFTable 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 ...

Practise SQL with the AI mentor

Lesson test

Take an 8-question test on “JOINs” — concepts, output prediction, bug spotting and interview questions.

Can't find a SQL topic you need for exams or placements? Request it and we'll add it.