Alternative For Cartesian And Cross Join

I want to output all the possible combination in two tables without using cartesian join or cross join. Is this possible?

Solution 1:

I want to output all the possible combination in two tables without using cartesian join or cross join. Is this possible?

By a strict definition level, it is not possible. Why? Because the definition of a Cartesian product is exactly what you describe (the term "Cartesian join" is not often used but is a synonym for "Cartesian product"). Hence, any method that you use is implementing this functionality. Normally this functionality is implementing using CROSS JOIN (and I reluctantly admit, sometimes using ,).

You might say "add 1 to a number without doing + 1". Someone else comes along and says "use + 2 - 1". Well, that is adding one, but just using two operations rather than one.

If you want a Cartesian product but you don't want to use the CROSS JOIN operator, the most typical method uses ON 1=1:

select t1.*, t2.*
from t1 join
     t2
     on1 = 1;

Solution 2:

All possible combinations is the definition of a cartesian product.

Here are 3 alternatives to get a cartesian product from two tables. All 3 alternatives eventually boils down to a cross join (and execution plan is the same for all 3):

Create and populate sample tables:

CREATE TABLE t1 
(
    int_col int
)

CREATE TABLE t2
(
    char_col char(1)
)

INSERT INTO t1 VALUES 
(1), (2), (3), (4), (5), (6), (7), (8), (9), (10)

INSERT INTO t2 VALUES 
('a'), ('b'), ('c'), ('d'), ('e'), ('f'), ('h'), ('i'), ('j'), ('k')

The queries:

An implicit cross join:

SELECT*FROM t1, t2

An explicit cross join:

SELECT*FROM t1
CROSSJOIN t2    

Cross apply:

SELECT*FROM t1
CROSS APPLY t2

All results are the same:

int_col     char_col
1a2a3a4a5a6a7a8a9a10a1b2b3b4b5b6b7b8b9b10b1           c
2           c
3           c
4           c
5           c
6           c
7           c
8           c
9           c
10          c
1           d
2           d
3           d
4           d
5           d
6           d
7           d
8           d
9           d
10          d
1           e
2           e
3           e
4           e
5           e
6           e
7           e
8           e
9           e
10          e
1           f
2           f
3           f
4           f
5           f
6           f
7           f
8           f
9           f
10          f
1           h
2           h
3           h
4           h
5           h
6           h
7           h
8           h
9           h
10          h
1i2i3i4i5i6i7i8i9i10i1           j
2           j
3           j
4           j
5           j
6           j
7           j
8           j
9           j
10          j
1           k
2           k
3           k
4           k
5           k
6           k
7           k
8           k
9           k
10          k