Explain SQL Joins

Introduction

In this article, you will learn about the Join statements of SQL servers. A SQL JOIN fetches data from two different tables by relating one or more columns of the tables. Continue reading about Joins in SQL Server: Joins in SQL Server. Everything You Need To Know About SQL Joins.

Table 1: Department

Table 2: Employee

Types Of SQL Joins

• Equi join
• Inner Join or Self Join
• Outer Join
• Further, we have two parts of an Outer Join:
  • Left Outer Join
  • Right Outer Join

Find a detailed article about Types of Joins in SQL Server here: Types of Joins in SQL Server. 

Equi Join in SQL

An equi join has a join condition containing an equality operator (=). An equijoin combines rows that have equivalent values for the specified columns.

Example

The following query displays the employee name and the corresponding department.

SELECT e.EmployeeID,e.Employee_Name, d.Department_Name
FROM employee e(nolock), department d (nolock)
WHERE e.DepartmentID = d.DepartmentID

Output

Inner or Self Join in SQL

A self-join is a join of a table to itself. This table appears twice in the FROM clause and is followed by table aliases that qualify the column names in the join condition. To perform a self-join, SQL combines and returns rows of the table that satisfy the join condition.

Example

The following query displays the Employee Name and the corresponding Manager Name within the employee table.

SELECT e1.Employee_Name EmployeeName, e2.Employee_Name ManagerName
FROM employee e1(nolock), employee e2(nolock)
WHERE e1.EmployeeID = e2.ManagerID

Output

An inner join (sometimes called a "simple join") is a join of two or more tables that only returns rows that satisfy the join condition.

Example

The following query displays the employee name and the corresponding department.

SELECT e.EmployeeID,e.Employee_Name, d.Department_Name
FROM employee e(nolock) INNER JOIN department d(nolock)
ON e.DepartmentID = d.DepartmentID

Output

Left Outer Join in SQL

A left outer join displays all the rows from the first table and the matched rows from the second table.

Example

The following query retrieves the employee's name and the corresponding department he belongs to, whereas all the departments are displayed even if the employee is not assigned to any department.

SELECT e.EmployeeID,e.Employee_Name, d.Department_Name
FROM employee e(nolock) LEFT JOIN department d(nolock)
ON e.DepartmentID = d.DepartmentID

Output

Right Outer Join in SQL

The right outer join displays all the rows from the second table and matching rows from the first table.

Example

SELECT e.EmployeeID,e.Employee_Name, d.Department_Name
FROM employee e(nolock) RIGHT JOIN department d(nolock)
ON e.DepartmentID = d.DepartmentID

Output

Conclusion

This article taught us about the Join statements of SQL Server. Continue reading about Joins in SQL Server: Joins in SQL Server. Everything You Need To Know About SQL Joins.