Understanding SQL Join Queries with Examples

Structured Query Language (SQL) is a powerful tool for managing and manipulating relational databases. One of the key features of SQL is the ability to combine data from multiple tables using JOIN operations. In this article, we will explore four types of JOIN operations: INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN, with examples for each.

Let’s define the initial versions of the employees and departments tables with some sample data:

-- Creating the 'employees' table
CREATE TABLE employees (
    employee_id INT,
    employee_name VARCHAR(50),
    department_id INT
);

-- Inserting sample data into the 'employees' table
INSERT INTO employees (employee_id, employee_name, department_id)
VALUES
    (1, 'John Doe', 1),  -- HR
    (2, 'Jane Smith', 2),  -- IT
    (3, 'Bob Johnson', 3);  -- Finance

-- Creating the 'departments' table
CREATE TABLE departments (
    department_id INT,
    department_name VARCHAR(50)
);

-- Inserting sample data into the 'departments' table
INSERT INTO departments (department_id, department_name)
VALUES
    (1, 'HR'),
    (2, 'IT'),
    (3, 'Finance'),
    (4, 'Marketing');

employees Table

+-------------+---------------+---------------+
| employee_id | employee_name | department_id |
+-------------+---------------+---------------+
| 1           | John Doe      | 1             |
| 2           | Jane Smith    | 2             |
| 3           | Bob Johnson   | 3             |
+-------------+---------------+---------------+

departments Table

+---------------+------------------+
| department_id | department_name |
+---------------+------------------+
| 1             | HR               |
| 2             | IT               |
| 3             | Finance          |
| 4             | Marketing        |
+---------------+------------------+

INNER JOIN

The INNER JOIN keyword selects records that have matching values in both tables. This means only the rows with common values in the specified columns of both tables will be included in the result set.

Let’s consider two tables, employees and departments, and we want to retrieve information about employees along with their respective departments.

SELECT employees.employee_id, employees.employee_name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;

Output:

| employee_id | employee_name | department_name |
|-------------|---------------|------------------|
| 1           | John Doe       | HR               |
| 2           | Jane Smith     | IT               |
| 3           | Bob Johnson    | Finance          |

This query returns only the records where there is a match between the department_id column in the employees table and the department_id column in the departments table.

LEFT JOIN

The LEFT JOIN keyword returns all records from the left table (the table mentioned before the JOIN keyword), and the matched records from the right table. If there is no match, NULL values will be returned for columns from the right table.

Consider the same employees and departments tables, but this time we want to retrieve all employees, regardless of whether they are associated with a department.

SELECT employees.employee_id, employees.employee_name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id;

Output:

| employee_id | employee_name | department_name |
|-------------|---------------|------------------|
| 1           | John Doe       | HR               |
| 2           | Jane Smith     | IT               |
| 3           | Bob Johnson    | Finance          |
| 4           | Sarah Brown    | NULL             |

This query returns all employees from the employees table, and if there is a match with the departments table, the corresponding department name is included. If there is no match, the department name is NULL.

RIGHT JOIN

The RIGHT JOIN keyword returns all records from the right table, and the matched records from the left table. If there is no match, NULL values will be returned for columns from the left table.

Continuing with the employees and departments tables, let's retrieve all departments along with the employees associated with them.

SELECT employees.employee_id, employees.employee_name, departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.department_id;

Output:

| employee_id | employee_name | department_name |
|-------------|---------------|------------------|
| 1           | John Doe       | HR               |
| 2           | Jane Smith     | IT               |
| 3           | Bob Johnson    | Finance          |
| NULL        | NULL          | Marketing        |

This query returns all departments from the departments table, and if there is a match with the employees table, the corresponding employee information is included. If there is no match, the employee columns are NULL.

FULL JOIN

The FULL JOIN keyword returns all records when there is a match in either the left or right table. If there is no match, NULL values will be returned for columns from the table without a match.

Let’s use the same employees and departments tables to retrieve information about both employees and departments, regardless of whether there is a match.

SELECT employees.employee_id, employees.employee_name, departments.department_name
FROM employees
FULL JOIN departments ON employees.department_id = departments.department_id;

Output:

| employee_id | employee_name | department_name |
|-------------|---------------|------------------|
| 1           | John Doe       | HR               |
| 2           | Jane Smith     | IT               |
| 3           | Bob Johnson    | Finance          |
| 4           | Sarah Brown    | NULL             |
| NULL        | NULL          | Marketing        |

This query returns all records from both tables, combining information from both the employees and departments tables. If there is a match, the corresponding values are included; otherwise, NULL values are used for columns without a match.

In conclusion, understanding the different types of JOIN operations in SQL allows you to retrieve and analyze data from multiple tables based on specific criteria, providing flexibility and efficiency in database queries.