Advanced JOINs
RIGHT JOIN, FULL OUTER JOIN, and self-joins.
Questions this answers
- →What's RIGHT JOIN and when do I use it?
- →How do I find rows missing from either table?
- →How do I join a table to itself?
- →How do I join on multiple conditions?
How it works
Beyond INNER and LEFT JOIN, SQL has RIGHT JOIN (all rows from the right table), FULL OUTER JOIN (all rows from both tables), and self-joins (joining a table to itself). Each answers a different question about the relationship between your data.
RIGHT JOIN
SELECT customers.name, orders.id FROM orders RIGHT JOIN customers ON orders.customer_id = customers.id;
💡 RIGHT JOIN is LEFT JOIN with the tables swapped. Most people just rewrite it as a LEFT JOIN instead.
FULL OUTER JOIN
SELECT a.name AS artist, b.name AS similar FROM artists a FULL OUTER JOIN artists b ON a.similar_id = b.id;
💡 Not supported in SQLite. Use UNION of LEFT and RIGHT JOIN instead.
Self-join
-- Find 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;
Ready to practise Advanced JOINs?
Solve real exercises with AI feedback — free to start.
Try it free →