Free Web Development Online Tutorials, Learn to Code
Learn SQL Database Development, MS SQL Tutorial Online

Joins in SQL Database with Example


There are Four Types of Joins in SQL Database inner join, left join, right join, and full join.

SQL Joins with 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())

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

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

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

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
 
Joins in SQL Database

Joins Example in SQL Database