How can I join two table (with a query) that, one table has no data and the other has some data!!???

Third question goes in the series of 10 typical questions; this is a very interesting question which I got from the comment section of one of the fantastic article from Pinal Dave

The question was like this “How can I join two table (with a query) that, one table has no data and the other has some data!!???  this is because sometimes my data base may have a table with or without data.”

The solution is very simple; yes it is full outer join.

Lets stimulate the problem and implement our solution. Here I am creating two table one with few records in it and another with no records.

create table Department

(did int identity(1,1) primary key,

dname varchar(15))

Command(s) completed successfully.

create table Employee

(eid int identity(1,1) primary key,

ename varchar(15),

did int references Department(did))

Command(s) completed successfully.

Here we have two table created with common field did. Now its time to insert few records in Department table and keep the Employee table empty.

insert into Department

select 'IT' union

select 'OPS' union

select 'HR' union

select 'PMO'

select * from Department

did         dname

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

1           HR

2           IT

3           OPS

4           PMO

(4 row(s) affected)

select * from Employee

eid         ename           did

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

(0 row(s) affected)

Now lets implement our solution to join two table where Department has data but there are no data in Employee Table. Our solution is to use FULL OUTER JOIN

select d.dname, e.ename from Department d

full outer join Employee e

on d.did = e.did

dname           ename

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

HR              NULL

IT              NULL

OPS             NULL

PMO             NULL

(4 row(s) affected)

The result shows that there are no related employee data for any department exist in Department table.

Programmers have burning passion towards programming; don’t dare them to their technology :)

Post Reference: Vikram Aristocratic Elfin Share

How can I join two table (with a query) that, one table has no data and the other has some data!!???

Third question goes in the series of 10 typical questions; this is a very interesting question which I got from the comment section of one of the fantastic article from Pinal Dave

The question was like this “How can I join two table (with a query) that, one table has no data and the other has some data!!???  this is because sometimes my data base may have a table with or without data.”

The solution is very simple; yes it is full outer join.

Lets stimulate the problem and implement our solution. Here I am creating two table one with few records in it and another with no records.

create table Department

(did int identity(1,1) primary key,

dname varchar(15))

Command(s) completed successfully.

create table Employee

(eid int identity(1,1) primary key,

ename varchar(15),

did int references Department(did))

Command(s) completed successfully.

Here we have two table created with common field did. Now its time to insert few records in Department table and keep the Employee table empty.

insert into Department

select 'IT' union

select 'OPS' union

select 'HR' union

select 'PMO'

select * from Department

did         dname

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

1           HR

2           IT

3           OPS

4           PMO

(4 row(s) affected)

select * from Employee

eid         ename           did

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

(0 row(s) affected)

Now lets implement our solution to join two table where Department has data but there are no data in Employee Table. Our solution is to use FULL OUTER JOIN

select d.dname, e.ename from Department d

full outer join Employee e

on d.did = e.did

dname           ename

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

HR              NULL

IT              NULL

OPS             NULL

PMO             NULL

(4 row(s) affected)

The result shows that there are no related employee data for any department exist in Department table.

Whatever you are, it is just because of programming. Love your program like anything. I love SQL Server :)

Post Reference: Vikram Aristocratic Elfin Share

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