This chapter focuses on INNER JOIN, the most common join type. INNER JOIN returns only rows that have a match in both tables. If a row on either side has no match, it is excluded from the result. Writing JOIN without a qualifier defaults to INNER JOIN.

Insert the following rows to follow along:

Notice that user 6 (frank_j) has no rows in streams. This will matter when we compare INNER JOIN behavior to LEFT JOIN later.

INNER JOIN pairs each row from the left table with every row in the right table that satisfies the ON condition. Rows with no match on either side are silently dropped.

Here is a simple example. We want to see which users have streamed something, along with the track ID they streamed:

Five of our six users appear in the result. frank_j has no rows in streams, so no row in the right table satisfies the ON condition for user_id = 6. INNER JOIN excludes that user entirely.

The following diagram shows the matching process. Rows with a match are combined into the result. Rows without a match (highlighted in red) are dropped:

The red node (frank_j) does not appear in the result because INNER JOIN requires a match on both sides.

One-to-Many Matching

When one user has multiple streams, INNER JOIN produces one output row per match. alice_m has four streams in our sample data, so she appears four times in the result. This is not a bug. It is exactly how one-to-many relationships work in joins. If you want one row per user with a count of streams, you need GROUP BY on top of the join.

JOIN vs INNER JOIN

Both keywords produce identical results. JOIN is shorthand for INNER JOIN. Most developers write just JOIN because it is shorter, but writing INNER JOIN explicitly can make the intent clearer, especially in queries that mix multiple join types. Either form is fine. Pick one and be consistent.

The ON clause is not limited to a single equality check. You can combine multiple conditions with AND to narrow which rows are considered a match.

Matching on Two Columns

Suppose you want to find streams where the user's home country matches the country they were streaming from. That requires checking both the foreign key relationship and a country comparison:

The first JOIN now has two conditions. A stream row is matched to a user row only when both user_id values match AND the stream's country equals the user's home country. Streams where the user was traveling (streaming from a different country than their home) are excluded.

Compare that to putting the country check in a WHERE clause instead:

For INNER JOIN, both queries return the same result. The next section explains why, and when the distinction matters.

Non-Equality Conditions

The ON clause can use any comparison operator, not just =. For example, to find streams that happened after a user signed up (which should be all of them, but is useful for data validation):

Non-equality joins are less common than equality joins, but they appear in scenarios like finding overlapping date ranges, matching events within a time window, or comparing values between tables.

For INNER JOIN, placing a filter condition in ON or in WHERE produces identical results. These two queries return the same rows:

Both queries return streams from premium users only. The reason they are equivalent: INNER JOIN discards any row that does not satisfy the ON condition, and discards any row that does not satisfy WHERE. Since unmatched rows are dropped either way, it does not matter which clause does the filtering.

This equivalence breaks with LEFT JOIN. When you move a filter on the right table into the ON clause of a LEFT JOIN, it changes which rows survive, because LEFT JOIN preserves unmatched left rows regardless of the ON condition. The next chapter covers this in detail.

For now, the best practice is:

This convention keeps queries readable and avoids subtle bugs when you later change an INNER JOIN to a LEFT JOIN.

Real queries often chain three or more tables. Each additional JOIN clause connects a new table to one already in the query. The order of the chain follows the foreign key path between the tables.

Here is a query that answers "for each stream, show the username, track title, album title, and artist name":

Four joins, five tables. Each JOIN follows a foreign key:

How the Chain Works Step by Step

Think of multi-table joins as sequential operations (the database optimizes the actual execution, but the logical result is the same):

If a single link in the chain has no match, the entire row disappears from the result. A stream pointing to a track that has no album (if such data existed) would be excluded, even though the stream-to-track join succeeded.

Adding Filters to Multi-Table Joins

You can filter the result of a multi-table join with WHERE, just like any other query. To find streams of tracks by verified artists only:

The WHERE clause runs after all the joins complete. It filters out rows where the artist is not verified.

Adding Aggregation to Multi-Table Joins

Multi-table joins pair naturally with GROUP BY. To count streams per artist:

The join chain connects streams to artists through tracks and albums. GROUP BY then collapses the rows by artist, and COUNT gives the total streams for each one.

Joining the Same Table Twice

Sometimes you need the same table in a query for two different purposes. You can join a table multiple times by giving each instance a different alias.

For example, genres have a parent_genre_id that references another row in the same genres table. To show each album with both its genre name and the parent genre name, you join genres twice:

The first join (g) gets the album's genre. The second join (pg) gets the parent genre by following the parent_genre_id foreign key. Albums whose genre has no parent (where parent_genre_id is NULL) are excluded because INNER JOIN requires a match. In our sample data, Pop and Electronic have parent_genre_id = NULL, so albums in those genres would not appear. Only J-Pop and House albums survive because their parent genres (Pop and Electronic) exist in the table.

Before the SQL-92 standard introduced the JOIN ... ON syntax, joins were written by listing tables separated by commas in the FROM clause and putting the join condition in WHERE:

Both queries produce the same result. The database treats them identically.

The modern JOIN ... ON syntax is better for several reasons:

The risk with comma syntax is that forgetting a join condition in WHERE does not cause an error. It produces a Cartesian product silently. With three or more tables, this becomes dangerous:

With JOIN ... ON, each join has its own ON clause. A missing condition is easier to spot because the table appears in the FROM clause but has no ON to connect it.

Use the JOIN ... ON syntax. The comma style still works in every database, but there is no advantage to it. You may encounter it in legacy codebases or older tutorials.