{"title":"INNER JOIN","description":"","content":"The _Introduction to Joins_ chapter covered what joins are and when to use each type. This chapter focuses entirely 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.\n\n---\n\n# Sample Data\n\nThis chapter uses the `users`, `artists`, `albums`, `tracks`, `streams`, and `genres` tables. Insert the following rows to follow along:\n\n\n```sql\nINSERT INTO genres (genre_id, name, parent_genre_id)\nVALUES\n (1, 'Pop', NULL),\n (2, 'Electronic', NULL),\n (3, 'J-Pop', 1),\n (4, 'House', 2);\n\nINSERT INTO users (user_id, username, email, country, timezone, signup_date, subscription_tier)\nVALUES\n (1, 'alice_m', 'alice@example.com', 'US', 'America/New_York', '2020-06-15', 'premium'),\n (2, 'bob_jones', 'bob@example.com', 'US', 'America/Chicago', '2021-11-22', 'free'),\n (3, 'carol_uk', 'carol@example.com', 'GB', 'Europe/London', '2022-08-10', 'premium'),\n (4, 'dave_kr', 'dave@example.com', 'KR', 'Asia/Seoul', '2023-05-01', 'family'),\n (5, 'emma_dev', 'emma@example.com', 'US', NULL, '2024-02-14', 'free'),\n (6, 'frank_j', 'frank@example.com', 'GB', NULL, '2024-09-01', 'premium');\n\nINSERT INTO artists (artist_id, name, country, verified)\nVALUES\n (1, 'Luna Wave', 'US', TRUE),\n (2, 'The Midnight', 'GB', TRUE),\n (3, 'Neon Dreams', 'CA', FALSE),\n (4, 'Aiko', 'JP', TRUE),\n (5, 'DJ Pulse', NULL, FALSE);\n\nINSERT INTO albums (album_id, artist_id, title, release_date, genre_id)\nVALUES\n (1, 1, 'Ocean Drive', '2022-06-15', 1),\n (2, 2, 'Nocturnal', '2023-01-20', 2),\n (3, 3, 'City Lights', '2023-09-01', 1),\n (4, 4, 'Sakura', '2024-03-10', 3),\n (5, 5, 'Bass Drop', '2024-07-22', 4);\n\nINSERT INTO tracks (track_id, album_id, title, duration_seconds, track_number)\nVALUES\n (1, 1, 'Sunrise', 142, 1),\n (2, 1, 'Tidal', 237, 2),\n (3, 2, 'Afterglow', 198, 1),\n (4, 2, 'Shadows', 312, 2),\n (5, 3, 'Neon Skyline', 275, 1),\n (6, 3, 'Downtown', 165, 2),\n (7, 4, 'Bloom', 348, 1),\n (8, 4, 'Petals', 90, 2),\n (9, 5, 'Drop Zone', 420, 1),\n (10, 5, 'Pulse', 180, 2);\n\nINSERT INTO streams (stream_id, user_id, track_id, started_at, duration_listened, country)\nVALUES\n (1, 1, 2, '2024-03-01 08:00:00+00', 237, 'US'),\n (2, 1, 4, '2024-03-01 08:15:00+00', 200, 'US'),\n (3, 2, 5, '2024-03-01 09:00:00+00', 275, 'US'),\n (4, 3, 7, '2024-03-01 10:30:00+00', 348, 'GB'),\n (5, 4, 1, '2024-03-01 11:00:00+00', 100, 'KR'),\n (6, 5, 9, '2024-03-01 12:00:00+00', 420, 'US'),\n (7, 1, 8, '2024-03-02 08:00:00+00', 90, 'US'),\n (8, 3, 3, '2024-03-02 09:00:00+00', 198, 'GB'),\n (9, 2, 6, '2024-03-02 14:00:00+00', 165, 'US'),\n (10, 4, 7, '2024-03-02 15:00:00+00', 348, 'KR'),\n (11, 1, 7, '2024-03-03 07:00:00+00', 348, 'US'),\n (12, 3, 9, '2024-03-03 11:00:00+00', 300, 'GB'),\n (13, 2, 2, '2024-03-03 14:00:00+00', 237, 'US'),\n (14, 5, 5, '2024-03-03 16:00:00+00', 275, 'US'),\n (15, 4, 4, '2024-03-04 08:00:00+00', 312, 'KR');\n```\n\n\nNotice that user 6 (`frank_j`) has no rows in `streams`. This will matter when we compare INNER JOIN behavior to LEFT JOIN later.\n\n---\n\n# How INNER JOIN Matches Rows\n\nINNER 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.\n\nHere is a simple example. We want to see which users have streamed something, along with the track ID they streamed:\n\n\n```sql\nSELECT u.username, s.track_id, s.started_at\nFROM users u\nINNER JOIN streams s ON u.user_id = s.user_id\nORDER BY s.started_at;\n```\n\n\nFive 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.\n\nThe following diagram shows the matching process. Rows with a match are combined into the result. Rows without a match (highlighted in red) are dropped:\n\n\n```mermaid\nflowchart LR\n subgraph Left[\"users\"]\n U1[\"alice_m (1)\"]:::cyan\n U2[\"bob_jones (2)\"]:::cyan\n U3[\"carol_uk (3)\"]:::cyan\n U4[\"dave_kr (4)\"]:::cyan\n U5[\"emma_dev (5)\"]:::cyan\n U6[\"frank_j (6)\"]:::red\n end\n\n subgraph Right[\"streams (sample)\"]\n S1[\"stream 1
user_id=1\"]:::orange\n S3[\"stream 3
user_id=2\"]:::orange\n S4[\"stream 4
user_id=3\"]:::orange\n end\n\n subgraph Result[\"INNER JOIN Result\"]\n R1[\"alice_m + stream 1\"]:::green\n R2[\"bob_jones + stream 3\"]:::green\n R3[\"carol_uk + stream 4\"]:::green\n end\n\n U1 --> R1\n S1 --> R1\n U2 --> R2\n S3 --> R2\n U3 --> R3\n S4 --> R3\n\n classDef cyan fill:#00ceff,stroke:#000,color:#000\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n classDef green fill:#69db7c,stroke:#000,color:#000\n classDef red fill:#ff8787,stroke:#000,color:#000\n```\n\n\nThe red node (`frank_j`) does not appear in the result because INNER JOIN requires a match on both sides.\n\n#### One-to-Many Matching\n\nWhen 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.\n\n\n```sql\nSELECT u.username, COUNT(*) AS stream_count\nFROM users u\nINNER JOIN streams s ON u.user_id = s.user_id\nGROUP BY u.username\nORDER BY stream_count DESC;\n```\n\n\n#### JOIN vs INNER JOIN\n\nBoth 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.\n\n---\n\n# ON Clause with Multiple Conditions\n\nThe 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.\n\n#### Matching on Two Columns\n\nSuppose 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:\n\n\n```sql\nSELECT u.username,\n u.country AS home_country,\n s.country AS stream_country,\n t.title\nFROM streams s\nINNER JOIN users u ON s.user_id = u.user_id\n AND s.country = u.country\nINNER JOIN tracks t ON s.track_id = t.track_id\nORDER BY u.username, s.started_at;\n```\n\n\nThe 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.\n\nCompare that to putting the country check in a WHERE clause instead:\n\n\n```sql\nSELECT u.username,\n u.country AS home_country,\n s.country AS stream_country,\n t.title\nFROM streams s\nINNER JOIN users u ON s.user_id = u.user_id\nINNER JOIN tracks t ON s.track_id = t.track_id\nWHERE s.country = u.country\nORDER BY u.username, s.started_at;\n```\n\n\nFor INNER JOIN, both queries return the same result. The next section explains why, and when the distinction matters.\n\n#### Non-Equality Conditions\n\nThe 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):\n\n\n```sql\nSELECT u.username, u.signup_date, s.started_at\nFROM users u\nINNER JOIN streams s ON u.user_id = s.user_id\n AND s.started_at >= u.signup_date\nORDER BY u.username, s.started_at;\n```\n\n\nNon-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.\n\n---\n\n# Filtering in ON vs WHERE\n\nFor INNER JOIN, placing a filter condition in ON or in WHERE produces identical results. These two queries return the same rows:\n\n\n```sql\n-- Filter in ON\nSELECT u.username, s.track_id\nFROM streams s\nINNER JOIN users u ON s.user_id = u.user_id\n AND u.subscription_tier = 'premium'\nORDER BY s.started_at;\n\n-- Filter in WHERE\nSELECT u.username, s.track_id\nFROM streams s\nINNER JOIN users u ON s.user_id = u.user_id\nWHERE u.subscription_tier = 'premium'\nORDER BY s.started_at;\n```\n\n\nBoth 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.\n\nThis 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 _LEFT JOIN_ chapter covers this in detail. For now, the best practice is:\n\n- Use **ON** for the actual join condition (the foreign key relationship between the tables).\n- Use **WHERE** for filtering rows after the join.\n\nThis convention keeps queries readable and avoids subtle bugs when you later change an INNER JOIN to a LEFT JOIN.\n\n---\n\n# Multi-Table INNER JOINs\n\nReal 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.\n\nHere is a query that answers \"for each stream, show the username, track title, album title, and artist name\":\n\n\n```sql\nSELECT u.username,\n t.title AS track_title,\n al.title AS album_title,\n ar.name AS artist_name,\n s.started_at\nFROM streams s\nINNER JOIN users u ON s.user_id = u.user_id\nINNER JOIN tracks t ON s.track_id = t.track_id\nINNER JOIN albums al ON t.album_id = al.album_id\nINNER JOIN artists ar ON al.artist_id = ar.artist_id\nORDER BY s.started_at;\n```\n\n\nFour joins, five tables. Each JOIN follows a foreign key:\n\n\n```mermaid\nflowchart LR\n S[\"streams
(s)\"]:::cyan\n U[\"users
(u)\"]:::orange\n T[\"tracks
(t)\"]:::cyan\n AL[\"albums
(al)\"]:::teal\n AR[\"artists
(ar)\"]:::orange\n\n S -->|\"user_id\"| U\n S -->|\"track_id\"| T\n T -->|\"album_id\"| AL\n AL -->|\"artist_id\"| AR\n\n classDef cyan fill:#00ceff,stroke:#000,color:#000\n classDef orange fill:#ffa94d,stroke:#000,color:#000\n classDef teal fill:#38d9a9,stroke:#000,color:#000\n```\n\n\n#### How the Chain Works Step by Step\n\nThink of multi-table joins as sequential operations (the database optimizes the actual execution, but the logical result is the same):\n\n1. `streams INNER JOIN users` on `user_id`: pairs each stream with its user. Streams whose `user_id` has no match in `users` are dropped.\n2. The result of step 1 is joined to `tracks` on `track_id`: adds track details. Any row where the `track_id` has no match in `tracks` is dropped.\n3. The result of step 2 is joined to `albums` on `album_id`: adds album details. Rows with no matching album are dropped.\n4. The result of step 3 is joined to `artists` on `artist_id`: adds artist details. Rows with no matching artist are dropped.\n\nIf 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.\n\n#### Adding Filters to Multi-Table Joins\n\nYou 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:\n\n\n```sql\nSELECT u.username,\n t.title AS track_title,\n ar.name AS artist_name\nFROM streams s\nINNER JOIN users u ON s.user_id = u.user_id\nINNER JOIN tracks t ON s.track_id = t.track_id\nINNER JOIN albums al ON t.album_id = al.album_id\nINNER JOIN artists ar ON al.artist_id = ar.artist_id\nWHERE ar.verified = TRUE\nORDER BY s.started_at;\n```\n\n\nThe WHERE clause runs after all the joins complete. It filters out rows where the artist is not verified.\n\n#### Adding Aggregation to Multi-Table Joins\n\nMulti-table joins pair naturally with GROUP BY. To count streams per artist:\n\n\n```sql\nSELECT ar.name AS artist_name,\n COUNT(*) AS total_streams\nFROM streams s\nINNER JOIN tracks t ON s.track_id = t.track_id\nINNER JOIN albums al ON t.album_id = al.album_id\nINNER JOIN artists ar ON al.artist_id = ar.artist_id\nGROUP BY ar.name\nORDER BY total_streams DESC;\n```\n\n\nThe 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.\n\n#### Joining the Same Table Twice\n\nSometimes 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.\n\nFor 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:\n\n\n```sql\nSELECT al.title AS album_title,\n g.name AS genre_name,\n pg.name AS parent_genre_name\nFROM albums al\nINNER JOIN genres g ON al.genre_id = g.genre_id\nINNER JOIN genres pg ON g.parent_genre_id = pg.genre_id;\n```\n\n\nThe 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.\n\n---\n\n# INNER JOIN vs Comma Syntax\n\nBefore 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:\n\n\n```sql\n-- Old comma syntax\nSELECT u.username, s.track_id\nFROM streams s, users u\nWHERE s.user_id = u.user_id;\n\n-- Modern JOIN syntax\nSELECT u.username, s.track_id\nFROM streams s\nINNER JOIN users u ON s.user_id = u.user_id;\n```\n\n\nBoth queries produce the same result. The database treats them identically.\n\nThe modern `JOIN ... ON` syntax is better for several reasons:\n\n\n| | Comma Syntax | JOIN ... ON |\n|---|---|---|\n| Join condition location | Mixed into WHERE with filters | Separated in ON clause |\n| Readability | Harder to see which conditions are joins vs filters | Join logic is explicit and next to the table |\n| Accidental cross join risk | Forget a WHERE condition and you silently get a Cartesian product | Forget the ON clause and PostgreSQL gives a syntax error |\n| Multiple join types | Cannot express LEFT, RIGHT, or FULL joins | Supports all join types |\n\n\nThe 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:\n\n\n```sql\n-- Comma syntax with a missing condition\nSELECT u.username, t.title\nFROM streams s, users u, tracks t\nWHERE s.user_id = u.user_id;\n-- Missing: AND s.track_id = t.track_id\n-- Result: every stream-user pair crossed with EVERY track\n```\n\n\nWith `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.\n\nUse 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.\n\n---\n\n# Common INNER JOIN Pitfalls\n\nThe _Introduction to Joins_ chapter covered mistakes like missing ON clauses and ambiguous columns. Here are two more pitfalls specific to how INNER JOIN handles data.\n\n#### Unexpected Row Multiplication\n\nWhen a row in the left table matches multiple rows in the right table, the result has more rows than either input table. This is correct behavior, but it surprises people who expect a one-to-one output.\n\nConsider joining `artists` to `albums`. Each artist can have one album (one-to-one) or many albums. If artist \"Luna Wave\" had three albums, that artist would appear three times in the result. If you then join to `tracks` and each album has 10 tracks, Luna Wave now appears 30 times.\n\nThis multiplication is not a bug. But if you are aggregating (say, summing revenue), failing to account for it produces inflated numbers. The fix is to aggregate at the right level before joining, or to use DISTINCT where appropriate.\n\n**What goes wrong here?**\n\n\n```sql\n-- Trying to count unique artists each user has listened to,\n-- but joining through tracks and albums first\nSELECT u.username,\n COUNT(ar.artist_id) AS artists_listened\nFROM streams s\nINNER JOIN users u ON s.user_id = u.user_id\nINNER JOIN tracks t ON s.track_id = t.track_id\nINNER JOIN albums al ON t.album_id = al.album_id\nINNER JOIN artists ar ON al.artist_id = ar.artist_id\nGROUP BY u.username;\n```\n\n\nIf alice_m streamed three tracks from the same artist, `COUNT(ar.artist_id)` returns 3, not 1. The count includes duplicates.\n\n**Fix:** Use `COUNT(DISTINCT ar.artist_id)`:\n\n\n```sql\nSELECT u.username,\n COUNT(DISTINCT ar.artist_id) AS artists_listened\nFROM streams s\nINNER JOIN users u ON s.user_id = u.user_id\nINNER JOIN tracks t ON s.track_id = t.track_id\nINNER JOIN albums al ON t.album_id = al.album_id\nINNER JOIN artists ar ON al.artist_id = ar.artist_id\nGROUP BY u.username\nORDER BY artists_listened DESC;\n```\n\n\n#### Silently Dropping Rows\n\nINNER JOIN only returns matched rows. If your data has referential integrity issues (a foreign key pointing to a row that does not exist) or if you are joining on an optional relationship, INNER JOIN quietly removes those rows.\n\nThis is the correct behavior, but it can cause confusion when you expect a certain number of rows and get fewer. If a query returns fewer rows than expected, check whether one of the join steps is filtering out unmatched rows. Switching that step to LEFT JOIN (and checking for NULLs in the result) will reveal which rows lack a match.","pageType":"sql"}

INNER JOIN

Ashish Pratap Singh

Get Premium