Types of Joins in SQL Server

Types of joins:
A. INNER JOIN
B. LEFT OUTER JOIN
C. RIGHT OUTER JOIN
D. FULL JOIN
E. CROSS JOIN

To explain the types of join we can have the following tables as examples:Types of

A. INNER JOIN: Return rows when there is at least one match in both tables

Example:
SELECT
  S.StudentName,
C.CourseName
FROM
StudentDetail S
JOIN CourseDetail c
ON
S.CourseID = C.CourseID

Output table:

B. LEFT OUTER JOIN: Return all rows from the left table, even if there are no matches in the right table
Example:
SELECT
S.StudentName,
C.CourseName
FROM
StudentDetail S
 left JOIN
CourseDetail c
ON
S.CourseID = C.CourseID

Output table:

C. RIGHT OUTER JOIN: Return all rows from the right table, even if there are no matches in the left table
Example:
SELECT
 S.StudentName,
C.CourseName
FROM
StudentDetail S
 RIGHT JOIN
CourseDetail c
ON
S.CourseID = C.CourseID

Output table:

D. FULL JOIN: Return rows when there is a match in one of the tables
Example:
SELECT
S.StudentName,C.CourseName
FROM
StudentDetail S
Full Outer Join
CourseDetail c
ON
S.CourseID = C.CourseID

Output table:

E. CROSS JOIN: Return the Cartesian product of the rows of two tables
Example:
SELECT
  S.StudentName,
C.CourseName
FROM
StudentDetail S
Cross Join
CourseDetail c

Output table:

Posted in: SQL Server Tutorial