Cross joins are one of the more straightforward join types in SQL, but they’re also one of the most misunderstood and potentially dangerous if used carelessly. Understanding when and how to use them properly can help you solve certain data problems efficiently while avoiding performance disasters.
A cross join returns the Cartesian product of two tables. This means that it combines every row from the first table with every row from the second table. If you have 10 rows in one table and 5 rows in another, you might immediately assume that it will return 15 rows. But you’d be wrong. A cross join will return 50 rows. No join condition in the ON clause. Just every possible combination of rows.
How Does a Cross Join Work?
Unlike other joins where you specify conditions for matching rows, a cross join has no ON clause. It simply pairs each row from one table with each row from another table.
Here’s the basic syntax:
SELECT *
FROM Table1
CROSS JOIN Table2;You can also achieve the same result using the (older) implicit comma syntax:
SELECT *
FROM Table1, Table2;However, CROSS JOIN is more explicit and generally recommended.
Both queries produce identical results. That is, they’d return every possible combination of rows from both tables.
When Would You Actually Use This?
At first glance, cross joins might seem useless or even reckless. Why would you want every possible combination of rows? But there are legitimate scenarios where this is exactly what you need.
One common use case is generating test data or creating combinations for analysis. If you’re building a scheduling system and need to see all possible time slots paired with all available rooms, a cross join does this instantly.
Similarly, if you’re setting up a product catalog where you need every product paired with every possible size or color variant, cross joins can help initialize that data.
Cross joins are also useful for generating sequences or date ranges when combined with number tables, creating matrices for reports, or setting up lookup tables that need every combination of two dimensions.
Example of a CROSS JOIN
Let’s say you’re running a small gym and you want to create a schedule template showing all possible combinations of trainers and time slots. You might start with the following tables:
-- Create the Trainers table
CREATE TABLE Trainers (
TrainerID INT PRIMARY KEY,
TrainerName VARCHAR(100)
);
-- Create the TimeSlots table
CREATE TABLE TimeSlots (
SlotID INT PRIMARY KEY,
SlotTime VARCHAR(20)
);
-- Insert sample data
INSERT INTO Trainers (TrainerID, TrainerName)
VALUES
(1, 'Alex Morgan'),
(2, 'Jordan Lee'),
(3, 'Sam Rivera');
INSERT INTO TimeSlots (SlotID, SlotTime)
VALUES
(1, '6:00 AM'),
(2, '9:00 AM'),
(3, '12:00 PM'),
(4, '3:00 PM'),
(5, '6:00 PM');To see all possible trainer-slot combinations for your schedule template, you could use a CROSS JOIN, like this:
SELECT
t.TrainerName,
ts.SlotTime
FROM Trainers t
CROSS JOIN TimeSlots ts
ORDER BY ts.SlotID, t.TrainerID;Result:
TrainerName SlotTime
----------- --------
Alex Morgan 6:00 AM
Jordan Lee 6:00 AM
Sam Rivera 6:00 AM
Alex Morgan 9:00 AM
Jordan Lee 9:00 AM
Sam Rivera 9:00 AM
Alex Morgan 12:00 PM
Jordan Lee 12:00 PM
Sam Rivera 12:00 PM
Alex Morgan 3:00 PM
Jordan Lee 3:00 PM
Sam Rivera 3:00 PM
Alex Morgan 6:00 PM
Jordan Lee 6:00 PM
Sam Rivera 6:00 PM
This produces 15 rows (3 trainers × 5 time slots), showing every possible pairing. From here, you could filter out unavailable combinations or add additional data like which clients are booked for each slot.
Using a Cross Join to Generate Product Variants
Here’s another scenario where cross joins can really come in handy. In this case, it’s generating product variants. Suppose you have a clothing item that comes in multiple sizes and colors:
-- Create tables for sizes and colors
CREATE TABLE Sizes (
SizeID INT PRIMARY KEY,
SizeName VARCHAR(10)
);
CREATE TABLE Colors (
ColorID INT PRIMARY KEY,
ColorName VARCHAR(20)
);
-- Insert sample data
INSERT INTO Sizes (SizeID, SizeName)
VALUES
(1, 'Small'),
(2, 'Medium'),
(3, 'Large'),
(4, 'X-Large');
INSERT INTO Colors (ColorID, ColorName)
VALUES
(1, 'Navy'),
(2, 'Charcoal'),
(3, 'Olive');To generate all possible size-color combinations for a product, you could do something like this:
SELECT
s.SizeName,
c.ColorName,
'T-Shirt - ' + s.SizeName + ' - ' + c.ColorName AS SKU
FROM Sizes s
CROSS JOIN Colors c
ORDER BY c.ColorID, s.SizeID;Result:
SizeName ColorName SKU
-------- --------- ----------------------------
Small Navy T-Shirt - Small - Navy
Medium Navy T-Shirt - Medium - Navy
Large Navy T-Shirt - Large - Navy
X-Large Navy T-Shirt - X-Large - Navy
Small Charcoal T-Shirt - Small - Charcoal
Medium Charcoal T-Shirt - Medium - Charcoal
Large Charcoal T-Shirt - Large - Charcoal
X-Large Charcoal T-Shirt - X-Large - Charcoal
Small Olive T-Shirt - Small - Olive
Medium Olive T-Shirt - Medium - Olive
Large Olive T-Shirt - Large - Olive
X-Large Olive T-Shirt - X-Large - Olive
This gives you 12 combinations (4 sizes × 3 colors), which you could then use to populate your inventory or product catalog tables.
The Performance Warning
While cross joins are nice and simple, they are also quite dangerous. Cross joins grow exponentially. A table with 1,000 rows crossed with another table of 1,000 rows produces 1,000,000 rows. Cross with a third table of 100 rows and you’re at 100,000,000 rows.
This exponential growth can bring your database to its knees if you’re not careful. Always consider the size of your tables before using a cross join, and think about whether you really need all those combinations.
If you accidentally write a query that creates a cross join (by forgetting the WHERE clause when using the old comma syntax, for example), you might not notice on small development datasets, but it could cause serious problems in production.
Filtering Cross Join Results
In practice, you’ll often use a cross join to generate all combinations and then filter them down to what you actually need. This is done with a WHERE clause after the join:
SELECT
t.TrainerName,
ts.SlotTime
FROM Trainers t
CROSS JOIN TimeSlots ts
WHERE ts.SlotTime NOT IN ('6:00 AM', '6:00 PM')
OR t.TrainerName = 'Alex Morgan'
ORDER BY ts.SlotID, t.TrainerID;Result:
TrainerName SlotTime
----------- --------
Alex Morgan 6:00 AM
Alex Morgan 9:00 AM
Jordan Lee 9:00 AM
Sam Rivera 9:00 AM
Alex Morgan 12:00 PM
Jordan Lee 12:00 PM
Sam Rivera 12:00 PM
Alex Morgan 3:00 PM
Jordan Lee 3:00 PM
Sam Rivera 3:00 PM
Alex Morgan 6:00 PM
This generates all combinations but then filters out certain slots for most trainers, while including all slots for Alex Morgan. You could also add LEFT JOINs to existing schedule data to exclude already-booked slots.
Cross-Database Compatibility
Cross joins are part of the SQL standard and work consistently across all major database systems (SQL Server, PostgreSQL, MySQL, Oracle, SQLite, etc). The CROSS JOIN syntax is universally supported.
The comma syntax also works everywhere, but it’s considered less explicit and can lead to confusion about whether you intended a cross join or simply forgot to add a WHERE clause.
Alternatives to Cross Joins
Sometimes what looks like a job for a cross join might be better solved another way. If you’re generating number sequences or date ranges, many databases have built-in functions or table-valued functions that are more efficient. SQL Server has system tables and functions for generating sequences, PostgreSQL has generate_series(), and most systems have recursive CTEs that can generate ranges without needing a cross join.
If you’re creating combinations for analysis and the result set is huge, consider whether you can filter first with WHERE clauses or use other join types with conditions to reduce the dataset before creating combinations.
Summary
Cross joins produce the Cartesian product of two tables – every row from one table paired with every row from another. They’re useful for generating combinations, creating schedule templates, setting up product variants, and initializing lookup data.
But remember that cross joins grow exponentially with table size, so they should be used deliberately and carefully. Use the explicit CROSS JOIN syntax to make your intentions clear, and be mindful of performance implications when working with larger datasets. When used appropriately, cross joins can be perfect for solving specific data problems efficiently.