{"title":"LEFT JOIN","description":"","content":"INNER JOIN drops rows that have no match on either side. That works well when you only care about matched data, but sometimes you need to keep every row from one table regardless of whether a match exists. LEFT JOIN does exactly that: it returns all rows from the left table and fills in NULLs for the right table's columns when there is no match.\n\n---\n\n# Sample Data\n\nThis chapter uses the `users`, `artists`, `albums`, `tracks`, `streams`, `genres`, and `playlists` 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\nINSERT INTO playlists (playlist_id, creator_user_id, title, is_public, created_at)\nVALUES\n (1, 1, 'Morning Vibes', TRUE, '2024-01-10 08:00:00+00'),\n (2, 1, 'Workout Mix', TRUE, '2024-02-15 10:00:00+00'),\n (3, 3, 'UK Favorites', FALSE, '2024-03-01 12:00:00+00'),\n (4, 5, 'Chill Beats', TRUE, '2024-03-10 09:00:00+00');\n```\n\n\nUser 6 (`frank_j`) has no rows in `streams`. Users 2 (`bob_jones`), 4 (`dave_kr`), and 6 (`frank_j`) have no playlists. These gaps are deliberate and will drive the examples in this chapter.\n\n---\n\n# How LEFT JOIN Works\n\nLEFT JOIN starts the same way as INNER JOIN: it compares each row in the left table against every row in the right table using the ON condition. Rows that match are combined, just like INNER JOIN. The difference is what happens to left rows that have no match. Instead of being dropped, they are kept in the result with NULLs filling every column from the right table.\n\n\n```sql\nSELECT u.username, s.stream_id, s.track_id\nFROM users u\nLEFT JOIN streams s ON u.user_id = s.user_id\nORDER BY u.user_id, s.stream_id;\n```\n\n\nThis query returns all 15 stream rows for the five users who have streams, plus one additional row for `frank_j` where `stream_id` and `track_id` are both NULL. With INNER JOIN, that same query would return only the 15 matched rows.\n\nThe following diagram shows the matching process. All left rows are preserved. Matched rows get combined with right-table data. Unmatched rows get NULLs:\n\n\n```mermaid\nflowchart LR\n subgraph Left[\"users (left table)\"]\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)\"]:::cyan\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[\"LEFT JOIN Result\"]\n R1[\"alice_m + stream 1\"]:::green\n R2[\"bob_jones + stream 3\"]:::green\n R3[\"carol_uk + stream 4\"]:::green\n R6[\"frank_j + NULL\"]:::red\n end\n\n U1 --> R1\n S1 --> R1\n U2 --> R2\n S3 --> R2\n U3 --> R3\n S4 --> R3\n U6 --> R6\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 shows the key difference from INNER JOIN: `frank_j` has no matching stream, but LEFT JOIN keeps the row anyway with NULL values for the right side.\n\n#### LEFT JOIN vs LEFT OUTER JOIN\n\n`LEFT JOIN` and `LEFT OUTER JOIN` are identical. The word OUTER is optional. Most developers write `LEFT JOIN` because it is shorter. You will see both forms in documentation and interview questions, but they mean the same thing.\n\n#### One-to-Many Behavior\n\nLEFT JOIN has the same one-to-many behavior as INNER JOIN. When a left row matches multiple right rows, it appears once per match. alice_m has four streams, so she produces four rows. The only difference is that LEFT JOIN also includes left rows with zero matches, which INNER JOIN would drop.\n\n---\n\n# Finding Unmatched Rows\n\nOne of the most common uses of LEFT JOIN is finding rows in the left table that have no corresponding row in the right table. The technique is straightforward: do a LEFT JOIN, then use WHERE to keep only the rows where the right table's key column is NULL.\n\n\n```sql\nSELECT u.username, u.signup_date\nFROM users u\nLEFT JOIN streams s ON u.user_id = s.user_id\nWHERE s.stream_id IS NULL;\n```\n\n\nThis returns only `frank_j`, the one user who has never streamed a track.\n\nThe logic works in two steps:\n\n1. LEFT JOIN produces a result where every user appears. Users with streams have non-NULL `stream_id` values. Users without streams have NULL `stream_id` values.\n2. `WHERE s.stream_id IS NULL` filters out all the matched rows, leaving only the unmatched ones.\n\n\n```mermaid\nflowchart LR\n subgraph Step1[\"Step 1: LEFT JOIN\"]\n A1[\"alice_m + streams\"]:::green\n A2[\"bob_jones + streams\"]:::green\n A3[\"carol_uk + streams\"]:::green\n A4[\"dave_kr + streams\"]:::green\n A5[\"emma_dev + streams\"]:::green\n A6[\"frank_j + NULL\"]:::red\n end\n\n subgraph Step2[\"Step 2: WHERE IS NULL\"]\n B6[\"frank_j\"]:::red\n end\n\n A6 --> B6\n\n classDef green fill:#69db7c,stroke:#000,color:#000\n classDef red fill:#ff8787,stroke:#000,color:#000\n```\n\n\nThis pattern is called an **anti-join**: \"give me rows from the left table that have no match in the right table.\" It appears constantly in interviews and real-world queries.\n\n#### Choosing the Right Column for IS NULL\n\nAlways check for NULL on a column that cannot be NULL when a match exists. The safest choice is the right table's primary key or the join key itself. Checking a nullable column could give false positives: a row that matched but happened to have a NULL value in that column would incorrectly pass the filter.\n\n\n```sql\n-- Good: stream_id is the primary key, never NULL when a match exists\nWHERE s.stream_id IS NULL\n\n-- Good: the join key works too\nWHERE s.user_id IS NULL\n\n-- Risky: duration_listened could be NULL even for matched rows\nWHERE s.duration_listened IS NULL\n```\n\n\n#### More Anti-Join Examples\n\nFinding artists who have never been followed:\n\n\n```sql\nSELECT ar.name\nFROM artists ar\nLEFT JOIN artist_follows af ON ar.artist_id = af.artist_id\nWHERE af.artist_id IS NULL;\n```\n\n\nFinding users who have never made a payment:\n\n\n```sql\nSELECT u.username\nFROM users u\nLEFT JOIN payments p ON u.user_id = p.user_id\nWHERE p.payment_id IS NULL;\n```\n\n\nThe pattern is always the same: LEFT JOIN on the relationship, then WHERE right-side key IS NULL.\n\n---\n\n# ON vs WHERE: The Critical Difference\n\nThe _INNER JOIN_ chapter mentioned that putting a filter in ON vs WHERE produces identical results for INNER JOIN. That equivalence does not hold for LEFT JOIN, and understanding why is essential.\n\nThe rule is:\n\n- **ON** conditions affect which right-table rows are considered a match. Unmatched left rows are still preserved with NULLs regardless of what the ON condition says.\n- **WHERE** conditions filter the final result. They can and will remove the NULL-padded rows that LEFT JOIN preserved.\n\nHere is a concrete example. We want all users, with their streams, but we only care about streams of track 7 (Bloom):\n\n#### Filter in ON\n\n\n```sql\nSELECT u.username, s.stream_id, s.track_id\nFROM users u\nLEFT JOIN streams s ON u.user_id = s.user_id\n AND s.track_id = 7\nORDER BY u.user_id;\n```\n\n\nThis returns all six users. alice_m, carol_uk, and dave_kr have streams of track 7, so their rows show the stream data. The other three users (bob_jones, emma_dev, frank_j) have no streams of track 7, so their stream columns are NULL. The ON condition narrowed which streams counted as a \"match,\" but LEFT JOIN still preserved every user.\n\n#### Filter in WHERE\n\n\n```sql\nSELECT u.username, s.stream_id, s.track_id\nFROM users u\nLEFT JOIN streams s ON u.user_id = s.user_id\nWHERE s.track_id = 7\nORDER BY u.user_id;\n```\n\n\nThis returns only the three users who streamed track 7. The WHERE clause runs after the join completes, and it filters out every row where `track_id` is not 7, including all the NULL-padded rows from unmatched users. This effectively converts the LEFT JOIN into an INNER JOIN.\n\nThe following diagram illustrates how the two approaches produce different results:\n\n\n```mermaid\nflowchart TD\n subgraph ON[\"Filter in ON clause\"]\n direction LR\n O1[\"All 6 users
preserved\"]:::cyan\n O2[\"Only track 7
streams matched\"]:::orange\n O3[\"6 rows
(3 with data,
3 with NULLs)\"]:::green\n O1 --> O2 --> O3\n end\n\n subgraph WHERE[\"Filter in WHERE clause\"]\n direction LR\n W1[\"All 6 users
preserved\"]:::cyan\n W2[\"All streams
matched\"]:::orange\n W3[\"WHERE track_id = 7
filters result\"]:::red\n W4[\"3 rows
(NULLs removed)\"]:::teal\n W1 --> W2 --> W3 --> W4\n end\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 classDef teal fill:#38d9a9,stroke:#000,color:#000\n```\n\n\nThis distinction is the single most important thing to understand about LEFT JOIN. When you want to narrow the match without losing unmatched left rows, put the condition in ON. When you want to filter the final result (and accept that unmatched rows will be removed), put the condition in WHERE.\n\n#### Combining ON Filters with IS NULL\n\nYou can combine both techniques. For example, \"find users who have never streamed track 7\" requires an ON filter to restrict the match and a WHERE filter to find the unmatched:\n\n\n```sql\nSELECT u.username\nFROM users u\nLEFT JOIN streams s ON u.user_id = s.user_id\n AND s.track_id = 7\nWHERE s.stream_id IS NULL;\n```\n\n\nThis first finds streams of track 7 for each user (ON condition). Users with no stream of track 7 get NULLs. The WHERE clause then keeps only those NULL rows. The result is every user who has not streamed track 7: bob_jones, emma_dev, and frank_j.\n\n---\n\n# LEFT JOIN with Multiple Tables\n\nReal queries often chain several tables. When every join in the chain is a LEFT JOIN, unmatched rows are preserved at each step. But mixing LEFT JOIN with INNER JOIN in the same query requires caution.\n\n#### Chaining LEFT JOINs\n\nTo show every track with its stream count and the username of who streamed it (including tracks nobody has streamed), you might chain two LEFT JOINs:\n\n\n```sql\nSELECT t.title AS track_title,\n u.username,\n s.started_at\nFROM tracks t\nLEFT JOIN streams s ON t.track_id = s.track_id\nLEFT JOIN users u ON s.user_id = u.user_id\nORDER BY t.track_id, s.started_at;\n```\n\n\nEvery track appears in the result. Track 10 (Pulse) has no streams in our sample data, so it shows up with NULLs for both the stream and user columns. The second LEFT JOIN preserves the NULL-padded rows from the first one.\n\n#### Mixing LEFT JOIN and INNER JOIN\n\nWatch what happens if you change the second join to INNER JOIN:\n\n\n```sql\nSELECT t.title AS track_title,\n u.username,\n s.started_at\nFROM tracks t\nLEFT JOIN streams s ON t.track_id = s.track_id\nINNER JOIN users u ON s.user_id = u.user_id\nORDER BY t.track_id, s.started_at;\n```\n\n\nTrack 10 (Pulse) disappears from the result. The LEFT JOIN preserved it with a NULL `user_id`, but the INNER JOIN on `users` requires a match on `s.user_id`. Since `s.user_id` is NULL for unstreamed tracks, the INNER JOIN drops them.\n\nThis is a common mistake: using LEFT JOIN early in the chain to preserve rows, then accidentally removing them with an INNER JOIN later. If you need to preserve all left rows through the entire chain, every subsequent join in that path must also be a LEFT JOIN.\n\n\n```mermaid\nflowchart LR\n subgraph Chain[\"Join Chain\"]\n T[\"tracks
(10 rows)\"]:::cyan\n S[\"LEFT JOIN
streams\"]:::green\n U_LEFT[\"LEFT JOIN users
(10 rows kept)\"]:::green\n U_INNER[\"INNER JOIN users
(unstreamed tracks
dropped)\"]:::red\n end\n\n T --> S\n S --> U_LEFT\n S --> U_INNER\n\n classDef cyan fill:#00ceff,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\n#### Multi-Table Anti-Join\n\nYou can find unmatched rows through a chain of tables. For example, \"find artists whose tracks have never been streamed\" requires going through `artists` → `albums` → `tracks` → `streams`:\n\n\n```sql\nSELECT ar.name AS artist_name\nFROM artists ar\nJOIN albums al ON ar.artist_id = al.artist_id\nJOIN tracks t ON al.album_id = t.album_id\nLEFT JOIN streams s ON t.track_id = s.track_id\nWHERE s.stream_id IS NULL;\n```\n\n\nThe first two joins are INNER JOINs because we only want artists who have albums and tracks (an artist with no albums has nothing to stream in the first place). The LEFT JOIN on `streams` preserves tracks with no streams, and the WHERE clause filters down to just those. If every track by an artist has been streamed, that artist does not appear.\n\nIn our sample data, every track has at least one stream except Pulse (track 10). So this query would return DJ Pulse, since one of their tracks has never been streamed. But the query finds tracks, not artists, that were never streamed. If you specifically want artists where none of their tracks have been streamed, you need a different approach using a subquery or a GROUP BY with HAVING. The query above gives you artists who have at least one unstreamed track.\n\n---\n\n# Common Patterns\n\n#### Counting with Zeros\n\nA frequent task is counting related rows for every item in a table, including items with a count of zero. INNER JOIN cannot do this because it drops the items with no related rows.\n\nTo get the stream count for every track, including tracks with zero streams:\n\n\n```sql\nSELECT t.title,\n COUNT(s.stream_id) AS stream_count\nFROM tracks t\nLEFT JOIN streams s ON t.track_id = s.track_id\nGROUP BY t.title\nORDER BY stream_count DESC;\n```\n\n\nTwo details matter here:\n\n1. **Use `COUNT(s.stream_id)` instead of `COUNT(*)`**. With LEFT JOIN, unmatched tracks have one row where all stream columns are NULL. `COUNT(*)` counts that NULL row, giving 1 instead of 0. `COUNT(s.stream_id)` skips NULLs and correctly returns 0 for unstreamed tracks.\n\n1. **LEFT JOIN before GROUP BY**. The LEFT JOIN preserves all tracks. GROUP BY then collapses the rows, and COUNT gives the correct count including zeros.\n\nThis same pattern works for any \"count per item including zeros\" scenario: albums per artist, playlists per user, payments per user.\n\n#### Optional Relationships\n\nSome relationships in a database are optional. A user might not have a timezone set. A track might not have streams yet. When you need data from both sides of an optional relationship, LEFT JOIN keeps the rows where the optional side is missing.\n\nFor example, showing every user with their most recent payment date:\n\n\n```sql\nSELECT u.username,\n MAX(p.payment_date) AS last_payment\nFROM users u\nLEFT JOIN payments p ON u.user_id = p.user_id\nGROUP BY u.username\nORDER BY u.username;\n```\n\n\nUsers who have never made a payment show NULL for `last_payment` instead of being excluded from the result.\n\n#### Default Values with COALESCE\n\nWhen NULL values from unmatched rows make the output confusing, use COALESCE to provide a default:\n\n\n```sql\nSELECT t.title,\n COALESCE(COUNT(s.stream_id), 0) AS stream_count\nFROM tracks t\nLEFT JOIN streams s ON t.track_id = s.track_id\nGROUP BY t.title\nORDER BY stream_count;\n```\n\n\nIn this particular case, `COUNT(s.stream_id)` already returns 0 (not NULL) for unstreamed tracks, so COALESCE is not strictly needed. But for aggregations like SUM, MAX, or MIN that return NULL when all input values are NULL, COALESCE is essential:\n\n\n```sql\nSELECT u.username,\n COALESCE(SUM(s.duration_listened), 0) AS total_seconds\nFROM users u\nLEFT JOIN streams s ON u.user_id = s.user_id\nGROUP BY u.username\nORDER BY total_seconds DESC;\n```\n\n\nWithout COALESCE, frank_j would show NULL for `total_seconds`. With it, frank_j shows 0.\n\n---\n\n# Common Pitfalls\n\n#### COUNT(*) vs COUNT(column) After LEFT JOIN\n\nThis is the most common mistake with LEFT JOIN and aggregation.\n\n**What goes wrong here?**\n\n\n```sql\nSELECT t.title,\n COUNT(*) AS stream_count\nFROM tracks t\nLEFT JOIN streams s ON t.track_id = s.track_id\nGROUP BY t.title\nORDER BY stream_count;\n```\n\n\nEvery track shows a count of at least 1, even tracks with no streams. `COUNT(*)` counts rows, and LEFT JOIN produces one row per unmatched track (filled with NULLs). That row gets counted.\n\n**Fix:** Use `COUNT(s.stream_id)` to count only non-NULL values:\n\n\n```sql\nSELECT t.title,\n COUNT(s.stream_id) AS stream_count\nFROM tracks t\nLEFT JOIN streams s ON t.track_id = s.track_id\nGROUP BY t.title\nORDER BY stream_count;\n```\n\n\nNow unstreamed tracks correctly show 0.\n\n#### Accidentally Converting LEFT JOIN to INNER JOIN\n\nAdding a WHERE condition on a right-table column (without an IS NULL check) silently turns your LEFT JOIN into an INNER JOIN.\n\n**What goes wrong here?**\n\n\n```sql\nSELECT u.username, s.stream_id, t.title\nFROM users u\nLEFT JOIN streams s ON u.user_id = s.user_id\nLEFT JOIN tracks t ON s.track_id = t.track_id\nWHERE t.duration_seconds > 200;\n```\n\n\nThe intent might be \"show all users, but only include streams of long tracks.\" But the WHERE clause removes every row where `t.duration_seconds` is NULL, which includes all unmatched users (frank_j) and all streams of short tracks. The LEFT JOIN's preservation guarantee is completely undone.\n\n**Fix:** If you want to keep unmatched users, move the filter to ON:\n\n\n```sql\nSELECT u.username, s.stream_id, t.title\nFROM users u\nLEFT JOIN streams s ON u.user_id = s.user_id\nLEFT JOIN tracks t ON s.track_id = t.track_id\n AND t.duration_seconds > 200\nORDER BY u.user_id;\n```\n\n\nNow all six users appear. Users whose streams are for short tracks show NULLs for the track columns, and frank_j still appears with NULLs across the board.\n\nIf you do need a WHERE filter on the right table but want to also keep unmatched left rows, combine the condition with an IS NULL check:\n\n\n```sql\nWHERE t.duration_seconds > 200 OR t.track_id IS NULL\n```\n\n\nThis keeps both the filtered matches and the unmatched rows.\n\n#### INNER JOIN After LEFT JOIN\n\nAs shown in the multi-table section, placing an INNER JOIN after a LEFT JOIN can undo the preservation. If you see fewer rows than expected after a LEFT JOIN chain, check whether a later INNER JOIN is filtering out NULL-padded rows. The fix is usually to make the later join a LEFT JOIN as well.","pageType":"sql"}

LEFT JOIN

Ashish Pratap Singh

Get Premium