Basics of Joins

There are mainly 3 types of Joins in sql server

a.       Inner Join—This Join returns the rows when there matching records in both tables.

Use AdventureWorks2008R2

select * from person.BusinessEntity BE

Inner Join HumanResources.Employee PER on BE.BusinessEntityID=Per.BusinessEntityID

b.      Outer Join

1.       Left Outer Join--This join returns all the rows from the left table with the matching rows from the right table. If there are no columns matching in the right table, it returns NULL values.

                                                Use AdventureWorks2008R2

select * from person.BusinessEntity BE

Left Join HumanResources.Employee PER on BE.BusinessEntityID=Per.BusinessEntityID

2.       Right Outer Join--This join returns all the rows from the right table with the matching rows from the left table. If there are no columns matching in the left table, it returns NULL values.

select * from person.BusinessEntity BE

right Join HumanResources.Employee PER on BE.BusinessEntityID=Per.BusinessEntityID

3.       Full outer Join-- It returns row from either table when the conditions are met and returns null value when there is no match.

select * from person.BusinessEntity BE

full outer Join HumanResources.Employee PER on BE.BusinessEntityID=Per.BusinessEntityID

c.       Cross Join—Cross Join gives Cartesian Product . The resultset contains records that are multiplication of record number from both the tables.

select * from person.BusinessEntity BE

Cross Join HumanResources.Employee PER on BE.BusinessEntityID=Per.BusinessEntityID