CROSS JOIN
In this page:
What is a CROSS JOIN?
A CROSS JOIN produces a Cartesian product between two tables. It pairs every single row from the first table with every single row from the second table. This is helpful when you want to see all possible combinations of items, like every size paired with every color.
Example: What is a CROSS JOIN?
CREATE TABLE sizes (size TEXT);
CREATE TABLE colors (color TEXT);
INSERT INTO sizes VALUES ('S'), ('M');
INSERT INTO colors VALUES ('Red'), ('Blue');
SELECT sizes.size, colors.color FROM sizes CROSS JOIN colors;Explicit CROSS JOIN Syntax
You can explicitly use the CROSS JOIN keywords in your query without any ON condition. This clearly shows other developers that you intend to create a Cartesian product of the two tables on purpose, not by accident.
Example: Explicit CROSS JOIN Syntax
CREATE TABLE sizes (size TEXT);
CREATE TABLE colors (color TEXT);
INSERT INTO sizes VALUES ('S'), ('M');
INSERT INTO colors VALUES ('Red'), ('Blue');
-- Explicit CROSS JOIN: intent is clear to other developers
SELECT sizes.size, colors.color FROM sizes CROSS JOIN colors;Implicit CROSS JOIN
You can write a cross join implicitly by listing the tables separated by a comma in the FROM clause with no join condition. This behaves exactly like an explicit CROSS JOIN, though it's easy to write by mistake if you forget a WHERE condition meant to link them.
Example: Implicit CROSS JOIN
CREATE TABLE sizes (size TEXT);
CREATE TABLE colors (color TEXT);
INSERT INTO sizes VALUES ('S'), ('M');
INSERT INTO colors VALUES ('Red'), ('Blue');
-- Implicit cross join: comma-separated, no ON condition
SELECT sizes.size, colors.color FROM sizes, colors;CROSS JOIN with a WHERE Filter
You can filter the combinations produced by a CROSS JOIN down to a useful subset. Simply add a standard WHERE clause to limit the final results based on your requirements, effectively turning it into a manual join condition.
Example: CROSS JOIN with a WHERE Filter
CREATE TABLE sizes (size TEXT);
CREATE TABLE colors (color TEXT);
INSERT INTO sizes VALUES ('S'), ('M');
INSERT INTO colors VALUES ('Red'), ('Blue');
SELECT sizes.size, colors.color FROM sizes CROSS JOIN colors WHERE colors.color = 'Red';Practical Use Cases for CROSS JOIN
CROSS JOIN is great for generating matrix structures where every combination genuinely matters. This includes matching card suits with ranks, or pairing menu food items with drink choices to build a complete combo list.
Example: Practical Use Cases for CROSS JOIN
CREATE TABLE suits (suit TEXT);
CREATE TABLE ranks (rank_name TEXT);
INSERT INTO suits VALUES ('Hearts'), ('Spades');
INSERT INTO ranks VALUES ('Ace'), ('King');
SELECT ranks.rank_name, suits.suit FROM ranks CROSS JOIN suits;Chapter Quiz — Complete all 10 topics to unlock
0/10 topics done
Complete these topics first: