Cross Join

A CROSS JOIN produces the Cartesian product of two tables: it pairs every row of the left table with every row of the right table. Unlike INNER JOIN or LEFT JOIN, it has no ON clause — there is no matching condition, because every combination is returned by definition.

If the left table has M rows and the right table has N rows, the result has M × N rows. Two small tables can multiply into a very large result set, so cross joins are powerful but easy to misuse.

Syntax

Azure SQL (T-SQL) accepts two equivalent forms — the explicit keyword and the older comma syntax:

-- Explicit, preferred
SELECT s.Size, c.Color
FROM   Sizes  AS s
CROSS JOIN Colors AS c;

-- Legacy comma form (same result, avoid in new code)
SELECT s.Size, c.Color
FROM   Sizes AS s, Colors AS c;

Example: generating every product variant

Say a store stocks a few sizes and a few colors, and you want every possible combination as the starting catalog of variants.

CREATE TABLE Sizes  (Size  varchar(4));
CREATE TABLE Colors (Color varchar(20));

INSERT INTO Sizes  VALUES ('S'), ('M'), ('L');
INSERT INTO Colors VALUES ('Black'), ('White');

SELECT s.Size, c.Color
FROM   Sizes  AS s
CROSS JOIN Colors AS c
ORDER BY s.Size, c.Color;

3 sizes × 2 colors → 6 rows:

| Size | Color | |------|-------| | L | Black | | L | White | | M | Black | | M | White | | S | Black | | S | White |

When cross joins are genuinely useful

• Generating combinations — every size×color, every product×region, every user×feature-flag.
• Building calendars / number series — cross join digit tables (0-9) together to manufacture a dense range of dates or integers with no gaps.
• Seeding test data — multiply a handful of rows into thousands quickly.
• Filling reporting grids — cross join all Stores × Months, then LEFT JOIN the actual sales so months with zero sales still appear as 0 instead of vanishing.

-- A gap-free calendar of the first 1,000 days from a start date
SELECT DATEADD(day, ones.n + 10 * tens.n + 100 * hundreds.n, '2026-01-01') AS TheDate
FROM      (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS ones(n)
CROSS JOIN (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS tens(n)
CROSS JOIN (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS hundreds(n)
ORDER BY TheDate;

The accidental cross join

The most common way to meet a CROSS JOIN is by accident: forgetting the join predicate. These two queries return the same explosive result — the comma form just hides the mistake:

-- Meant to match customers to their orders, but the WHERE/ON was forgotten:
SELECT *
FROM Customers c, Orders o;          -- 10k customers × 50k orders = 500,000,000 rows

Related: CROSS APPLY

Azure SQL also has CROSS APPLY, a T-SQL cousin. It looks similar but is correlated: the right side is a table expression (often a function) that is evaluated per left row, so it can reference columns from the left table — something a plain CROSS JOIN cannot do.

-- For each customer, their 3 most recent orders
SELECT c.Name, o.OrderId, o.OrderDate
FROM Customers AS c
CROSS APPLY (
  SELECT TOP (3) OrderId, OrderDate
  FROM   Orders
  WHERE  Orders.CustomerId = c.CustomerId   -- references the left row
  ORDER BY OrderDate DESC
) AS o;