Explain SQL Joins

A SQL JOIN fetches data from two different tables by relating one or more columns of the tables.

Table 1: Department



Table 2: Employee



The types Of SQL Joins are:

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

We shall go through all the above joins in detail with examples and descriptions. Let's start with Equijoin.

Equi Join

An equi join is a join with 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

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 returns only those 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

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

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

Example

For 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



Thanks