SQL JOIN | SQL JOINS | SQL JOINS with Examples | SQL JOIN TABLES | SQL JOINS TUTORIAL

SQL JOINS

SQL Joins basically used for mapping the data from more than 1  tables.

For correct out output we need to map correct respective columns.

Following example will demonstrate how to use sql joins

TYPES of SQL JOINS:

followings are joins type in sql

1.Inner Join

2.Left Join 

3.RIght Join

4.Full outer Join

Table setup for Join example.

Create Table CityMaster_k4Coding

(

id bigint identity(1,1),

CityName varchar(100)

)

Create Table StudentMaster_k4Coding

(

 STudentName Varchar(100),

 Standard varchar(100),

 Admissiondate datetime,

Cityid bigint

)

Insert Into cityMaster_k4coding values ('MUMBAI') -- auto id 1

Insert Into cityMaster_k4coding  values ('DELHI') -- auto id 2

Insert Into cityMaster_k4coding  values ('PUNE') -- auto id 3

Insert Into cityMaster_k4coding  values ('CHENNAI') -- auto id 4

Insert Into  StudentMaster_k4Coding  values ('ASHISH','X',getdate(),1)

Insert Into  StudentMaster_k4Coding  values ('Jay','X',getdate(),2)

Insert Into  StudentMaster_k4Coding  values ('VIJ','X',getdate(),3)

Insert Into  StudentMaster_k4Coding  values ('PAREKH','X',getdate(),4)

Insert Into  StudentMaster_k4Coding  values ('David','X',getdate(),1)

Insert Into  StudentMaster_k4Coding  values ('Rohan','X',getdate(),2)

Insert Into  StudentMaster_k4Coding  values ('Swap','X',getdate(),2)

Insert Into  StudentMaster_k4Coding  values ('Mutthu','X',getdate(),5)

City Master :Sample data

Query :Select * from cityMaster_k4coding

Student Master:  Sample data

Query : Select * from StudentMaster_k4Coding

Inner Join :

Inner Join is used when matched records need to be extracted.

As per sample data in City we have respective name and  ids as 1.2.3 & 4 where as in Student table we have address ids 1,2,3,4,5.

We we apply inner join query based on city id unique field in this case only matched records will display.

Query Syntax:

Select * from tablename1 S
Inner Join tablename1  C on
S.uniquefield=c.uniquefield 

Note: Here S and C are the table alias and uniquefield is common fields in both table for join.

Select * from StudentMaster_k4Coding S
Inner Join cityMaster_k4coding C on
S.Cityid=c.id

System showing the out put where matching records are available .

In City master table there is no ID=5 so query is not displaying that additional record.

Left Join :

Left Join is used when all matched records need to be extracted from left side of the table data.

Similar to Inner Join query 

Select * from StudentMaster_k4Coding S
Left Join cityMaster_k4coding C on
S.Cityid=c.id

here we have StudentMaster_k4Coding  S as left side table, so all the output will display from student table.

As per above screen no data present in City table and we have left join from student table because of that its showing cityid and name as NULL.

RIGHT Join :

Left Join is used when all matched records need to be extracted from left side of the table data.

Similar to LEFT Join query we are doing join with RIGHT keyword

Select * from cityMaster_k4coding C 
RIGHT Join StudentMaster_k4Coding S on

S.Cityid=c.id

here we have StudentMaster_k4Coding  S as left side table, so all the output will display from student table.

FULL Outer Join :

FULL outer Join is used when all matched records need to be extracted from both side of the table data.

Similar to RIGHT Join query we are doing join with FULL OUTER JOIN keyword

Select * from cityMaster_k4coding C 
FULL OUTER Join StudentMaster_k4Coding S on

S.Cityid=c.id

here we have StudentMaster_k4Coding  S as left side table, so all the output will display from student table and City table both.