Multi-Table Joins and Self-Joins

~14 min · joins, self-join, multi-table

Level 0Scout

0 XP0/80 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

Three or more tables

Joining more than two tables is just chained joins — SQLite evaluates them left to right. Aliases become essential at three or more tables; long unaliased names get unreadable fast.

A self-join is a table joined to itself with two aliases. It's the natural shape for hierarchical data (parent/child, manager/employee), graph traversal one hop, and pairwise comparisons.

Tip: Recursive hierarchies (arbitrary depth) want WITH RECURSIVE CTEs (track 8). A self-join handles one level. For deeper trees, prefer the recursive CTE form — it's more general and SQLite optimizes it well.

Code

Three-table join with aliases·sql

SELECT c.id, c.title,
       u.username AS owner,
       count(m.id) AS n_messages
FROM   conversations c
INNER  JOIN users u    ON u.id = c.owner_id
LEFT   JOIN messages m ON m.conversation_id = c.id
GROUP  BY c.id, u.username
ORDER  BY n_messages DESC LIMIT 10;

Self-join — find users with the same email domain·sql

SELECT u1.username AS a, u2.username AS b,
       substr(u1.email, instr(u1.email, '@')+1) AS domain
FROM   users u1
INNER  JOIN users u2
       ON substr(u1.email, instr(u1.email, '@')+1)
        = substr(u2.email, instr(u2.email, '@')+1)
       AND u1.id < u2.id;

External links

Joins reference

Exercise

Model a small org chart: employees(id, name, manager_id) with manager_id a self-FK to employees.id. Insert 10 employees forming a 3-level tree. Write a self-join query that returns each employee paired with their direct manager's name. Then think about how you'd return the full path to the CEO — and whether a recursive CTE would read better.

Progress

Progress is local-only — sign in to sync across devices.

← PreviousLEFT JOIN — Keep the Lonely Rows Next →Cardinality — One-to-Many and Many-to-Many

Spotted a bug or have feedback on this page?Report an Issue

Comments 0

🔔 Reply notifications (sign in)

No comments yet — be the first.