Joins in SQL Server

What are Joins?

Joins enable us to retrieve columns from more than one table at a time as one result set. The relationship between the tables is not mandatory.

• There are multiple types of joins
  • Inner Join
  • Left Join
  • Right Join
  • Outer Join
  • Cross Join
  • Self Join

Observe Closely the two table

SELECT *
   FROM Student
SELECT *
   FROM Courses

Output:

Let’s retrieve data from these two tables using joins

Inner Join in SQL Server

Inner join retrieves only the rows that matched in both tables or the join condition.

SELECT Course.*,std.*
FROM Student Course
INNER JOIN Courses std ON Course.CourseID = std.CourseID

Output:

We can also use WHERE Clause in the query such as

SELECT Course.*,std.*
FROM Student Course
INNER JOIN Courses std ON Course.CourseID = std.CourseID
where std.CourseID<3

Left Outer Join in SQL Server

Left Join is used to retrieve all rows from the first table and only matched rows from the second table

SELECT Course.*,std.*
FROM Student Course
LEFT JOIN Courses std ON Course.CourseID = std.CourseID

Output:

Right Outer Join in SQL Server

Right Join is used to retrieve all rows from the second table and only matched rows from the first table. Right Join is the opposite of left join.

SELECT Course.*,std.*
FROM Student Course
RIGHT JOIN Courses std ON Course.CourseID = std.CourseID

Output:

Full Outer Join in SQL Server

Full outer Join retrieves rows all rows from both tables either matched or not matched. Full Outer Join is helpful in finding unmatched rows where the relationship is not applied between tables.

SELECT Course.*,std.*
FROM Student Course
FULL OUTER JOIN Courses std ON Course.CourseID = std.CourseID

Output:

Cross Join in SQL Server

Cross Joins retrieves the cross/cartesian product of the table rows. Where each item of the second table is displayed with each item of the first table.

SELECT Course.*,std.*
FROM Student Course
CROSS JOIN Courses std

Self Join in SQL Server

Self Join query is applied on a single table that references a record within itself. Observe the following table. It contains Employee data as well as Manager ID which is Employee ID.

Example 1: Find Employee vs Managers

SELECT e1.Name EmployeeName, e2.name AS ManagerName
FROM Employee e1
INNER JOIN Employee e2
ON e1.ManagerID = e2.EmployeeID

Output:

Example 2: Find the top manager

SELECT e1.Name EmployeeName, ISNULL(e2.name, 'Top Manager') AS ManagerName
FROM Employee e1
LEFT JOIN Employee e2
ON e1.ManagerID = e2.EmployeeID
GO

Output: