JOINs: Combining Tables

INNER JOIN

INNER JOIN returns one row for each pair that satisfies ON. A user with several listens appears once per listen; a user with none does not appear.

Both tables have a user_id column. Qualify it as u.user_id or l.user_id to identify the intended table.

-- Keep matched pairs; users without listens drop.
SELECT u.user_id, u.name, l.song_id, l.rating
FROM Users u
INNER JOIN Listens l ON u.user_id = l.user_id
ORDER BY u.user_id

A double loop is one way to trace the matching pairs; it does not specify the database’s execution plan.

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;

Listens connects users and songs in a many-to-many relationship: a user can play many songs, and a song can have many listeners.

Logically, the joins form rows before WHERE filters them. The optimizer may reorder equivalent inner joins and push the filter earlier.

Outer Joins: LEFT and RIGHT

LEFT JOIN

A LEFT JOIN returns the matched pairs and keeps unmatched rows from the table before JOIN. Here, that table is Users.

-- Keep every user; missing listen columns are NULL.
SELECT u.user_id, u.name, l.song_id, l.rating
FROM Users u
LEFT JOIN Listens l ON u.user_id = l.user_id
ORDER BY u.user_id

RIGHT JOIN

A RIGHT JOIN keeps unmatched rows from the table after JOIN. Here, that table is Songs: the result includes songs that nobody played. Swapping the tables and using LEFT JOIN gives the same matched and unmatched pairs.

-- Keep every song; missing listen columns are NULL.
SELECT s.song_id, s.title, l.rating
FROM Listens l
RIGHT JOIN Songs s ON l.song_id = 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 keeps every matched pair and every unmatched row from both inputs. On engines without FULL OUTER JOIN, combine a LEFT JOIN with UNION ALL of the unmatched rows from the opposite side.

Foreign Keys

Every listen must match a user because Listens.user_id is NOT NULL and references Users.user_id. Neither constraint requires a user to have listens.

For Users u JOIN Listens l ON u.user_id = l.user_id:

• INNER JOIN returns the nine listens with their users.

• LEFT JOIN also keeps Pluto, giving ten rows.

• RIGHT JOIN adds nothing: every listen already matches a user, so it returns the same nine rows as INNER JOIN.

LEFT and RIGHT refer to table positions. With the tables reversed, Listens LEFT JOIN Users returns the nine matches, while Listens RIGHT JOIN Users also keeps Pluto. If a foreign key allows NULL, rows with NULL keys need not match; the guarantee above depends on NOT NULL too.

JOIN ON: Beyond Simple Equality

The ON condition decides which row pairs match. It can use comparisons such as < or >=, and combine conditions with AND or OR; it need not be an equality test.

Missing Join Conditions

Users CROSS JOIN Listens pairs every user with every listen: 4 × 9 = 36 rows. Joining on u.user_id = l.user_id keeps only the nine pairs where the listen belongs to that user. Use a Cartesian product only when you want every combination; simply omitting ON from a JOIN may instead be a syntax error.

Takeaway

A join returns matching row pairs. Choose the join type based on which unmatched rows must survive.