Joins in SQL server are
used to query (retrieve) data from 2 or more related tables. In general tables
are related to each other using foreign key constraints.
In Sql server we have following 6 types of
Joins:
1.
INNER
JOIN
2.
LEFT
OUTER JOIN
3.
RIGHT
OUTER JOIN
4.
FULL
OUTER JOIN
5.
CROSS
JOIN
6.
SELF
JOIN
Now for understanding the concept of joining
in sql server lets create 2 table name is TblStudent and tblCollage .
|
Create table tblCollage
(
ID int primary key,
CollageName nvarchar(50),
Location nvarchar(50),
CollageHead nvarchar(50)
)
Go
Create table tblStudent
(
ID int primary key,
Name nvarchar(50),
Gender nvarchar(50),
CourceID int,
CollageId int foreign key references
tblCollage(ID)
)
Go
|
Insert the data into the tables
|
Insert into tblCollage values (1, ‘VIT’,
‘Jaipur’, ‘Munesh’)
Insert into tblCollage values (2, ‘ABC’,
‘Delhi’, ‘Rahul’)
Insert into tblCollage values (3, ‘PQR’,
‘Bangalore’, ‘Anshuman’)
Insert into tblCollage values (4, ‘Other
Collage’, ‘Mumbai’, ‘Govind’)
Go
Insert into tblStudent values (1, ‘Rohan’,
‘Male’, 101, 1)
Insert into tblStudent values (2, ‘Vidya’,
‘Female’, 115, 3)
Insert into tblStudent values (3, ‘Samar’,
‘Male’, 110, 1)
Insert into tblStudent values (4, ‘Ajay’,
‘Male’, 201, 2)
Insert into tblStudent values (5, ‘Sharath’,
‘Male’, 180, 2)
Insert into tblStudent values (6, ‘Santosh’,
‘Male’, 501, 3)
Insert into tblStudent values (7, ‘Pooja’,
‘Female’, 401, 1)
Insert into tblStudent values (8, ‘Veshali’,
‘Female’, 335, 1)
Insert into tblStudent values (9, ‘Rajeev’,
‘Male’, 801, NULL)
Insert into tblStudent values (10,
‘Sarsawati’, ‘Female’, 901, NULL)
Go
|
After executing above query fetch the data
|
SELECT * FROM dbo.tblStudent
SELECT * FROM dbo.tblCollage
|
Syntax for joining in
SQl Server
|
SELECT ColumnList
FROM LeftTableName JOIN_TYPE RightTableName ON JoinCondition |
INNER JOIN in Sql
Server
Inner Join returns only the matching rows in
both the tables (i.e. returns only those rows for which the join condition
satisfies).
Now from above table write a query to retrive
the data where collageID of student table is match with the ID of Collage table
then query will be
|
SELECT
S.Name,S.Gender,C.CollageName,C.CollageHead FROM dbo.tblStudent S JOIN
dbo.tblCollage C ON C.ID = S.CollageID
|
Output
Note: JOIN or INNER
JOIN means the same. It’s always better to use INNER JOIN, as this
explicitly specifies your intention.
Left OUTER JOIN in Sql
Server
Left Outer Join/Left Join returns all the rows
from the LEFT table and the corresponding matching rows from the right table.
If right table doesn’t have the matching record then for such records right
table column will have NULL value in the result.
|
SELECT
S.Name,S.Gender,C.CollageName,C.CollageHead FROM dbo.tblStudent S LEFT OUTER
join dbo.tblCollage C ON C.ID = S.CollageID
|
Output
RIGHT OUTER JOIN in Sql
Server
|
SELECT
S.Name,S.Gender,C.CollageName,C.CollageHead FROM dbo.tblStudent S
right OUTER join dbo.tblCollage C ON C.ID
= S.CollageID
|
Right Outer Join/Right Join returns all the
rows from the RIGHT table and the corresponding matching rows from the left
table. If left table doesn’t have the matching record then for such records
left table column will have NULL value in the result.
Output
FULL OUTER JOIN in Sql
Server
It returns all the rows from both the tables,
if there is no matching row in either of the sides then it displays NULL
values in the result for that table columns in such rows.
Full Outer Join = Left Outer Join + Right
Outer Join
|
SELECT
S.Name,S.Gender,C.CollageName,C.CollageHead FROM dbo.tblStudent S
full OUTER join dbo.tblCollage C ON C.ID
= S.CollageID
|
Output
CROSS JOIN in Sql
Server
Cross join is also referred to as Cartesian
Product. For every row in the LEFT Table of the CROSS JOIN all the rows from
the RIGHT table are returned and Vice-Versa (i.e.result will have the Cartesian
product of the rows from join tables).
No.of Rows in the Result of CRoss Join = (No. of
Rows in LEFT Table) * (No. of Rows in RIGHT Table)
|
SELECT * FROM dbo.tblStudent S cross join
dbo.tblCollage
|
Here it is not the full image ,but when you
will run this query then you will see full data.
SELF JOIN in Sql Server
If a Table is joined to itself using one of
the join types explained above, then such a type of join is called SELF JOIN.
|
CREATE TABLE SelfJoinTable
(StudentId INT, Name NVARCHAR(50), CollageId
INT)
GO
INSERT INTO SelfJoinTable
VALUES(1,’Munesh’,1)
INSERT INTO SelfJoinTable
VALUES(2,’Anshuman’,1)
INSERT INTO SelfJoinTable
VALUES(3,’Rahul’,2)
INSERT INTO SelfJoinTable
VALUES(4,’Govind’,2)
GO
|
The table is like below
Now if we need to get the name of the Student
and his Collage name for each Student in the Student Table. Then we have to
JOIN
Student Table to itself as Student and his
Collage data is present in this table only as shown in the below query:
|
SELECT E.StudentId,
E.Name ‘Student Name’, M.Name ‘Collage Name’
FROM dbo.SelfJoinTable E
INNER JOIN SelfJoinTable M
ON M.StudentId = E.CollageId
|
find collage name WHERE more than 2 student
have applied
|
SELECT tblCollage.CollageName
FROM tblCollage
JOIN tblStudent ON tblStudent.CollageiD =
tblCollage.ID
GROUP BY tblCollage.CollageName
HAVING COUNT(tblCollage.CollageName) > 2
|
Output
Write a query FOR find
collage name WHERE NO one student have applied
|
SELECT tblCollage.CollageName AS
CollageName FROM (tblStudent right JOIN tblCollage ON tblStudent.CollageID =
tblCollage.ID) WHERE tblStudent.CollageID IS NULL
|
Output
For important Query Go to the link Important Sql Query
EmoticonEmoticon