Free Web Development Online Tutorials, Learn to Code
Joins In SQL, Different type of Joins Example, Learn SQL Join Query Development, SQL Tutorial

SQL Join Query Example

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


There are Four Types of Joins in SQL Database

SQL Joins Example

sql joins explained

To understand joins we have created two tables "tbStudent" and "tbAdmission"
Now we try with different type of joins based on "StudentId" to see the changes in record

sql joins example

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 inner join example

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

right join example

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

full join example

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
Comment
Name
Email
Website
Subscribe