Outer Joins -- Sql Server

There are three different Outer Join methods. 

•  Left Outer Join 
•  Right Outer Join 
•  Full Outer Join 

Left Outer Join:

This join returns all the rows from the left table in conjunction with the matching rows from the right table. If there are no columns matching in the right table, it returns NULL values.

Example for Left Outer Join 

We consider above tables  for this Outer joins

Left Outer Join Quey

select * from dbo.Students S LEFT OUTER JOIN dbo.Advisors A ON S.Advisor_ID=A.Advisor_ID

Out Put

RIGHT OUTER JOIN:

This join returns all the rows from the right table in conjunction with the matching rows from the left table. If there are no columns matching in the left table, it returns NULL values.

Query for Right Outer Join:

select * from dbo.Students S Right OUTER JOIN dbo.Advisors A ON S.Advisor_ID=A.Advisor_ID

FULL OUTER JOIN:

This join combines left outer join and right outer join. It returns row from either table when the conditions are met and returns null value when there is no match.

Query for Full Outer Join

select * from dbo.Students S FULL OUTER JOIN dbo.Advisors A ON S.Advisor_ID=A.Advisor_ID