Joins | All Types | Detail Description

Interview Questions Sep 12, 2023 0 227 Add to Reading List

Joins in SQL are used to combine rows from two or more tables based on a related column between them. SQL supports several types of joins, each serving a different purpose. Here, I'll explain the most common types of joins with examples:

Let's consider two sample tables for the examples:

Table: Employees

| EmployeeID | FirstName | LastName  | DepartmentID |

|------------|-----------|-----------|-------------|

| 1          | John      |  Smith    | 101         |

| 2          | Jane      |  Johnson  | 102         |

| 3          | Robert    |  Williams | 101         |

| 4          | Emily     |  Davis    | 103         |

Table: Departments

| DepartmentID | DepartmentName |

|--------------|----------------|

| 101 | HR |

| 102          | IT             |

| 103          | Sales          |

INNER JOIN:
- An INNER JOIN returns only the rows that have matching values in both tables.
Example:

SELECT Employees.FirstName, Employees.LastName, Departments.DepartmentName FROM Employees INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

This query will return the first name, last name, and department name of employees who belong to a department.
LEFT JOIN (or LEFT OUTER JOIN):
- A LEFT JOIN returns all rows from the left table and the matched rows from the right table. If there is no match, NULL values are returned for columns from the right table.
Example:

SELECT Employees.FirstName, Employees.LastName, Departments.DepartmentName FROM Employees LEFT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

This query will return all employees and their department names. If an employee has no department, the department name will be NULL.
RIGHT JOIN (or RIGHT OUTER JOIN):
- A RIGHT JOIN returns all rows from the right table and the matched rows from the left table. If there is no match, NULL values are returned for columns from the left table.
Example:

SELECT Employees.FirstName, Employees.LastName, Departments.DepartmentName FROM Employees RIGHT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

This query will return all departments and the employees who belong to them. If a department has no employees, employee names will be NULL.
FULL OUTER JOIN:
- A FULL OUTER JOIN returns all rows when there is a match in either the left or the right table. If there is no match, NULL values are returned for columns from the table that does not have a match.
Example:

SELECT Employees.FirstName, Employees.LastName, Departments.DepartmentName FROM Employees FULL OUTER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

This query will return all employees and departments. If there is no match between an employee and a department, either the employee's name or department name will be NULL.
SELF JOIN:
- A SELF JOIN is used to join a table with itself, typically when you have hierarchical or relationship data within a single table.
Example:

SELECT A.FirstName AS EmployeeName, B.FirstName AS ManagerName FROM Employees AS A LEFT JOIN Employees AS B ON A.ManagerID = B.EmployeeID;

This query retrieves the names of employees and their respective managers from the same Employees table.
CROSS JOIN (or Cartesian Join):
- A CROSS JOIN returns the Cartesian product of two tables, resulting in all possible combinations of rows from both tables.
Example:

SELECT Employees.FirstName, Departments.DepartmentName FROM Employees CROSS JOIN Departments;

This query returns all combinations of employees and departments, effectively listing each employee with every department.

These are the fundamental types of joins in SQL. The choice of which join to use depends on your specific requirements and the data you need to retrieve from multiple tables.