Joins in SQL Server Example

In this tutorial you will learn SQL Join, how to write different types of joins in MS SQL

There are Four Types of Joins in MS SQL Database.

Sql inner join query syntax
Sql left join query syntax
Sql right join query syntax
Sql full join query syntax

To understand joins we have created two tables "tbStudent" and "tbAdmission", we will write different type of join in sql query to see the effect in result set.

SQL Joins Example

Now we try with different type of joins based on "StudentId" to see the changes in record.

Now we insert three records in “tbAdmission” table

INSERT INTO [tbAdmission] ([StudentId],[CourseId],[AdmissionDate])
VALUES  (1,1,getdate())
INSERT INTO [tbAdmission]  ([StudentId],[CourseId],[AdmissionDate])
VALUES  (2,3,getdate())
INSERT INTO [tbAdmission]  ([StudentId],[CourseId],[AdmissionDate])
VALUES  (5,3,getdate())

SQL Inner join example

First we write Inner join, this is actually default join in SQL

SELECT dbo.tbAdmission.AdmissionDate, dbo.tbStudent.Email, dbo.tbStudent.Lastname, dbo.tbStudent.Firstname, dbo.tbStudent.StudentId
FROM dbo.tbAdmission INNER JOIN
dbo.tbStudent ON dbo.tbAdmission.StudentId = dbo.tbStudent.StudentId

See the result; inner join will fetch only common records in both tables

SQL Left join example

Left join will fetch all records in left table and common records from right table

SELECT dbo.tbAdmission.AdmissionDate, dbo.tbStudent.Email, dbo.tbStudent.Lastname, dbo.tbStudent.Firstname, dbo.tbStudent.StudentId
FROM dbo.tbAdmission left JOIN              
dbo.tbStudent ON dbo.tbAdmission.StudentId = dbo.tbStudent.StudentId

sql left join example

SQL Right join example

Now see Right join, Will fetch all records from right table and common records from left table

SELECT dbo.tbAdmission.AdmissionDate, dbo.tbStudent.Email, dbo.tbStudent.Lastname, dbo.tbStudent.Firstname, dbo.tbStudent.StudentId
FROM dbo.tbAdmission right JOIN                
dbo.tbStudent ON dbo.tbAdmission.StudentId = dbo.tbStudent.StudentId

SQL Full join example

Full join, this will fetch matched records and unmatched records both tables

SELECT dbo.tbAdmission.AdmissionDate, dbo.tbStudent.Email, dbo.tbStudent.Lastname, dbo.tbStudent.Firstname, dbo.tbStudent.StudentId
FROM dbo.tbAdmission Full JOIN
dbo.tbStudent ON dbo.tbAdmission.StudentId = dbo.tbStudent.StudentId

Difference between Right and Left OUTER JOIN in SQL

RIGHT OUTER join	LEFT OUTER join
RIGHT OUTER join includes unmatched rows from the right side of the table	LEFT OUTER join includes unmatched rows from left table
RIGHT OUTER join = INNER JOIN + unmatched rows from the right side table	LEFT OUTER join = INNER JOIN + unmatched rows of left Table

SQL Tutorial | MS SQL Course | SQL Interview Questions

Hire SQL Developer

Joins in SQL Database

SQL Training: For any group or corporate training, please contact us at webtrainingroom(at)gmail.

Course in Demand

Learn LINQ using C#

Learn Entity Framework

Learn Ado.Net using C#

Learn MySQL Database

SQL database development tutorials for learning sql query, data manipulation language, working with MS SQL Server.

SQL Joins Example

MS SQL Examples | Join MS SQL Course