CROSS JOIN: Duplicate Inputs Multiply the Result

CROSS JOIN combines every left row with every right row, including repeated input values. I count source rows before counting distinct labels. Identical-looking combinations can represent several distinct row pairs.

Keep source identifiers in the first result

The left input contains two rows with the same Blue label. The right input contains three rows, one Small and two Large. Unique source identifiers distinguish those rows.

The cross join expects six result rows because two source rows pair with three source rows. Each left identifier appears with every right identifier. No equality predicate limits those combinations.

The output retains both source identifiers beside the labels. That shows why four Blue-Large combinations are not one physical pair. They arise from two left rows and two right rows.

I order by the source identifiers to make the expected tuple list deterministic. Ordering does not create or remove combinations. It only gives the six pairs a stable display order.

WITH LeftRows AS
(
    SELECT LeftId,Color FROM (VALUES (1,CAST('Blue' AS varchar(8))),(2,CAST('Blue' AS varchar(8)))) AS v(LeftId,Color)
), RightRows AS
(
    SELECT RightId,SizeText FROM (VALUES (1,CAST('Small' AS varchar(8))),(2,CAST('Large' AS varchar(8))),(3,CAST('Large' AS varchar(8)))) AS v(RightId,SizeText)
)
SELECT l.LeftId,l.Color,r.RightId,r.SizeText
FROM LeftRows AS l CROSS JOIN RightRows AS r
ORDER BY l.LeftId,r.RightId;

Grouping labels measures multiplicity rather than removing its cause

The second query groups the result by the visible label pair. It expects a count of four for Blue-Large and two for Blue-Small. Those counts preserve the original row-pair multiplicity.

There are only two distinct label combinations, but there are six joined rows. Both observations can be correct. They describe different grains of the result.

Selecting DISTINCT labels would hide that multiplicity from the display. It would not prove the original inputs were unique. Decide whether repeated input rows are legitimate before changing the projection.

I use grouped counts here to expose the difference rather than silently deduplicate. A reporting requirement may legitimately need every source pair. Another requirement may need unique attribute combinations.

WITH LeftRows AS
(
    SELECT Color FROM (VALUES (CAST('Blue' AS varchar(8))),(CAST('Blue' AS varchar(8)))) AS v(Color)
), RightRows AS
(
    SELECT SizeText FROM (VALUES (CAST('Small' AS varchar(8))),(CAST('Large' AS varchar(8))),(CAST('Large' AS varchar(8)))) AS v(SizeText)
)
SELECT l.Color,r.SizeText,COUNT(*) AS PairRows
FROM LeftRows AS l CROSS JOIN RightRows AS r
GROUP BY l.Color,r.SizeText ORDER BY r.SizeText;

Use a cross join when all combinations are intended

A small parameter grid is one useful application. Every chosen color can pair with every chosen size. The row-count product is part of that design.

An accidental cross join in a report can instead inflate measures. Repeated labels on either side increase the product. A familiar-looking output does not establish that its grain is correct.

Before adapting the example, state what one source row represents on each side. Then state what one result row should represent. Those definitions help determine whether an unrestricted pairing belongs in the query.

If a relationship should restrict the pairs, use the appropriate join predicate. A cross join does not discover that relationship from matching column names. The absence of a predicate is deliberate syntax.

Count the inputs and inspect the complete pair set

The source counts here are small enough to enumerate every pair. That makes the six-row expectation independently reviewable. A larger product can hide repeated relationships inside a long grid.

The example uses duplicate text labels with distinct source identifiers. This avoids claiming that a duplicated label is necessarily a duplicated entity. The identifiers and label values answer separate questions.

Filtering the source inputs changes the product. Filtering the joined result can also remove pairs. Include those conditions in the stated row-pair requirement rather than rely on an informal count shortcut.

Read the complete pair list beside the grouped multiplicities. Six source combinations can reduce to two visible label groups. Choose the required result grain before discarding source identifiers.

When reviewing a real all-combinations query, keep identifiers visible until the grain is confirmed. Compare the input row counts with the expected pair count. Then choose whether the final display needs pairs, grouped counts or distinct labels.

Keep the identifiers visible until the grain is confirmed, and the counts will make sense.

A distinct label pair is not one joined row, it is several source pairs counted together.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.