Friday, November 19, 2010

SQL Sevre Interview Question's : JOINS In SQL Server

What are Joins in SQL Server   Joins is the functionality introduced in SQL server 2005. Joins are used to map two relative tables with relational key between them.
What are the different types of joins in SQL Server?
1.              Inner join
2.              Left join
3.              Left outer join
4.              Right outer join
5.              Self-Join
For explaining all these joins I am taking the two sample table. First table is the employee and second table is employer.
Employee tables contain the data like employee name, employee Id, employee salary.
Employer table contains the data for employer with employer name and other details
 Employee
ID
Name
Employer ID
Salary
1
Randheer
1
50000
2
Amit
2
70000
3
Anshu
1
65000
4
Sarub
3
70000
5
rahul
4
50000

Employer
EmplId
Empl Name
1
Accent
2
XYX INC
3
ABC
4
PQR
5
RST



Implementing Inner Join
Inner join means the common data between the two tables. I.e. you will get only those employer name for which employee exist in the table.
Select * from employee inner join employer on employer id=EmplId
For getting the result paste the SQL in SSMS Query Analyzer (it’s the window where you are executing the query)

Declare @Employee table(Id int identity(1,1),Name varchar(100),EmployerID int,Salary money)
insert into @Employee values ('randheer',1,50000)
insert into @Employee values ('Amit',2,70000)
insert into @Employee values ('Anshu',1,70000)
insert into @Employee values ('Sarub',3,70000)
insert into @Employee values ('rahul',4,50000)
insert into @Employee values ('gaur',6,50000)

Declare @Employer table(EmplId int identity(1,1),Name varchar(100))
insert into @Employer values ('Accent')
insert into @Employer values ('XYZ')
insert into @Employer values ('ABC')
insert into @Employer values ('POR')
insert into @Employer values ('RST')
insert into @Employer values ('OPR')



select * from @employee EM INNER JOIN @Employer EP on EP.EmplId=EM.EmployerID

Result of this query will be the common data between the two tables on the employer id.

1          randheer        1          50000.00         1          Accent
2          Amit   2          70000.00         2          XYZ
3          Anshu            1          70000.00         1          Accent
4          Sarub  3          70000.00         3          ABC
5          rahul   4          50000.00         4          POR
6          gaur    6          50000.00         6          OPR


Implementing Left Join

In Left join left tables complete data comes and from other table only the ommon data comes. Remaining columns from the other table will come null.

Run this query with the same above table.

select * from @employee EM LEFT JOIN @Employer EP on EP.EmplId=EM.EmployerID

1              randheer             1              50000.00          1              Accent
2             Amit      2             70000.00          2             XYZ
3             Anshu   1              70000.00          1              Accent
4             Sarub    3             70000.00          3             ABC
5              rahul     4             50000.00          4             POR
6             gaur       8             50000.00          NULL   NULL

Null value are showing this and also all the rows which were in employer table are not in result.

Implementing the Right Join

In right join the right table all the value appears in the result and left table only common values.

Run the below query with same above mentioned table.

select * from @employee EM RIGHT JOIN @Employer EP on EP.EmplId=EM.EmployerID

Id        Name EmployerID  Salary EmplId           Name
1          randheer        1          50000.00         1          Accent
3          Anshu            1          70000.00         1          Accent
2          Amit   2          70000.00         2          XYZ
4          Sarub  3          70000.00         3          ABC
5          rahul   4          50000.00         4          POR
NULL NULL NULL NULL 5          RST
NULL NULL NULL NULL 6          OPR


RIGHT OUTER JOIN and LEFT OUTER JOIN are same as right join and left join just having different name.
These queries will give the Sme result as earlier there respective query given.

select * from @employee EM RIGHT OUTER JOIN @Employer EP on EP.EmplId=EM.EmployerID
select * from @employee EM RIGHT LEFT JOIN @Employer EP on EP.EmplId=EM.EmployerID

No comments:

Post a Comment