Types of Joins in SQL Server

What are Joins?

Joins: Joins are used to merge two or more table on behalf of foreign key and primary key relationship to retrieve multiple data. It means foreign key column of one table points to primary key of another table. That’s the reason they are preferred over sub-query to fetch records in much faster way.

Types of SQL Server Joins

 

Types of SQL Server Joins

SQL Server Join are of 3 types: - 

o   Inner Join or Join: Inner join are those join which give only the matched rows from the table on which join is applied. 

o   Outer Join

Outer join is sub-categorized into 3 types:

o   Left Outer Join or Left Join: Left outer join are those join which give all rows from left table and only the matched row from the other table on which join is applied.

o   Right Outer Join or Right Join: Right outer join are those join which give all rows from right table and only the matched row from the other table on which join is applied.

o   Full Outer Join: Full outer join are those join which give all rows from left table and all rows from left table.

o   Cross Join: Cross join are those join which give the cartesian products of tables on which the join is applied. Cartesian product means each row from left table is matched with each row from right table.  

Under mentioned is the syntax of join in select query.

--Basic syntax of join

select columnsNameThatYouWantToShow

from NameOfTableA as aliasNameOfTableA

JoinType NameOfTableB as aliasNameOfTableB

on aliasNameOfTableA.ForeignKeyColumn = aliasNameOfTableB.PrimaryKeyColumn

Note: Except for the case of Cross join the on clause is eliminated from the query. 


We will understand joins by taking a simple example.

Ques- Let’s suppose we have two tables - Flight table and Passenger table. Now, we will see how to use all the above mentioned join on table in real scenario.

Practical Implementation:

Step-1: Creation of table.

First create passenger table which is going to contain 3 columns: PassengerId which is primary key column, PassengerName and FlightId which act as a foreign key column for our next table flight. The script is under mentioned:

--Creating table tblPassengers

create table tblPassengers

(

PassengerId int primary key,

PassengerName varchar(10),

FlightId int,

constraint FK_tblFlight_FlightId foreign key(FlightId) references tblFlight(FlightId)

)

Now, create flight table which is going to contain 3 columns: FlightId which is primary key column, FlightCode and FlightName. The script is under mentioned:

--Creating table tblFlight

create table tblFlight

(

FlightId int primary key,

FlightCode varchar(2),

FlightName varchar(15)

)

Step-2: Inserting values in tables.

Insert command to populate values in passenger table. The script is under mentioned:

--Inserting records in tblPassengers

insert into tblPassengers(PassengerId, PassengerName, FlightId) values(1,'Shalu',4)

insert into tblPassengers(PassengerId, PassengerName, FlightId) values(2,'Pranav',8)

insert into tblPassengers(PassengerId, PassengerName, FlightId) values(3,'Nitesh',12)

insert into tblPassengers(PassengerId, PassengerName, FlightId) values(5,'Prashant',1)

insert into tblPassengers(PassengerId, PassengerName, FlightId) values(6,'Sanjay',3)

insert into tblPassengers(PassengerId, PassengerName, FlightId) values(8,'Deepika',5)

insert into tblPassengers(PassengerId, PassengerName, FlightId) values(9,'Pooja',13)

insert into tblPassengers(PassengerId, PassengerName, FlightId) values(11,'Abhinav',4)

Insert command to populate values in flight table. The script is under mentioned:

--Inserting records in tblFlight

insert into tblFlight(FlightId, FlightCode, FlightName) values(4,'I5','AirAsia India')

insert into tblFlight(FlightId, FlightCode, FlightName) values(6,'AI','Air India')

insert into tblFlight(FlightId, FlightCode, FlightName) values(7,'G8','GoAir')

insert into tblFlight(FlightId, FlightCode, FlightName) values(8,'6E','GoAir')

insert into tblFlight(FlightId, FlightCode, FlightName) values(10,'9W','Jet Airways')

insert into tblFlight(FlightId, FlightCode, FlightName) values(12,'SG','SpiceJet')

insert into tblFlight(FlightId, FlightCode, FlightName) values(13,'UK','Vistara')

Step-3: Showing the values in table as per our query.

--Returning result from tblPassengers

select * from tblPassengers

 

Table Passenger

--Returning result from tblFlight

select * from tblFlight

 

Table Flight

For any query, comment us below.

Previous – Most commonly used SQL Server Data Types #6

Next – Types of SQL Server Joins #2

Click imagination hunt to read latest blogs.

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

Keep learning and sharing...