{"title":"CROSS JOIN","description":"","content":"Every join type we have covered so far uses an ON condition to match rows. CROSS JOIN is different. It has no matching condition at all. It pairs every row from the left table with every row from the right table, producing a Cartesian product. If the left table has 4 rows and the right table has 5 rows, the result has 20 rows.\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\n---\n\n# How CROSS JOIN Works\n\nCROSS JOIN takes every row from the left table and pairs it with every row from the right table. There is no ON clause and no matching condition. The result contains all possible combinations.\n\nHere is a simple example. We want every possible combination of genre and subscription tier:\n\n\n```sql\nSELECT g.name AS genre, u.subscription_tier AS tier\nFROM genres g\nCROSS JOIN (SELECT DISTINCT subscription_tier FROM users) u\nORDER BY g.name, u.tier;\n```\n\n\nThe `genres` table has 4 rows. There are 3 distinct subscription tiers (`free`, `premium`, `family`). The result has 4 x 3 = 12 rows, one for every genre-tier combination.\n\nThe following diagram shows how CROSS JOIN pairs rows. Every row on the left connects to every row on the right:\n\n\n```mermaid\nflowchart LR\n subgraph Left[\"genres\"]\n G1[\"Pop\"]:::cyan\n G2[\"Electronic\"]:::cyan\n G3[\"J-Pop\"]:::cyan\n end\n\n subgraph Right[\"tiers\"]\n T1[\"free\"]:::orange\n T2[\"premium\"]:::orange\n T3[\"family\"]:::orange\n end\n\n subgraph Result[\"CROSS JOIN Result (9 rows)\"]\n R1[\"Pop + free\"]:::green\n R2[\"Pop + premium\"]:::green\n R3[\"Pop + family\"]:::green\n R4[\"Electronic + free\"]:::green\n R5[\"Electronic + premium\"]:::green\n R6[\"Electronic + family\"]:::green\n R7[\"J-Pop + free\"]:::green\n R8[\"J-Pop + premium\"]:::green\n R9[\"J-Pop + family\"]:::green\n end\n\n G1 --> R1\n G1 --> R2\n G1 --> R3\n G2 --> R4\n G2 --> R5\n G2 --> R6\n G3 --> R7\n G3 --> R8\n G3 --> R9\n\n T1 --> R1\n T2 --> R2\n T3 --> R3\n T1 --> R4\n T2 --> R5\n T3 --> R6\n T1 --> R7\n T2 --> R8\n T3 --> R9\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\nThe diagram only shows 3 genres for readability, but the concept extends to any number of rows. With 4 genres and 3 tiers, the full result has 12 rows.\n\nCompare this to INNER JOIN, which pairs rows only when a condition is met. CROSS JOIN skips the condition entirely and pairs everything with everything.\n\n---\n\n# Explicit vs Implicit Syntax\n\nThere are three ways to write a CROSS JOIN, and all produce the same result.\n\n#### Explicit CROSS JOIN\n\nThe most readable form. It makes the intent clear:\n\n\n```sql\nSELECT g.name AS genre, ar.name AS artist\nFROM genres g\nCROSS JOIN artists ar\nORDER BY g.name, ar.name;\n```\n\n\nThis produces 4 genres x 5 artists = 20 rows.\n\n#### Comma Syntax (Implicit Cross Join)\n\nListing tables separated by commas in the FROM clause, without a WHERE condition, produces the same Cartesian product:\n\n\n```sql\nSELECT g.name AS genre, ar.name AS artist\nFROM genres g, artists ar\nORDER BY g.name, ar.name;\n```\n\n\nThis is the same query as above. The comma syntax was the only way to write joins before SQL-92 introduced the `JOIN` keyword. Without a WHERE clause to filter matches, it becomes a cross join.\n\nAs the _INNER JOIN_ chapter explained, the comma syntax is riskier because it is easy to forget a WHERE condition and accidentally produce a Cartesian product. When you actually want a cross join, using the explicit `CROSS JOIN` keyword makes that intention obvious to anyone reading the query.\n\n#### JOIN ... ON TRUE\n\nA third form that works in PostgreSQL and some other databases:\n\n\n```sql\nSELECT g.name AS genre, ar.name AS artist\nFROM genres g\nJOIN artists ar ON TRUE\nORDER BY g.name, ar.name;\n```\n\n\nThe `ON TRUE` condition matches every row to every other row, which is functionally identical to CROSS JOIN. This form is rarely used in practice, but you may encounter it in generated SQL or when a query builder does not support the CROSS JOIN keyword directly.\n\n\n| Syntax | Example | Clarity |\n|---|---|---|\n| `CROSS JOIN` | `FROM genres g CROSS JOIN artists ar` | Best. Intent is explicit. |\n| Comma | `FROM genres g, artists ar` | Ambiguous. Could be a missing WHERE. |\n| `JOIN ... ON TRUE` | `FROM genres g JOIN artists ar ON TRUE` | Uncommon. Works but not idiomatic. |\n\n\nUse the explicit `CROSS JOIN` syntax. It is the clearest way to communicate that a Cartesian product is intentional.\n\n---\n\n# Result Size and Performance\n\nThe output size of a CROSS JOIN is the product of the row counts of both tables. This grows fast:\n\n\n| Left Table Rows | Right Table Rows | Result Rows |\n|---|---|---|\n| 4 | 5 | 20 |\n| 100 | 100 | 10,000 |\n| 1,000 | 1,000 | 1,000,000 |\n| 10,000 | 10,000 | 100,000,000 |\n\n\nWith INNER JOIN or LEFT JOIN, the ON condition filters out most combinations, so the result is usually close to the size of the larger table. CROSS JOIN has no such filter. Two 10,000-row tables produce 100 million rows.\n\nThis does not mean CROSS JOIN is inherently slow. It is a tool, and the key is to use it on small sets. Cross-joining a 12-row month list with a 4-row genre list gives 48 rows. That is trivial. Cross-joining two tables with thousands of rows each is where things go wrong.\n\nA good rule: before writing a CROSS JOIN, check the row counts of both inputs. If either side has more than a few hundred rows, consider whether you actually need every combination or whether a different join type with a condition would be more appropriate.\n\n---\n\n# Use Cases\n\nCROSS JOIN is not something you use every day, but there are a few scenarios where it is the right tool.\n\n#### Generating All Combinations\n\nSometimes you need a complete matrix of options. For example, building a report that shows every artist paired with every genre, regardless of whether that artist has any albums in that genre:\n\n\n```sql\nSELECT ar.name AS artist, g.name AS genre\nFROM artists ar\nCROSS JOIN genres g\nORDER BY ar.name, g.name;\n```\n\n\nThis produces 5 artists x 4 genres = 20 rows. Every artist appears with every genre. This skeleton can be used as the basis for a report that fills in actual data with a LEFT JOIN.\n\n#### Filling Gaps with Zero Counts\n\nThis is the most practical use of CROSS JOIN. Reports often need to show all categories, including ones with zero activity. A regular GROUP BY query only returns rows for categories that have data. CROSS JOIN fills the gaps.\n\nSay you want to know how many streams each genre received. A straightforward approach:\n\n\n```sql\nSELECT g.name AS genre, COUNT(s.stream_id) AS stream_count\nFROM genres g\nINNER JOIN albums al ON g.genre_id = al.genre_id\nINNER JOIN tracks t ON al.album_id = t.album_id\nINNER JOIN streams s ON t.track_id = s.track_id\nGROUP BY g.name\nORDER BY stream_count DESC;\n```\n\n\nThis works, but it only shows genres that have at least one stream. If a genre has no albums, no tracks, or no streams, it vanishes from the result.\n\nTo include all genres, even those with zero streams, use CROSS JOIN to create the full list of genres first, then LEFT JOIN to bring in the stream data:\n\n\n```sql\nSELECT g.name AS genre,\n COUNT(s.stream_id) AS stream_count\nFROM genres g\nLEFT JOIN albums al ON g.genre_id = al.genre_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 g.name\nORDER BY stream_count DESC;\n```\n\n\nIn this case, starting from `genres` with LEFT JOINs is enough because we want all genres. But when the \"rows you want to keep\" come from two separate tables (like genres and months), CROSS JOIN becomes essential.\n\nHere is the more interesting version. You want a grid: every genre for every day in March 2024, with the stream count for that genre on that day. Days with no streams should show zero, not disappear.\n\n\n```sql\nSELECT g.name AS genre,\n d.day,\n COUNT(s.stream_id) AS stream_count\nFROM genres g\nCROSS JOIN generate_series('2024-03-01'::date, '2024-03-04'::date, '1 day') AS d(day)\nLEFT JOIN albums al ON g.genre_id = al.genre_id\nLEFT JOIN tracks t ON al.album_id = t.album_id\nLEFT JOIN streams s ON t.track_id = s.track_id\n AND s.started_at >= d.day\n AND s.started_at < d.day + INTERVAL '1 day'\nGROUP BY g.name, d.day\nORDER BY g.name, d.day;\n```\n\n\nThe CROSS JOIN between `genres` and the generated date series creates the grid skeleton: 4 genres x 4 days = 16 rows. The LEFT JOINs then attach stream counts where they exist. Genre-day combinations with no streams get a count of zero from `COUNT(s.stream_id)` because all the stream columns are NULL.\n\nThe following diagram shows the flow:\n\n\n```mermaid\nflowchart LR\n G[\"genres
(4 rows)\"]:::cyan\n D[\"date series
(4 days)\"]:::orange\n CJ[\"CROSS JOIN
(16 rows)\"]:::green\n AL[\"albums\"]:::teal\n T[\"tracks\"]:::teal\n S[\"streams\"]:::teal\n R[\"Final Result
genre + day +
stream_count\"]:::cyan\n\n G --> CJ\n D --> CJ\n CJ -->|\"LEFT JOIN\"| AL\n AL -->|\"LEFT JOIN\"| T\n T -->|\"LEFT JOIN\"| S\n S --> R\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 teal fill:#38d9a9,stroke:#000,color:#000\n```\n\n\nNotice that the stream date filter (`s.started_at >= d.day AND s.started_at < d.day + INTERVAL '1 day'`) goes in the ON clause of the LEFT JOIN, not in WHERE. Putting it in WHERE would turn the LEFT JOIN into an INNER JOIN and eliminate the zero-count rows. The _LEFT JOIN_ chapter covers this distinction in detail.\n\n#### Generating Date and Number Series\n\nPostgreSQL's `generate_series` function is a common partner for CROSS JOIN. It produces a set of sequential values that you can cross with another table to build report scaffolding.\n\nGenerate one row per month in 2024:\n\n\n```sql\nSELECT TO_CHAR(month, 'YYYY-MM') AS month_label\nFROM generate_series('2024-01-01'::date, '2024-12-01'::date, '1 month') AS s(month);\n```\n\n\nCross it with genres to build a 12-month x 4-genre grid:\n\n\n```sql\nSELECT g.name AS genre,\n TO_CHAR(m.month, 'YYYY-MM') AS month_label\nFROM genres g\nCROSS JOIN generate_series('2024-01-01'::date, '2024-12-01'::date, '1 month') AS m(month)\nORDER BY g.name, m.month;\n```\n\n\nThis produces 48 rows (4 genres x 12 months). You can then LEFT JOIN stream data onto this grid to fill in counts.\n\n`generate_series` is PostgreSQL-specific. MySQL and SQL Server have different ways to produce sequences:\n\n\n| Database | Date series approach |\n|---|---|\n| PostgreSQL | `generate_series('2024-01-01'::date, '2024-12-01'::date, '1 month')` |\n| MySQL | Recursive CTE or a pre-populated calendar table |\n| SQL Server | Recursive CTE or a numbers table with `DATEADD` |\n\n\nThe CROSS JOIN pattern itself works the same way across all databases. Only the series generation differs.\n\n---\n\n# CROSS JOIN with a WHERE Clause\n\nAdding a WHERE clause to a CROSS JOIN filters the Cartesian product down to matching rows. This is functionally identical to an INNER JOIN:\n\n\n```sql\n-- CROSS JOIN with WHERE\nSELECT u.username, s.stream_id\nFROM users u\nCROSS JOIN streams s\nWHERE u.user_id = s.user_id\nORDER BY s.started_at;\n\n-- Equivalent INNER JOIN\nSELECT u.username, s.stream_id\nFROM users u\nINNER JOIN streams s ON u.user_id = s.user_id\nORDER BY s.started_at;\n```\n\n\nBoth queries return the same result. The database optimizer typically produces the same execution plan for both.\n\nHowever, using CROSS JOIN with WHERE to simulate an INNER JOIN is poor style. It hides the join condition in the WHERE clause, making the query harder to read. Worse, it makes it unclear whether the Cartesian product is intentional or whether someone forgot to write a proper JOIN. Use INNER JOIN when you want matched rows and CROSS JOIN when you want all combinations.\n\n---\n\n# Common Mistakes\n\n#### Accidental Cross Joins\n\nThe most dangerous cross join is the one you did not mean to write. With comma syntax, forgetting a WHERE condition silently produces a Cartesian product:\n\n**What goes wrong here?**\n\n\n```sql\nSELECT u.username, t.title\nFROM users u, tracks t;\n-- Missing: WHERE or JOIN condition\n-- Result: 6 users x 10 tracks = 60 rows instead of the expected filtered set\n```\n\n\nWith 6 users and 10 tracks, this produces 60 rows. With production-sized tables, this can generate millions of rows and consume significant memory and CPU time.\n\n**Fix:** Use explicit `JOIN ... ON` syntax. If you forget the ON clause, PostgreSQL raises a syntax error instead of silently running a cross join:\n\n\n```sql\n-- This causes a syntax error, catching the bug early\nSELECT u.username, t.title\nFROM users u\nINNER JOIN tracks t; -- ERROR: missing ON clause\n```\n\n\n#### Cross Joining Large Tables\n\nCROSS JOIN is safe on small inputs. It becomes a problem when used carelessly on large tables:\n\n\n```sql\n-- Dangerous: if streams has 1 million rows and tracks has 10,000 rows,\n-- this produces 10 billion rows\nSELECT s.stream_id, t.title\nFROM streams s\nCROSS JOIN tracks t;\n```\n\n\nBefore writing a CROSS JOIN, always check the row counts:\n\n\n```sql\nSELECT COUNT(*) FROM streams; -- How many rows on the left?\nSELECT COUNT(*) FROM tracks; -- How many rows on the right?\n-- Multiply them to predict the output size\n```\n\n\nIf either table has more than a few hundred rows, reconsider whether CROSS JOIN is the right approach.","pageType":"sql"}

CROSS JOIN

Ashish Pratap Singh

Get Premium