# Joins ## **1. What is a Join?** A `join` is an SQL operation that combines records from two or more tables based on related columns. The primary purpose of a join is to assemble data from multiple tables to provide a comprehensive view of the information. Joins are performed using the `JOIN` clause, which specifies the columns on which the tables should be joined.

Credit: devtodev

## **2. Types of Joins** There are several types of joins in SQL, each serving a specific purpose. The most common types are: 1. **Join (Inner Join)** 2. **Left Join (Left Outer Join)** 3. **Right Join (Right Outer Join)** 4. **Full Join (Full Outer Join)** 5. **Cross Join** 6. **Self Join** Let’s explore each type with examples. Consider two tables, `employees` and `departments` :
Table Definitions **Employees Table:** ```sql CREATE TABLE employees ( employee_id INT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), department_id INT, manager_id INT ); ``` **Departments Table:** ```sql CREATE TABLE departments ( department_id INT PRIMARY KEY, department_name VARCHAR(50) ); ``` **Inserting Data into Employees and Departments Tables:** ```sql INSERT INTO employees (employee_id, first_name, last_name, department_id) VALUES (1, 'John', 'Doe', 1, NULL), (2, 'Jane', 'Smith', 2, 1), (3, 'Sam', 'Brown', NULL, 1), (4, 'Mike', 'Davis', 1, 1), (5, 'Sara', 'Wilson', 3, 2); INSERT INTO departments (department_id, department_name) VALUES (1, 'Human Resources'), (2, 'Finance'), (3, 'Engineering'), (4, 'Marketing'); ```
### 2.1. Inner Join An `inner join` returns only the rows that have matching values in both tables. It is the most commonly used type of join. ```sql SELECT e.employee_id, e.first_name, e.last_name, d.department_name FROM employees e INNER JOIN departments d ON e.department_id = d.department_id; ``` Result: ```markdown employee_id | first_name | last_name | department_name ------------------------------------------------------ 1 | John | Doe | Human Resources 2 | Jane | Smith | Finance 4 | Mike | Davis | Human Resources 5 | Sara | Wilson | Engineering ``` NOTE: No return for employee\_id = 3 ### **2.2. 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 no match is found, NULL values are returned for columns from the right table. ```sql SELECT e.employee_id, e.first_name, e.last_name, d.department_name FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id; ``` Result: ```markdown employee_id | first_name | last_name | department_name ------------------------------------------------------ 1 | John | Doe | Human Resources 2 | Jane | Smith | Finance 3 | Sam | Brown | NULL 4 | Mike | Davis | Human Resources 5 | Sara | Wilson | Engineering ``` ### 2.3. 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 no match is found, NULL values are returned for columns from the left table. ```sql SELECT e.employee_id, e.first_name, e.last_name, d.department_name FROM employees e RIGHT JOIN departments d ON e.department_id = d.department_id; ``` Result: ```markdown employee_id | first_name | last_name | department_name ------------------------------------------------------ 1 | John | Doe | Human Resources 2 | Jane | Smith | Finance 4 | Mike | Davis | Human Resources 5 | Sara | Wilson | Engineering NULL | NULL | NULL | Marketing ``` ### 2.4. Full Join (or Full Outer Join) A `full join` returns all rows when there is a match in either table. If there is no match, NULL values are returned for columns from the table without a match. ```sql SELECT e.employee_id, e.first_name, e.last_name, d.department_name FROM employees e FULL JOIN departments d ON e.department_id = d.department_id; ``` Result: ```markdown employee_id | first_name | last_name | department_name ------------------------------------------------------ 1 | John | Doe | Human Resources 2 | Jane | Smith | Finance 3 | Sam | Brown | NULL 4 | Mike | Davis | Human Resources 5 | Sara | Wilson | Engineering NULL | NULL | NULL | Marketing ``` ### 2.5. Cross Join A `cross join` returns the Cartesian product of the two tables, i.e., all possible combinations of rows from the two tables. In our example, `employees` table has 5 records and `departments` table has 4 records. Therefore, cross join should result in 5x4= 20 records/combinations. ```sql SELECT e.employee_id, e.first_name, e.last_name, d.department_name FROM employees e CROSS JOIN departments d; ``` Result: ```markdown employee_id | first_name | last_name | department_name ------------------------------------------------------ 1 | John | Doe | Human Resources 1 | John | Doe | Finance 1 | John | Doe | Engineering 1 | John | Doe | Marketing 2 | Jane | Smith | Human Resources 2 | Jane | Smith | Finance 2 | Jane | Smith | Engineering 2 | Jane | Smith | Marketing 3 | Sam | Brown | Human Resources 3 | Sam | Brown | Finance 3 | Sam | Brown | Engineering 3 | Sam | Brown | Marketing 4 | Mike | Davis | Human Resources 4 | Mike | Davis | Finance 4 | Mike | Davis | Engineering 4 | Mike | Davis | Marketing 5 | Sara | Wilson | Human Resources 5 | Sara | Wilson | Finance 5 | Sara | Wilson | Engineering 5 | Sara | Wilson | Marketing ``` ### 2.6. Self Join A `self join` is a regular join where a table is joined with itself. This is useful for scenarios where you need to compare rows within the same table. For example, let's say we want to find employees and their managers from the `employees` table. ```sql SELECT e1.employee_id AS employee_id, e1.first_name AS employee_first_name, e1.last_name AS employee_last_name, e2.first_name AS manager_first_name, e2.last_name AS manager_last_name FROM employees e1 LEFT JOIN employees e2 ON e1.manager_id = e2.employee_id; ``` Result: ```markdown employee_id | employee_first_name | employee_last_name | manager_first_name | manager_last_name --------------------------------------------------------------------------------------------- 1 | John | Doe | NULL | NULL 2 | Jane | Smith | John | Doe 3 | Sam | Brown | John | Doe 4 | Mike | Davis | John | Doe 5 | Sara | Wilson | Jane | Smith ``` Below is one of the most popular diagram showing all joins, excluding cross and self joins:

Credit: CL Moffatt

## 3. Conclusion Joins are a powerful feature of SQL that allow you to combine data from multiple tables in various ways to gain deeper insights and produce meaningful reports. Understanding the different types of joins and how to use them effectively is crucial for anyone working with relational databases. By mastering joins, you can write more efficient queries and build more complex and informative datasets.