Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

Thursday, 27 July 2017

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 by imagination hunt blogs
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 by imagination hunt blogs
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 by imagination hunt blogs
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 by imagination hunt blogs
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 by imagination hunt blogs
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 by imagination hunt blogs
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 by imagination hunt blogs
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 by imagination hunt blogs
Cross Join


For any query, comment us below.

Previous – Types of SQL Server Joins #1


Click imagination hunt to read latest blogs.


Keep learning and sharing...

No comments:

Post a comment

Featured post

Think that makes you rich and richer

 Napolean said: “You can think and grow rich, but if you can be brought up like most people with work and you won't starve, this wil...