Online Training for Asp.net MVC Web Designing Development, MS SQL, Digital Marketing

Joins in SQL Database with Example, SQL Tutorial


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
Group Training