Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

Wednesday, 26 July 2017

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 by imagination hunt
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 by imagination hunt
Table Passenger


--Returning result from tblFlight
select * from tblFlight
 
Table Flight by imagination hunt blogs
Table Flight


For any query, comment us below.



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...