Stimulating Cross Join using Left Outer Join

Second question goes in the same series of 10 typical questions, here we are asking to stimulate cross join using left outer join, ie implementation of cross join using left outer join.

Lets create 2 small table

create table t1

(col1 int,

col2 varchar(5))

Command(s) completed successfully.

create table t2

(col1 int)

Command(s) completed successfully.

Let’s now insert some records in newly created table.

insert into t1 values(1,'a')

insert into t1 values(2,'b')

insert into t1 values(3,'c')

insert into t2 values(1)

insert into t2 values(2)

insert into t2 values(3)

insert into t2 values(4)

Now our platform is set to implement cross join but without using cross join clause.

Lets try out with the help of left outer join, here below query is implementing a left out join with a joining condition of always true for each record from left table, i.e each record of table t1 is match with each records table t2.

select t1.col1 as 'T.Col1', t1.Col2 as 'T.Col2', t2.Col1 as 'T2.Col1'

from t1 left outer join t2

on 1=1 

So the output of the above query will be, Number of records present in table t1 * Number of records present in table t2

This means, it is exactly sitting to the logical concept of CROSS JOIN.

Without waiting more, lets execute above query to find the output.

select t1.col1 as 'T.Col1', t1.Col2 as 'T.Col2', t2.Col1 as 'T2.Col1'

from t1 left outer join t2

on 1=1

T.Col1      T.Col2 T2.Col1

----------- ------ -----------

1           a      1

1           a      2

1           a      3

1           a      4

2           b      1

2           b      2

2           b      3

2           b      4

3           c      1

3           c      2

3           c      3

3           c      4

(12 row(s) affected)

Seems from the output, it is similar to the output of CORSS join. Lets fire the cross join and compare the result.

select t1.col1 as 'T.Col1', t1.Col2 as 'T.Col2', t2.Col1 as 'T2.Col1'

from t1 cross join t2

T.Col1      T.Col2 T2.Col1

----------- ------ -----------

1           a      1

1           a      2

1           a      3

1           a      4

2           b      1

2           b      2

2           b      3

2           b      4

3           c      1

3           c      2

3           c      3

3           c      4

(12 row(s) affected)

Yes the result is exactly same.

Now lets see the execution plan for both the query to find, whether there is any difference in the plan.

That’s great; there is no difference between the plan.

Final Note: We can implement Cross Join using any join operator (inner, left or right join) with joining condition always true i.e 1=1.

Hurry up get me a cup of tea, my SQL Server is waiting for me :)

Post Reference: Vikram Aristocratic Elfin Share