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)

• Only matching rows make the cut.

• Pluto (user_id=4) gets left out, with no listens to his name.

• Each match results in a new row.

• Think of INNER JOIN as a double for loop in Python.

Outer Joins: LEFT and RIGHT

-- Get all users, including those without any 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;

-- Get all listens, including any without a matching user (none here)
SELECT u.user_id, u.name, l.listen_id, l.rating
FROM Users u
RIGHT JOIN Listens l
  ON u.user_id = l.user_id
ORDER BY l.listen_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 merger of LEFT + RIGHT. Every row from both tables stays, with NULLs filling the blanks. For our Users + Listens example the output looks identical to the LEFT JOIN above (Pluto preserved, no extra rows), since every Listen already belongs to a User. SQLite doesn't support it; combine a LEFT JOIN with UNION and a RIGHT JOIN instead.

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.

• Each JOIN is a stepping stone.

• Ideal for complex data relationships.

JOIN ON: Beyond Simple Equality

The ON clause is more flexible than you think. It accepts any logical condition, not just =.

Key Point: Use AND/OR to combine conditions. The ON clause is evaluated for each potential 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: Unmatched rows in outer joins are filled 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 customers, even those without orders.

• 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.