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