Powered by Blogger.

Saturday, February 15, 2014

Left outer join and Right outer join in SQL Server



In this article we will discuss about Left outer join and Right outer join in SQL Server.
SQL joins are very much useful whenever we are retrieving data from multiple tables.

You can also check difference between primary key and unique key in sql server. Also Difference between clustered index and non clustered index in sql server 2008.

Left outer join:
First remember Left outer join and left join both are same, there is no difference between left outer join and left join. Left outer join gives all records from the left table as well as match records in two tables and corresponding null value for the right table.

Right outer join:
Similarly right join and right outer join are absolutely same, there is no difference between a right join and right outer join. Right outer join returns all the matching records from both tables, as well as all records from the right table and corresponding null values for the left table.

Example:
Suppose we have 2 tables name as Employees and Salary as shown in the figure below:
Employees table has 3 columns: ID, Name and Age


Salary table has 2 columns ID and Salary


Now lets try to query left outer join like below:

SELECT e.ID,e.Age,e.Name,e.ID,s.ID FROM employees e
left join Salary s on e.ID=s.ID

The output will come as shown in the figure below:




Since its a left outer join, so its returning all records from the left table (Employees) and corresponding NULL values for Unmatched record.

Now lets try to query right outer join like below:

SELECT e.ID,e.Age,e.Name,e.ID,s.Salary FROM employees e
right join Salary s on e.ID=s.ID

Now check the out put in the figure below:


Since its a right outer join so it is showing all records from the right table (salary) and corresponding NULL values for unmatched record.





0 comments

Post a Comment