{"title":"RIGHT JOIN","description":"","content":"LEFT JOIN preserves all rows from the left table. RIGHT JOIN does the opposite: it preserves all rows from the right table and fills in NULLs for the left side when no match exists. Every RIGHT JOIN can be rewritten as a LEFT JOIN by swapping the table order, which makes RIGHT JOIN rare in practice. But understanding it matters because it shows up in interviews and in legacy codebases.\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`. Track 10 (`Pulse`) has no streams. These gaps drive the examples in this chapter.\n\n---\n\n# How RIGHT JOIN Works\n\nRIGHT JOIN is the mirror image of LEFT JOIN. It compares rows from both tables using the ON condition, combines matched rows, and preserves every row from the right table. When a right-table row has no match in the left table, the left-side columns are filled with NULLs.\n\n\n```sql\nSELECT s.stream_id, s.track_id, u.username\nFROM streams s\nRIGHT JOIN users u ON s.user_id = u.user_id\nORDER BY u.user_id, s.stream_id;\n```\n\n\nThis returns all 15 stream rows for the five users who have streams, plus one row for `frank_j` where `stream_id` and `track_id` are NULL. The `users` table is on the right side of the join, so RIGHT JOIN preserves every user.\n\nCompare this with the equivalent LEFT JOIN from the previous chapter:\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\nBoth queries produce the same rows. The only difference is which table appears on which side of the JOIN keyword.\n\nThe following diagram shows the matching process. All right-table rows are preserved. Unmatched right rows get NULLs for the left side:\n\n\n```mermaid\nflowchart LR\n subgraph Left[\"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 Right[\"users (right 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 Result[\"RIGHT JOIN Result\"]\n R1[\"stream 1 + alice_m\"]:::green\n R2[\"stream 3 + bob_jones\"]:::green\n R3[\"stream 4 + carol_uk\"]:::green\n R6[\"NULL + frank_j\"]:::red\n end\n\n S1 --> R1\n U1 --> R1\n S3 --> R2\n U2 --> R2\n S4 --> R3\n U3 --> 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 behavior: `frank_j` has no matching stream, but RIGHT JOIN keeps the row with NULLs for the left (streams) columns.\n\n#### RIGHT JOIN vs RIGHT OUTER JOIN\n\nJust like LEFT JOIN, the word OUTER is optional. `RIGHT JOIN` and `RIGHT OUTER JOIN` are identical. Most developers use the shorter form.\n\n---\n\n# Converting Between LEFT and RIGHT JOIN\n\nEvery RIGHT JOIN can be rewritten as a LEFT JOIN by swapping the table order, and vice versa. The two forms are logically equivalent.\n\n#### The Rule\n\n\n```shell\nA RIGHT JOIN B ON condition\n```\n\n\nis the same as:\n\n\n```shell\nB LEFT JOIN A ON condition\n```\n\n\nSwap the tables and change RIGHT to LEFT (or LEFT to RIGHT). The ON condition stays the same.\n\n#### Example 1: Preserving All Artists\n\nThis RIGHT JOIN keeps every artist and shows their album count:\n\n\n```sql\nSELECT ar.name, COUNT(al.album_id) AS album_count\nFROM albums al\nRIGHT JOIN artists ar ON al.artist_id = ar.artist_id\nGROUP BY ar.name\nORDER BY album_count DESC;\n```\n\n\nThe equivalent LEFT JOIN swaps the table order:\n\n\n```sql\nSELECT ar.name, COUNT(al.album_id) AS album_count\nFROM artists ar\nLEFT JOIN albums al ON ar.artist_id = al.artist_id\nGROUP BY ar.name\nORDER BY album_count DESC;\n```\n\n\nBoth return all five artists with their album counts. In our sample data every artist has exactly one album, so both queries return the same five rows with `album_count = 1`. If an artist had no albums, they would still appear with `album_count = 0`.\n\n#### Example 2: Anti-Join with RIGHT JOIN\n\nFinding tracks that have never been streamed, using RIGHT JOIN:\n\n\n```sql\nSELECT t.title\nFROM streams s\nRIGHT JOIN tracks t ON s.track_id = t.track_id\nWHERE s.stream_id IS NULL;\n```\n\n\nThe equivalent LEFT JOIN:\n\n\n```sql\nSELECT t.title\nFROM tracks t\nLEFT JOIN streams s ON t.track_id = s.track_id\nWHERE s.stream_id IS NULL;\n```\n\n\nBoth return `Pulse` (track 10), the only track with no streams. The anti-join pattern works identically with RIGHT JOIN. You check for NULL on the opposite table's key, the left table in this case.\n\n#### Example 3: Multi-Table Conversion\n\nWhen a query uses multiple joins, converting a RIGHT JOIN means you only need to swap the two tables involved in that specific join:\n\n\n```sql\n-- RIGHT JOIN version: preserve all genres\nSELECT g.name AS genre_name, COUNT(al.album_id) AS album_count\nFROM albums al\nRIGHT JOIN genres g ON al.genre_id = g.genre_id\nGROUP BY g.name\nORDER BY album_count DESC;\n```\n\n\n\n```sql\n-- Equivalent LEFT JOIN version\nSELECT g.name AS genre_name, COUNT(al.album_id) AS album_count\nFROM genres g\nLEFT JOIN albums al ON g.genre_id = al.genre_id\nGROUP BY g.name\nORDER BY album_count DESC;\n```\n\n\nBoth queries return all four genres with their album counts.\n\nThe conversion is mechanical: swap the two table positions, change the join direction, keep the ON condition as-is.\n\n\n```mermaid\nflowchart LR\n subgraph RJ[\"RIGHT JOIN\"]\n A1[\"albums\"]:::orange\n RK[\"RIGHT JOIN\"]:::cyan\n B1[\"artists\"]:::cyan\n end\n\n subgraph Equiv[\"Equivalent\"]\n EQ[\"=\"]:::green\n end\n\n subgraph LJ[\"LEFT JOIN\"]\n B2[\"artists\"]:::cyan\n LK[\"LEFT JOIN\"]:::cyan\n A2[\"albums\"]:::orange\n end\n\n RJ --> Equiv --> LJ\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```\n\n\n---\n\n# When to Use RIGHT JOIN\n\nIn practice, RIGHT JOIN is uncommon. Most SQL style guides recommend LEFT JOIN because it reads naturally: \"start with this table, and optionally bring in data from that table.\" The preserved table comes first, which matches how most people think about the query.\n\nThat said, there are a few situations where RIGHT JOIN appears:\n\n#### Legacy or Generated Code\n\nOlder queries, auto-generated SQL from ORMs or reporting tools, and stored procedures sometimes use RIGHT JOIN. Being able to read and convert them is a practical skill.\n\n#### Preserving a Later Table in a Join Chain\n\nWhen a query joins several tables in sequence and you want to preserve all rows from the last table in the chain, RIGHT JOIN avoids rearranging the entire FROM clause.\n\nConsider a query that starts with `streams`, joins through `tracks` and `albums`, and needs to preserve all artists:\n\n\n```sql\nSELECT ar.name AS artist_name,\n COUNT(s.stream_id) AS stream_count\nFROM streams s\nJOIN tracks t ON s.track_id = t.track_id\nJOIN albums al ON t.album_id = al.album_id\nRIGHT JOIN artists ar ON al.artist_id = ar.artist_id\nGROUP BY ar.name\nORDER BY stream_count DESC;\n```\n\n\nWith LEFT JOIN, you would need to restructure the query to start from `artists` and reverse the join direction:\n\n\n```sql\nSELECT ar.name AS artist_name,\n COUNT(s.stream_id) AS stream_count\nFROM artists ar\nLEFT JOIN albums al ON ar.artist_id = al.artist_id\nLEFT JOIN tracks t ON al.album_id = t.album_id\nLEFT JOIN streams s ON t.track_id = s.track_id\nGROUP BY ar.name\nORDER BY stream_count DESC;\n```\n\n\nThe LEFT JOIN version is more readable and is what most developers would write from scratch. But if you are modifying an existing query that already starts with `streams` and joins several tables, adding a RIGHT JOIN at the end can be simpler than rewriting the entire query.\n\n#### Readability Is the Deciding Factor\n\nThere is no performance difference between LEFT JOIN and RIGHT JOIN. The database engine treats them identically after resolving the table order. The choice is purely about readability. In almost every case, rewriting a RIGHT JOIN as a LEFT JOIN makes the query clearer, which is why most teams prefer LEFT JOIN.\n\n---\n\n# Common Pitfalls\n\nThe same pitfalls that apply to LEFT JOIN apply to RIGHT JOIN, just mirrored.\n\n#### ON vs WHERE Still Matters\n\nConditions on the left table (the optional side in a RIGHT JOIN) behave differently in ON vs WHERE, exactly as described in the _LEFT JOIN_ chapter for the right table.\n\n\n```sql\n-- ON: preserves all artists, filters which albums match\nSELECT ar.name, al.title\nFROM albums al\nRIGHT JOIN artists ar ON al.artist_id = ar.artist_id\n AND al.release_date >= '2024-01-01'\nORDER BY ar.name;\n```\n\n\nThis returns all five artists. Artists whose albums were released before 2024 show NULLs for `al.title`. The ON condition narrowed the match without removing unmatched right rows.\n\n\n```sql\n-- WHERE: removes artists whose album doesn't match the filter\nSELECT ar.name, al.title\nFROM albums al\nRIGHT JOIN artists ar ON al.artist_id = ar.artist_id\nWHERE al.release_date >= '2024-01-01'\nORDER BY ar.name;\n```\n\n\nThis returns only artists with albums released in 2024 or later (Aiko with Sakura, DJ Pulse with Bass Drop). The WHERE clause filters out NULL-padded rows, converting the RIGHT JOIN into an INNER JOIN.\n\nThe rule is the same as with LEFT JOIN: to narrow the match without losing unmatched rows, put the filter in ON. To filter the final result (accepting that unmatched rows will be removed), put it in WHERE.\n\n#### Mixing with Other Join Types\n\nAn INNER JOIN or LEFT JOIN earlier in the chain does not affect a RIGHT JOIN's guarantee to preserve all right-side rows. But a WHERE condition on any column from the left side can still filter out the NULL-padded rows.\n\n**What goes wrong here?**\n\n\n```sql\nSELECT s.started_at, t.title, ar.name AS artist_name\nFROM streams s\nJOIN tracks t ON s.track_id = t.track_id\nJOIN albums al ON t.album_id = al.album_id\nRIGHT JOIN artists ar ON al.artist_id = ar.artist_id\nWHERE t.duration_seconds > 300;\n```\n\n\nThe RIGHT JOIN preserves all artists, but `WHERE t.duration_seconds > 300` removes any row where `t.duration_seconds` is NULL. Artists with no streams (and therefore no track data on the left side) get filtered out.\n\n**Fix:** Move the filter to the appropriate ON clause, or accept that you are intentionally filtering the result.","pageType":"sql"}

RIGHT JOIN

Ashish Pratap Singh

Get Premium