Types of SQL Server Joins

Continuting with the previous article. Let’s discuss the last and final step to understand every type of join.

Step-4: Last step, performing different types of joins.

Let’s check out every join one by one.

Inner Join/Join: Starting with “inner join” or simply “join” keyword which give only the matched rows specified in the ON condition. Our result set would look like this:

--Using join

select P.PassengerId as PassengerId,P.PassengerName as PassengerName,

F.FlightName as FlightName,F.FlightCode as FlightCode

from tblPassengers P join tblFlight F

on P.FlightId = F.FlightId

 

Join

--Using Inner Join

select P.PassengerId as PassengerId,P.PassengerName as PassengerName,

F.FlightName as FlightName,F.FlightCode as FlightCode

from tblPassengers P inner join tblFlight F

on P.FlightId = F.FlightId

 

Inner Join

Left Join/ Left Outer Join: Next, “left outer join” or simply “left join” keyword which give all rows from LEFT table and only matched rows from other table specified in the ON condition. Unmatched columns cell are replaced with NULL. Our result set would look like this:

--Using Left Outer Join

select P.PassengerId as PassengerId,P.PassengerName as PassengerName,

F.FlightName as FlightName,F.FlightCode as FlightCode

from tblPassengers P left outer join tblFlight F

on P.FlightId = F.FlightId

 

Left Outer Join

--Using Left Join

select P.PassengerId as PassengerId,P.PassengerName as PassengerName,

F.FlightName as FlightName,F.FlightCode as FlightCode

from tblPassengers P left join tblFlight F

on P.FlightId = F.FlightId

 

Left Join

Right Outer Join/ Right Join: Next, “right outer join” or simply “right join” which give all rows from RIGHT table and only matched rows from other table specified in the ON condition. Unmatched columns cell are replaced with NULL. Our result set would look like this:

--Using Right Outer Join

select P.PassengerId as PassengerId,P.PassengerName as PassengerName,

F.FlightName as FlightName,F.FlightCode as FlightCode

from tblPassengers P right outer join tblFlight F

on P.FlightId = F.FlightId

 

Right Outer Join

--Using Right Join

select P.PassengerId as PassengerId,P.PassengerName as PassengerName,

F.FlightName as FlightName,F.FlightCode as FlightCode

from tblPassengers P right join tblFlight F

on P.FlightId = F.FlightId

 

Right Join

Full Outer Join: Next, “full outer join” gives all records from LEFT and RIGHT table specified in the ON condition. Unmatched columns cell are replaced with NULL. Our result set would look like this:

--Using Full Outer Join

select P.PassengerId as PassengerId,P.PassengerName as PassengerName,

F.FlightName as FlightName,F.FlightCode as FlightCode

from tblPassengers P full outer join tblFlight F

on P.FlightId = F.FlightId

 

Full Outer Join

Cross Join: At last, “cross join” gives the cross product of two table. Here, it is not reqiuired to specify the ON condition. Our result set would look like this:

--Using Cross Join

select P.PassengerId as PassengerId,P.PassengerName as PassengerName,

F.FlightName as FlightName,F.FlightCode as FlightCode

from tblPassengers P cross join tblFlight F

 

Cross Join

For any query, comment us below.

Previous – Types of SQL Server Joins #1

Next – How to create flat files from SQL Server table

Click imagination hunt to read latest blogs.

Posted By - +Manish Kumar Gautam +LIVE VIAR +ASP.NET SOLUTIONS 

Keep learning and sharing...