JOINs: Combining Tables

INNER JOIN

-- Get all users with their listening history (excludes users without listens)
SELECT u.user_id, u.name, l.listen_id, l.song_id, l.rating
FROM Users u
INNER JOIN Listens l
  ON u.user_id = l.user_id
ORDER BY u.user_id, l.listen_id;

Output (9 rows)

• INNER JOIN is a double for-loop: for each user, for each of their listens, emit one row. Unmatched users (Pluto) drop out.

Multiple JOINs

-- Get user + song details for high-rated listens (rating > 4.0)
SELECT u.name, s.title, s.artist, l.rating
FROM Users u
INNER JOIN Listens l ON u.user_id = l.user_id
INNER JOIN Songs   s ON l.song_id = s.song_id
WHERE l.rating > 4.0
ORDER BY u.name, l.rating DESC;

• Connect Users, Listens, and Songs.

• Apply WHERE after all JOINs.

• Chain joins to walk from Users to Songs through Listens; the WHERE filter runs once, after every join is built.

Outer Joins: LEFT and RIGHT

LEFT JOIN

-- Keep every user, even those with no listens
SELECT u.user_id, u.name, l.listen_id, l.rating
FROM Users u
LEFT JOIN Listens l
  ON u.user_id = l.user_id
ORDER BY u.user_id, l.listen_id;

LEFT keeps every row of the left table. So Pluto, who never listened, returns as a tenth row with his listen columns NULL-filled.

RIGHT JOIN

RIGHT keeps every row of the right table, the mirror of LEFT. Keep all the Songs: not every song has a listen, so the unmatched rows show up on the right.

-- Keep every song, even the ones nobody ever played
SELECT s.song_id, s.title, l.rating
FROM Listens l
RIGHT JOIN Songs s
  ON l.song_id = s.song_id
ORDER BY s.song_id;

FULL OUTER JOIN

-- Keep every user AND every listen, padding with NULLs on either side
SELECT u.user_id, u.name, l.listen_id, l.rating
FROM Users u
FULL OUTER JOIN Listens l
  ON u.user_id = l.user_id;

FULL OUTER JOIN is the union of LEFT + RIGHT. Every row from both tables stays, NULL-padded where they don't match: it keeps the unmatched rows from either side at once. SQLite doesn't support it; combine a LEFT JOIN with UNION and a RIGHT JOIN instead.

Foreign Keys

A foreign key forces every row on one side to have a match. Listens.user_id references Users, so every listen has a user. That is why Users RIGHT JOIN Listens came out identical to the inner join: there were no orphan listens to keep. An outer join only adds rows when the side it keeps has unmatched ones. Songs have no such rule, so five go unplayed, and a RIGHT JOIN on Songs surfaces them. On the foreign-key side, an outer join adds nothing; on the other side, it finds the orphans.

JOIN ON: Beyond Simple Equality

The ON clause accepts any boolean condition, not only equality. Combine predicates with AND/OR; it is evaluated for each candidate row pair.

Key Rules

1. Join Condition: The ON clause defines the relationship (e.g., Users.user_id = Listens.user_id).

2. Join Order: Multiple JOINs are processed left-to-right, building intermediate results.

3. NULL Handling: Outer joins fill unmatched rows with NULLs.

4. Cartesian Product: A missing ON clause results in every possible combination, which is usually a mistake.

5. Column Ambiguity: Table aliases clarify which table a column belongs to.

6. Result Ordering: Without ORDER BY, rows return in an undefined order. Use ORDER BY for consistent results. (As we'll see later in Section 3, ordering incurs a performance cost due to sorting.)

Common Patterns

• One-to-Many: One user can have many listens (Users → Listens).

• Many-to-Many: Users listen to songs via a junction table (Users → Listens → Songs).

• Optional Relationships: LEFT JOIN keeps all users, even those without listens.

• Chaining: Connect 3+ tables by joining results with the next table.

Common Mistakes

Forgetting JOIN conditions

Problem: Missing ON clause creates a cartesian product.

Wrong JOIN type for requirement

Problem: INNER JOIN excludes non-matching rows.

Ambiguous column names

Problem: Both tables have a user_id column.