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

Leisure time out of most hectic QA release day, playing with Count and Null

It became a leisure time out of most hectic QA release day, when my  friend ask me to design 10 typical query, don’t know why this SQL Server is so interesting it fuels up energy to even a non living atmosphere. Here is my first query goes.

What will be the output of the third query if following first two query produce the below result

select count(*) as TotalCount from dbo.MyTable

TotalCount

-----------

9

select count(*) as WhereCount from dbo.MyTable where MyID = 4

WhereCount

-----------

3

select count(*) as MyCount from dbo.MyTable where MyID <> 4

WhereCount

-----------

??

Before come to any conclusion let’s stimulate the same problem

CREATE TABLE dbo.MyTable

    (

      MyID INT

    , MyChar VARCHAR(10)

    );

   GO

Command(s) completed successfully.

Our table is ready, let’s insert some data and prepare our environment for testing.

INSERT INTO dbo.MyTable

        ( MyID, MyChar )

    VALUES

        ( 1, 'A' ),

        ( 2, 'B' ),

        ( NULL, 'C' ),

        ( 4, 'D' ),

        ( NULL, 'E' ),

        ( 6, 'F' ),

        ( 6, 'G' ),

        ( 4, 'H' ),

        ( 4, 'I' ); 

   GO

Let’s find out the total count of rows in the table

select count(*) from dbo.MyTable

TotalCount

-----------

9

So its 9 in count, lets try to find out now of rows where “MyID” field has value equal to 4

select count(*) as WhereCount from dbo.MyTable where MyID = 4

MyCount

-----------

3

So here it comes out as 3, lets now come to our asked query output

select count(*) as MyCount from dbo.MyTable where MyID <> 4

Here we saw from previous two output that

Total row count = 9

Total row with MyId value equal to 4 is = 3

So if we are asked

Total row with value not equal to 4, in a simple mathematic term we can reply,

Total row with value not equal to 4=(Total row count) – (Total row with MyId value equal to 4)

Total row with value not equal to 4 = 9-3 = 6

Now let’s run the query to find the real answer for the following query

select count(*) as MyCount from dbo.MyTable where MyID <> 4

MyCount

-----------

4

The output is not footing to our thought, lets find out where we went wrong

Lets first query the table data

MyID        MyChar

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

1           A

2           B

NULL        C

4           D

NULL        E

6           F

6           G

4           H

4           I

(9 row(s) affected)

From the output we can see total counts of rows are 9 in number and number of rows with MyID equal four are 3. Now if we check number of rows with values not equal to 4:

Considering Null is 6

Not considering Null is 4

Now if we recall our query the count came out as 4

select count(*) as MyCount from dbo.MyTable where MyID <> 4

MyCount

-----------

4

This means MyID <> 4 skip Null values while evaluating count(*) function.

Conclusion: While calculating count, SQL Server skip NULL values.

 

A cup of tea with SQL Server is the greatest combination :)

 

Post Reference: Vikram Aristocratic Elfin Share