When every join you have written pairs rows that match, it is easy to forget a join can also pair rows that have nothing to do with each other. A CROSS JOIN does exactly that: it takes every row of one table and pairs it with every row of the other, with no matching condition at all.
That sounds pointless until you need it. Building a grid of every department against every region, generating every employee as a candidate reviewer of every project, or constructing a report skeleton that you will later fill in — all of these want every combination, not just the matching ones. The deliberate CROSS JOIN exists for them. The trouble is that the same shape also appears by accident, and an accidental cross join is one of the most common reasons a query returns ten times more rows than anyone expected.
flowchart TD A["table A<br/>3 rows"] --> X["CROSS JOIN"] B["table B<br/>2 rows"] --> X X --> R["result<br/>3 x 2 = 6 rows"] style A fill:#0e7490,color:#fff style B fill:#0e7490,color:#fff style X fill:#1e293b,color:#fff style R fill:#0e7490,color:#fff
No ON, because there is nothing to match
Every join so far carried an ON that declared how two rows count as a match. A CROSS JOIN has no ON, because it is not matching — it is combining unconditionally. The keyword says so plainly:
SELECT e.name, d.name
FROM employees e
CROSS JOIN departments d;
There is no e.department_id = d.id here, and that absence is the point. Every one of the 15 employees is paired with every one of the 5 departments, producing 75 rows whether or not any real relationship exists between a given pair.
SELECT e.name AS employee, d.name AS department FROM employees e CROSS JOIN departments d;
The count always multiplies
A cross join's size is the product of its inputs: N rows on the left times M rows on the right gives N times M rows. That is its superpower and its danger in one rule. On the small seed here, 15 times 5 is a tidy 75; on a real table of fifty thousand customers crossed with a hundred countries, the same query returns five million rows and makes the database work far harder than it should.
So reach for CROSS JOIN when you genuinely need the full grid, and reach for something tighter — a join with an ON, or a WHERE that trims one side first — when you only need the rows that relate.
flowchart LR S["meant: each employee<br/>with their own department"] --> X["linked the tables?<br/>(ON or WHERE)"] X -->|"no"| C["accident:<br/>15 x 5 = 75 rows"] X -->|"yes"| K["correct:<br/>about 15 rows"] style C fill:#b45309,color:#fff style K fill:#0e7490,color:#fff
SELECT d.name AS department, p.name AS project FROM departments d CROSS JOIN projects p;
Say what you mean
Because the comma form hides the intent, prefer the explicit CROSS JOIN keyword whenever you actually want every combination. A reader seeing CROSS JOIN knows the explosion is deliberate; a reader seeing FROM a, b has to study the WHERE to tell a real join from an accident. The keyword costs four syllables and removes all guesswork.
You can still filter a cross join — WHERE d.city = 'Oslo' trims one side before the combinations settle — but the pairing itself stays unconditional. Filtering is not a substitute for a linking condition; it only narrows which rows enter the grid.
SELECT e.name AS employee, p.name AS project FROM employees e CROSS JOIN projects p WHERE p.budget >= 1000000 ORDER BY p.name, e.name LIMIT 10;
Produce every combination of employee and department. Return the employee name and the department name. Use the explicit CROSS JOIN keyword.
SELECT e.name, d.name FROM employees e -- cross join departments here ;
Write
CROSS JOIN departments dwith no ON clause.Every employee pairs with every department, so expect 15 x 5 rows.
SELECT e.name, d.name FROM employees e CROSS JOIN departments d;
Build the grid of every department against every project. Return the department name and the project name.
SELECT d.name, p.name FROM departments d -- cross join projects here ;
Use
CROSS JOIN projects p.5 departments x 8 projects gives 40 rows.
SELECT d.name, p.name FROM departments d CROSS JOIN projects p;
This query uses the comma form with no linking condition, so it returns 75 rows — every employee paired with every department — when it meant to pair each employee with their own department. Add the condition that ties the two tables together.
SELECT e.name, d.name FROM employees e, departments d ;
The comma form is an implicit CROSS JOIN until a WHERE links the tables.
Add
WHERE e.department_id = d.idso each employee keeps only their own department.
SELECT e.name, d.name FROM employees e, departments d WHERE e.department_id = d.id;
Using CROSS JOIN, list each project alongside every department that does not own it. Return the project name and department name, sorted by project name then department name.
SELECT p.name, d.name FROM projects p -- cross join departments, then keep only non-matching pairs here ;
Start with
CROSS JOIN departments dto make every project x department pair.Keep only the non-owners with
WHERE p.department_id <> d.id, then sort.
SELECT p.name, d.name FROM projects p CROSS JOIN departments d WHERE p.department_id <> d.id ORDER BY p.name, d.name;
A query on employees and departments returns 75 rows when you expected about 15. What is the most likely cause?
A multi-table FROM with nothing relating the tables pairs every row of one with every row of the other. 15 employees times 5 departments is exactly 75, the signature of an accidental cross join — scan the FROM for two tables with no linking condition.
Recap
CROSS JOINpairs every row of one table with every row of the other — noON, no matching.- The row count is the product: N times M.
- Use it deliberately for grids and combinations; prefer a linked join when only related rows matter.
- The comma form
FROM a, bis an implicit cross join — its danger is hiding the explosion. - A result count that is a clean multiple of a table's size is the tell-tale of an accidental Cartesian.
Next you will sharpen your joins with compound conditions and learn how NULL values behave — and misbehave — when tables are linked.