CROSS JOIN cartesian product in SQL

Introduction

A CROSS JOIN combines every row from one table with every row from another table, creating all possible pairs. It helps when you want to see all combinations of two sets of data.

When you want to pair every product with every store to check availability.

When creating all possible combinations of toppings and crusts for a pizza menu.

When testing all combinations of colors and sizes for a clothing line.

When you need to generate sample data by combining two lists fully.

Syntax

SQL

SELECT *
FROM table1
CROSS JOIN table2;

CROSS JOIN returns the Cartesian product of the two tables.

If table1 has M rows and table2 has N rows, the result has M x N rows.

Examples

Combines every fruit with every color.

SQL

SELECT * FROM fruits
CROSS JOIN colors;

Pairs every employee with every department.

SQL

SELECT employees.name, departments.name
FROM employees
CROSS JOIN departments;

Sample Program

This query creates two small tables and then uses CROSS JOIN to list all fruit-color pairs.

SQL

CREATE TABLE fruits (name VARCHAR(10));
INSERT INTO fruits VALUES ('Apple'), ('Banana');

CREATE TABLE colors (name VARCHAR(10));
INSERT INTO colors VALUES ('Red'), ('Yellow');

SELECT fruits.name AS fruit, colors.name AS color
FROM fruits
CROSS JOIN colors;

Important Notes

CROSS JOIN can produce very large results if tables have many rows.

Use CROSS JOIN carefully to avoid performance issues.

If you want to combine tables with a condition, consider INNER JOIN instead.

Summary

CROSS JOIN creates all possible pairs between two tables.

The number of rows in the result is the product of the row counts of both tables.

It is useful for generating combinations but can be large and slow with big tables.