How to Write INNER JOIN Which is Actually CROSS JOIN? – Interview Question of the Week #250

Question: Can an INNER JOIN produce a Cartesian product? Yes. An always-true join condition such as ON 1 = 1 pairs every row from the first input with every row from the second.

During a health check I found client queries written as INNER JOINs whose conditions didn’t relate the two tables. The keyword looked reassuring; the row multiplication was the clue. Here is the original idea with tiny table variables so you can run it without creating permanent tables.

DECLARE @Table1 table (ID int NOT NULL);
DECLARE @Table2 table (Code char(1) NOT NULL);
INSERT @Table1 VALUES (1), (2);
INSERT @Table2 VALUES ('A'), ('B'), ('C');

SELECT t1.ID, t2.Code
FROM @Table1 AS t1 INNER JOIN @Table2 AS t2 ON 1 = 1
ORDER BY t1.ID, t2.Code;

SELECT t1.ID, t2.Code
FROM @Table1 AS t1 CROSS JOIN @Table2 AS t2
ORDER BY t1.ID, t2.Code;

-- The original comma syntax, also a Cartesian product:
SELECT t1.*
FROM @Table1 AS t1, @Table2 AS t2
ORDER BY t1.ID;

The first two queries both return six pairs: (1,A), (1,B), (1,C), (2,A), (2,B), (2,C). Two rows times three rows equals six. CROSS JOIN states that intention plainly; INNER JOIN ON 1 = 1 expresses the same pairing through a true predicate.

The last query preserves the original comma syntax and its t1.* projection. It still generates six joined rows, but hides the second input’s columns. Each ID appears three times. Selecting only one table’s columns does not undo the Cartesian product.

A WHERE clause can filter any of these joined results, so “always a Cartesian product” applies to the unfiltered examples here. If the requirement is to match related keys, write that relationship explicitly in ON instead of relying on a predicate that is true for every pair.

Comma-separated table syntax itself isn’t proof of non-standard SQL; the original description was too broad. I prefer explicit JOIN syntax because a missing relationship is easier to see and review.

• Introduction to joins
• INNER JOIN and WHERE
• INNER JOIN versus JOIN

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