CROSS JOIN

A CROSS JOIN is like pairing every shirt with every pair of pants to see all possible outfits. Every row from one table meets every row from the other.

In this page:

What is a CROSS JOIN?
Explicit CROSS JOIN Syntax
Implicit CROSS JOIN
CROSS JOIN with a WHERE Filter
Practical Use Cases for CROSS JOIN

Syntax

sql

SELECT columns
FROM table1
CROSS JOIN table2;

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?

sql

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

sql

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

sql

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

sql

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

sql

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:

← Previous Lesson SELF JOIN Next Lesson → Multiple Table Joins