CROSS JOIN
CROSS JOIN pairs every row of one table with every row of another.
In this page:
Syntax
SELECT columns
FROM table1
CROSS JOIN table2;CROSS JOIN
The result has rows(A) times rows(B) rows and no ON condition. It is useful for generating combinations such as sizes by colours. Accidental cartesian products from a missing join condition are a common performance bug.
Note:
The row count is the product of both tables, so keep inputs small.
Example: CROSS JOIN
CREATE TABLE sizes (size TEXT);
CREATE TABLE colors (color TEXT);
INSERT INTO sizes VALUES ('S'), ('M'), ('L');
INSERT INTO colors VALUES ('red'), ('blue');
SELECT size, color FROM sizes CROSS JOIN colors ORDER BY size, color;
SELECT COUNT(*) AS combinations FROM sizes CROSS JOIN colors;
-- Output:
-- size | color
-- L | blue
-- L | red
-- M | blue
-- M | red
-- S | blue
-- S | red
-- combinations
-- 6Login to try C/C++/Java/PHP code in the editor
Related Topics
Common Mistakes
- Forgetting the ON condition and creating a cross join accidentally
- Using it on big tables
- Expecting matches
Chapter Summary
- CROSS JOIN pairs all rows
- Result size is the product
- No ON condition
- Great for combinations
🔒
Chapter Quiz — Complete all 7 topics to unlock
0/7 topics done
Complete these topics first: