Joins in SQL Server

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


Share this

Related Posts

Previous
Next Post »