Questions tagged [cross-join]

A CROSS JOIN is part of the ISO/ANSI SQL Standard and occurs when 2 tables are JOIN-ed with no ON criteria specified. This is the equivalent of combining every SELECT-ed field of table 1 with every SELECT-ed field of table 2.

Example:

CREATE TABLE t1 (x INT NOT NULL);
INSERT INTO t1 VALUES (1), (2), (3);

and

CREATE TABLE t2 (y TEXT NOT NULL);
INSERT INTO t2 VALUES ('a'), ('b'), ('c');

Now, we perform the CROSS JOIN. There are two ways of doing this - the "preferred" one is:

SELECT t1.x, t2.y
FROM t1
CROSS JOIN t2;
Result:
x   y
1   a
1   b
1   c
2   a
2   b
2   c
3   a
3   b
3   c
9 rows

An alternative way of writing this is:

SELECT t1.x, t2.y
FROM t1, t2;

Result:

idem

Some RDBMS's support a third syntax - ON TRUE.

SELECT t1.x, t2.y
FROM t1
JOIN t2
ON TRUE;

Result:

idem

A fiddle of the above is available here.