{"title":"Self Join","description":"","content":"Every join we have covered so far combines rows from two different tables. But some relationships exist within a single table: an artist managed by another artist, two users who follow each other, two tracks on the same album with the same duration. A self join handles this by joining a table to itself using two different aliases.\n\n---\n\n# Sample Data\n\nThis chapter uses the `genres`, `users`, `artists`, `albums`, `tracks`, and `follows` 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 (5, 'Synthwave', 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, manager_artist_id)\nVALUES\n (1, 'Luna Wave', 'US', TRUE, NULL),\n (2, 'The Midnight', 'GB', TRUE, 1),\n (3, 'Neon Dreams', 'US', FALSE, 1),\n (4, 'Aiko', 'JP', TRUE, 2),\n (5, 'DJ Pulse', 'US', FALSE, 3),\n (6, 'Echo Valley', 'GB', TRUE, 2),\n (7, 'Starfall', 'CA', FALSE, NULL),\n (8, 'Violet Sun', 'US', TRUE, 3);\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', 210, 1),\n (2, 1, 'Tidal', 237, 2),\n (3, 1, 'Driftwood', 210, 3),\n (4, 2, 'Afterglow', 198, 1),\n (5, 2, 'Shadows', 312, 2),\n (6, 3, 'Neon Skyline', 275, 1),\n (7, 3, 'Downtown', 275, 2),\n (8, 4, 'Bloom', 348, 1),\n (9, 4, 'Petals', 90, 2),\n (10, 5, 'Drop Zone', 420, 1),\n (11, 5, 'Pulse', 180, 2);\n\nINSERT INTO follows (follower_user_id, followed_user_id, followed_at)\nVALUES\n (1, 2, '2023-01-10 10:00:00+00'),\n (2, 1, '2023-01-12 14:30:00+00'),\n (1, 3, '2023-02-05 09:00:00+00'),\n (3, 1, '2023-02-06 11:00:00+00'),\n (2, 3, '2023-03-15 16:00:00+00'),\n (4, 1, '2023-04-20 08:00:00+00'),\n (5, 2, '2023-05-01 12:00:00+00'),\n (2, 5, '2023-05-03 10:00:00+00'),\n (6, 4, '2023-06-10 15:00:00+00'),\n (3, 6, '2023-07-22 09:30:00+00');\n```\n\n\n---\n\n# When a Table References Itself\n\nLook at the `artists` table. The `manager_artist_id` column is a foreign key that points back to `artist_id` in the same table. Luna Wave (artist_id 1) manages The Midnight and Neon Dreams. The Midnight (artist_id 2) manages Aiko and Echo Valley. The relationship between an artist and their manager lives entirely within one table.\n\nThis is called a self-referencing foreign key. The StreamFlow schema has several of them:\n\n- `artists.manager_artist_id` references `artists.artist_id` (management hierarchy)\n- `genres.parent_genre_id` references `genres.genre_id` (genre hierarchy)\n- `playlists.forked_from_playlist_id` references `playlists.playlist_id` (playlist forks)\n\nThe `follows` table is a different kind of self-reference. Both `follower_user_id` and `followed_user_id` reference `users.user_id`, so the table captures a relationship between two rows in the same `users` table.\n\nThe following diagram shows how the `artists` table references itself:\n\n\n```mermaid\nflowchart LR\n subgraph artists_table[\"artists table\"]\n AID[\"artist_id (PK)\"]:::cyan\n NAME[\"name\"]:::teal\n MID[\"manager_artist_id (FK)\"]:::orange\n end\n\n MID -->|\"references\"| AID\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\nTo query these relationships, you need a way to treat one table as if it were two. That is what a self join does.\n\n---\n\n# Self Join Mechanics\n\nA self join is not a new join type. It is a regular INNER JOIN (or LEFT JOIN, or any other join) where both sides happen to be the same table. The only special requirement is that you must give each \"copy\" of the table a different alias. Without aliases, the database cannot tell which instance of the table a column belongs to.\n\nHere is the simplest self join: find each artist and the name of their manager.\n\n\n```sql\nSELECT artist.name AS artist_name,\n manager.name AS manager_name\nFROM artists artist\nINNER JOIN artists manager ON artist.manager_artist_id = manager.artist_id;\n```\n\n\nThe database treats `artists artist` and `artists manager` as two separate copies of the same table. The ON clause says: \"match each row in the `artist` copy to the row in the `manager` copy whose `artist_id` equals the artist's `manager_artist_id`.\" Artists with `manager_artist_id` set to NULL have no match, so INNER JOIN excludes them.\n\nThe following table traces how the join processes each artist:\n\n\n| artist.artist_id | artist.name | artist.manager_artist_id | manager.artist_id | manager.name | Match? |\n|---|---|---|---|---|---|\n| 1 | Luna Wave | NULL | — | — | No (NULL) |\n| 2 | The Midnight | 1 | 1 | Luna Wave | Yes |\n| 3 | Neon Dreams | 1 | 1 | Luna Wave | Yes |\n| 4 | Aiko | 2 | 2 | The Midnight | Yes |\n| 5 | DJ Pulse | 3 | 3 | Neon Dreams | Yes |\n| 6 | Echo Valley | 2 | 2 | The Midnight | Yes |\n| 7 | Starfall | NULL | — | — | No (NULL) |\n| 8 | Violet Sun | 3 | 3 | Neon Dreams | Yes |\n\n\nThe result has 6 rows, one for each artist that has a manager.\n\nNotice the aliases. Using `artist` and `manager` instead of `a1` and `a2` makes the query readable. The alias tells you what role each copy of the table plays in the relationship.\n\n---\n\n# Hierarchy Pattern\n\nThe artist-manager relationship forms a tree. Luna Wave sits at the top, managing The Midnight and Neon Dreams. The Midnight manages Aiko and Echo Valley. Neon Dreams manages DJ Pulse and Violet Sun.\n\n\n```mermaid\nflowchart TD\n LW[\"Luna Wave\"]:::cyan\n TM[\"The Midnight\"]:::orange\n ND[\"Neon Dreams\"]:::orange\n AI[\"Aiko\"]:::teal\n EV[\"Echo Valley\"]:::teal\n DJ[\"DJ Pulse\"]:::teal\n VS[\"Violet Sun\"]:::teal\n SF[\"Starfall\"]:::green\n\n LW --> TM\n LW --> ND\n TM --> AI\n TM --> EV\n ND --> DJ\n ND --> VS\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 classDef green fill:#69db7c,stroke:#000,color:#000\n```\n\n\nStarfall has no manager and manages nobody, so it sits alone. The diagram shows a two-level hierarchy under Luna Wave.\n\nA self join traverses one level at a time. The query above finds each artist's direct manager. You can add more detail to the result:\n\n\n```sql\nSELECT artist.name AS artist_name,\n artist.country AS artist_country,\n manager.name AS manager_name,\n manager.country AS manager_country\nFROM artists artist\nINNER JOIN artists manager ON artist.manager_artist_id = manager.artist_id;\n```\n\n\nThis lets you answer questions like \"which artists are managed by someone from a different country?\" by filtering on the result.\n\n#### Artists Managed by Someone in the Same Country\n\nA common interview question: find artists whose manager is from the same country.\n\n\n```sql\nSELECT artist.name AS artist_name,\n manager.name AS manager_name,\n artist.country\nFROM artists artist\nINNER JOIN artists manager ON artist.manager_artist_id = manager.artist_id\nWHERE artist.country = manager.country;\n```\n\n\nThe self join connects each artist to their manager. The WHERE clause keeps only pairs where both share the same country. From the sample data, Neon Dreams (US) is managed by Luna Wave (US), DJ Pulse (US) is managed by Neon Dreams (US), Violet Sun (US) is managed by Neon Dreams (US), and Echo Valley (GB) is managed by The Midnight (GB).\n\n#### Genre Hierarchy\n\nThe same pattern works with the `genres` table. Each genre can have a `parent_genre_id` pointing to another genre:\n\n\n```sql\nSELECT child.name AS sub_genre,\n parent.name AS parent_genre\nFROM genres child\nINNER JOIN genres parent ON child.parent_genre_id = parent.genre_id;\n```\n\n\nJ-Pop's parent is Pop. House and Synthwave's parent is Electronic. Pop and Electronic have no parent (their `parent_genre_id` is NULL), so they do not appear in the result.\n\n#### Going Deeper: Multi-Level Self Joins\n\nA single self join covers one level. To find an artist's manager's manager, add a second self join:\n\n\n```sql\nSELECT artist.name AS artist_name,\n manager.name AS manager_name,\n grand_manager.name AS grand_manager_name\nFROM artists artist\nINNER JOIN artists manager ON artist.manager_artist_id = manager.artist_id\nINNER JOIN artists grand_manager ON manager.manager_artist_id = grand_manager.artist_id;\n```\n\n\nThis returns only artists where both the manager and the grand-manager exist. Aiko's manager is The Midnight, whose manager is Luna Wave. DJ Pulse's manager is Neon Dreams, whose manager is Luna Wave.\n\nEach additional self join adds one more level. For hierarchies of unknown depth, this approach breaks down because you would need an unknown number of joins. Recursive CTEs solve that problem, and a later chapter covers them.\n\n---\n\n# Pair Pattern: Mutual Follows\n\nThe `follows` table records who follows whom. Each row has a `follower_user_id` and a `followed_user_id`. Finding users who follow each other (mutual follows) requires checking whether both directions exist. This is a self join on the `follows` table.\n\n\n```sql\nSELECT f1.follower_user_id AS user_a,\n f1.followed_user_id AS user_b\nFROM follows f1\nINNER JOIN follows f2 ON f1.follower_user_id = f2.followed_user_id\n AND f1.followed_user_id = f2.follower_user_id;\n```\n\n\nThe ON clause says: find a row in `f2` where the follower and followed are swapped compared to `f1`. If user 1 follows user 2 (a row in `f1`) and user 2 follows user 1 (the matching row in `f2`), the join produces a match.\n\nThe problem is that this query returns duplicate pairs. If alice_m and bob_jones follow each other, the result includes both (1, 2) and (2, 1). Each mutual relationship appears twice.\n\n#### Eliminating Duplicate Pairs\n\nThe fix is to add a filter that keeps only one ordering of each pair:\n\n\n```sql\nSELECT f1.follower_user_id AS user_a,\n f1.followed_user_id AS user_b\nFROM follows f1\nINNER JOIN follows f2 ON f1.follower_user_id = f2.followed_user_id\n AND f1.followed_user_id = f2.follower_user_id\nWHERE f1.follower_user_id < f1.followed_user_id;\n```\n\n\nThe `<` operator keeps only the pair where user_a's ID is smaller than user_b's ID. This cuts the result in half and guarantees each mutual follow appears exactly once.\n\nWhy `<` instead of `<>`? Using `<>` prevents a row from matching itself (which cannot happen here anyway since a user cannot follow themselves with a composite primary key). But `<>` still produces both (1, 2) and (2, 1). Only `<` keeps a single canonical ordering.\n\nTo show usernames instead of IDs, join the `users` table:\n\n\n```sql\nSELECT u1.username AS user_a,\n u2.username AS user_b\nFROM follows f1\nINNER JOIN follows f2 ON f1.follower_user_id = f2.followed_user_id\n AND f1.followed_user_id = f2.follower_user_id\nINNER JOIN users u1 ON f1.follower_user_id = u1.user_id\nINNER JOIN users u2 ON f1.followed_user_id = u2.user_id\nWHERE f1.follower_user_id < f1.followed_user_id;\n```\n\n\nFrom the sample data, the mutual follow pairs are: alice_m and bob_jones (IDs 1 and 2), alice_m and carol_uk (IDs 1 and 3), and bob_jones and emma_dev (IDs 2 and 5).\n\n---\n\n# Comparison Pattern: Same-Table Row Matching\n\nSelf joins are not limited to foreign key relationships. You can join a table to itself on any shared attribute to compare rows.\n\n#### Tracks with Identical Durations on the Same Album\n\nFind pairs of tracks on the same album that have the same duration:\n\n\n```sql\nSELECT t1.title AS track_a,\n t2.title AS track_b,\n t1.duration_seconds,\n al.title AS album\nFROM tracks t1\nINNER JOIN tracks t2 ON t1.album_id = t2.album_id\n AND t1.duration_seconds = t2.duration_seconds\nINNER JOIN albums al ON t1.album_id = al.album_id\nWHERE t1.track_id < t2.track_id;\n```\n\n\nThe ON clause matches tracks that share the same album and the same duration. The `WHERE t1.track_id < t2.track_id` condition serves two purposes: it prevents a track from matching itself, and it eliminates duplicate pairs. Without it, \"Sunrise\" paired with \"Driftwood\" would appear twice (once in each order), and each track would also pair with itself.\n\nFrom the sample data, two pairs match: Sunrise and Driftwood on Ocean Drive (both 210 seconds), and Neon Skyline and Downtown on City Lights (both 275 seconds).\n\n#### Comparing Durations Within an Album\n\nYou can also compare rows without requiring equality. Find all pairs of tracks on the same album where one track is longer than the other:\n\n\n```sql\nSELECT shorter.title AS shorter_track,\n longer.title AS longer_track,\n shorter.duration_seconds AS short_duration,\n longer.duration_seconds AS long_duration,\n longer.duration_seconds - shorter.duration_seconds AS difference\nFROM tracks shorter\nINNER JOIN tracks longer ON shorter.album_id = longer.album_id\nWHERE shorter.duration_seconds < longer.duration_seconds;\n```\n\n\nThe `<` in the WHERE clause does double duty: it defines the comparison (shorter vs longer) and prevents duplicate pairs (each pair appears once with the shorter track first). Using `<` instead of `<>` means you do not need a separate deduplication condition.\n\n---\n\n# Self Join with LEFT JOIN\n\nThe hierarchy queries above use INNER JOIN, which excludes artists that have no manager. If you want all artists in the result, including those at the top of the hierarchy, use LEFT JOIN instead:\n\n\n```sql\nSELECT artist.name AS artist_name,\n manager.name AS manager_name\nFROM artists artist\nLEFT JOIN artists manager ON artist.manager_artist_id = manager.artist_id;\n```\n\n\nLuna Wave and Starfall have NULL `manager_artist_id`, so they have no match in the `manager` copy. LEFT JOIN keeps them in the result with NULL for `manager_name`.\n\nTo find only the top-level artists (those with no manager), filter for the NULL:\n\n\n```sql\nSELECT artist.name AS artist_name\nFROM artists artist\nLEFT JOIN artists manager ON artist.manager_artist_id = manager.artist_id\nWHERE manager.artist_id IS NULL;\n```\n\n\nThis is a left anti-join applied to a self join. You could also write this more simply as `WHERE artist.manager_artist_id IS NULL`, which avoids the join entirely. The self-join version becomes useful when you need to verify that the referenced manager actually exists in the table, not just that the FK column is non-NULL. If data integrity is not enforced by foreign key constraints, a non-NULL `manager_artist_id` could point to a deleted or nonexistent artist.\n\n---\n\n# Common Mistakes\n\n#### Forgetting Aliases\n\n**What goes wrong here?**\n\n\n```sql\nSELECT name, name\nFROM artists\nINNER JOIN artists ON manager_artist_id = artist_id;\n```\n\n\nPostgreSQL raises an error: `table name \"artists\" specified more than once`. The database needs aliases to tell the two copies apart.\n\n**Fix:** Give each copy a meaningful alias:\n\n\n```sql\nSELECT artist.name AS artist_name,\n manager.name AS manager_name\nFROM artists artist\nINNER JOIN artists manager ON artist.manager_artist_id = manager.artist_id;\n```\n\n\n#### Getting Duplicate Pairs\n\n**What goes wrong here?**\n\n\n```sql\nSELECT f1.follower_user_id, f1.followed_user_id\nFROM follows f1\nINNER JOIN follows f2 ON f1.follower_user_id = f2.followed_user_id\n AND f1.followed_user_id = f2.follower_user_id;\n```\n\n\nEach mutual follow appears twice: (1, 2) and (2, 1).\n\n**Fix:** Add `WHERE f1.follower_user_id < f1.followed_user_id` to keep only one ordering.\n\n#### Including Self-Matches\n\n**What goes wrong here?**\n\n\n```sql\nSELECT t1.title, t2.title\nFROM tracks t1\nINNER JOIN tracks t2 ON t1.album_id = t2.album_id\n AND t1.duration_seconds = t2.duration_seconds;\n```\n\n\nEvery track matches itself because it is on the same album and has the same duration as itself. The result is polluted with rows like (\"Sunrise\", \"Sunrise\").\n\n**Fix:** Add `WHERE t1.track_id < t2.track_id` to exclude self-matches and deduplicate.\n\n#### Confusing Join Direction\n\nIn hierarchy self joins, mixing up which alias is the child and which is the parent leads to incorrect results. The ON clause `artist.manager_artist_id = manager.artist_id` reads as: \"the artist's manager_artist_id points to the manager's artist_id.\" If you reverse it (`manager.manager_artist_id = artist.artist_id`), you get a different query: find each manager and who manages them, which is the relationship in the opposite direction.\n\nRead the ON clause aloud to check the direction. \"The artist's manager ID equals the manager's ID\" is correct. \"The manager's manager ID equals the artist's ID\" asks a different question.","pageType":"sql"}

Self Join

Ashish Pratap Singh

Get Premium