Follow in Twitter & Facebook


Like in Facebook

Powered By Blogger Widgets

Free Download

FREE Tools


Tuesday, September 25, 2012

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. You can also check another SQL server articles on different types of joins in SQL Server.

SQL joins are very much useful whenever we are retrieving data from multiple tables.

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 absoulutely 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 on: "Left outer join and Right outer join in SQL Server"