CROSS JOIN in SQL
By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17
Before this INNER JOIN
What is CROSS JOIN in SQL?
CROSS JOIN returns every possible combination of rows from two tables.
You're building a billing report template. You need one row for every user paired with every billing period — even if the user has no activity in that period yet. There's no shared ID to join on. You don't want matches; you want all combinations. That's CROSS JOIN.
With INNER JOIN and LEFT JOIN, the ON clause defines which rows belong together. CROSS JOIN has no ON clause. Every row in the left table pairs with every row in the right table — the full cartesian product.
How do you write a CROSS JOIN in SQL?
Here's the user-period grid:
SELECT u.id AS user_id, p.name AS period_name
FROM users u
CROSS JOIN periods pIf users has 50 rows and periods has 12 rows, this returns 600 rows — every user paired with every period. That's the complete grid.
Can you filter a CROSS JOIN with WHERE?
You can filter the cross product with WHERE:
SELECT u.id AS user_id, u.name AS user_name, p.name AS period_name
FROM users u
CROSS JOIN periods p
WHERE u.country = 'US'Only US users are included, but each US user still gets paired with every period. The filter narrows which rows enter the cross product, not how the pairing works.
You can also combine CROSS JOIN with a regular JOIN in the same query:
SELECT e.name AS employee_name, d.name AS department_name FROM employees e CROSS JOIN departments d
Every employee paired with every department — not just their own. Useful for generating comparison matrices, testing coverage, or building template rows for reporting.
Why is an unfiltered CROSS JOIN dangerous?
The one thing that trips people up: accidentally running an unfiltered CROSS JOIN on large tables.
CROSS JOIN scales multiplicatively. Two tables with 10,000 rows each produce 100,000,000 rows. That's not a slow query — it's a query that may never finish, or one that takes down a database. Always know the row counts before running a CROSS JOIN without a filter, and add WHERE conditions to limit the output to what you actually need.
Practice CROSS JOIN in SQL
Streamhub's analytics team is building a quarterly reporting template and needs a complete grid of every user across every reporting period — one row per user-period combination, regardless of whether any data appears for that pairing.
Write a query to return the user ID and period name for every possible combination.
Assumptions:
- The
userstable contains every account on the platform. - The
periodstable contains the calendar windows used to bucket metrics (Q1, Q2, etc.). - The output's row count is the product of the two tables' row counts (every user paired with every period).
Output:
- One row per user-period combination, with columns
user_idandperiod_name.
Schema · analytics5 tables? = nullable
Run previews · Check grades
Write a query, then run it to see results here.
The full breakdown walks through the shape, each clause, why this approach beats the alternatives, and the trap to avoid.
See the full worked solution9 CROSS JOIN practice problems
Write a query to return the user ID and period name for every possible combination.
Write a query to return the employee name and department name for every combination.
Write a query to return the product name and category name for every possible combination.
Write a query to return the user ID and period name for every pro-user-quarter combination.
Write a query to return the user ID, user name, and period name for every US-user-quarter combination.
Write a query to return the employee name and department name for every Engineering-employee × department combination.
Write a query to return each premium product paired with every category, returning the product name, price, and category name.
Write a query that produces the cross-product of products and categories and then narrows it to the actual assignments.
Write a query to return the user ID, session ID, and period name for every (session × period) combination.
Start learning to practice all 9 CROSS JOIN problems, with instant grading and mastery tracking.
Common questions about CROSS JOIN
How many rows does a CROSS JOIN produce?
The two row counts multiplied together. Seventy customers crossed with sixty-three products gives four thousand four hundred and ten rows. That growth is why an unfiltered cross join on two large tables is something to check before you run it, not after.
Is writing two tables separated by a comma the same as CROSS JOIN?
Yes, they produce the same rows. The spelled-out form is worth preferring because a comma looks like a typo, and a reader cannot tell whether you meant to cross the tables or forgot to write a join condition.
Does a CROSS JOIN take an ON clause?
No, and adding one is a syntax error. A cross join pairs every row with every other row, so there is no condition to state. The moment you want a condition you want one of the other join types instead.
