Retrieving Records From Multiple Tables With Joins in MySQL

28 min read·Jan 1, 2025

A relational database consists of multiple related tables linked together by common attributes called relationships.

These relationships can be used to combine the data from multiple tables simultaneously in a single query through a mechanism called a join.

Setting up the database

Before diving into this lesson, you will have to perform some modifications on your database.

  1. Add a new column named manager_id to the employees table:

    mysql> ALTER TABLE employees ADD COLUMN manager_id INT NULL;

  2. Create a relationship between the id column and the manager_id column:

    mysql> ALTER TABLE employees ADD CONSTRAINT fk_manager FOREIGN KEY (manager_id) REFERENCES employees(id);

  3. Update the records of the employees table:

    mysql> UPDATE employees SET manager_id = 9 WHERE id = 2;

    mysql> UPDATE employees SET manager_id = 1 WHERE id = 4;

    mysql> UPDATE employees SET manager_id = 9 WHERE id = 5;

    mysql> UPDATE employees SET manager_id = 6 WHERE id = 8;

    mysql> UPDATE employees SET manager_id = 1 WHERE id = 10;

  4. Create a new table named contracts:

    mysql> CREATE TABLE contracts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  contract_name VARCHAR(255) NOT NULL,
  employee_id INT NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE,
  status ENUM('Active', 'Completed') NOT NULL DEFAULT 'Active',
  budget INT,
  FOREIGN KEY (employee_id) REFERENCES employees(id)
);

  5. Insert rows into the contracts table:

    mysql> INSERT INTO contracts (id, contract_name, employee_id, start_date, end_date, status, budget) VALUES
(1, 'Sales Expansion Project', 1, '2023-04-15', '2023-10-15', 'Completed', 18000),
(2, 'AI Development Initiative', 9, '2024-01-01', NULL, 'Active', 50000),
(3, 'Employee Onboarding Program', 3, '2023-06-01', '2023-09-01', 'Completed', 12000),
(4, 'Rebranding Strategy', 8, '2023-09-15', '2024-03-15', 'Active', 25000),
(5, 'Data Migration to Cloud', 5, '2023-11-01', NULL, 'Active', 40000);

  6. Verify that the rows of the employees and contracts table look like this:

    mysql> SELECT * FROM employees;
+----+------------+-----------+-------------+------------+--------+------------+
| id | first_name | last_name | department  | hire_date  | salary | manager_id |
+----+------------+-----------+-------------+------------+--------+------------+
|  1 | John       | Doe       | Sales       | 2022-01-15 |  50000 |       NULL |
|  2 | Jane       | Smith     | Engineering | 2020-03-22 |  70000 |          9 |
|  3 | Alice      | Johnson   | HR          | 2019-07-30 |  45000 |       NULL |
|  4 | Bob        | Brown     | Sales       | 2018-11-10 |  52000 |          1 |
|  5 | Charlie    | Davis     | Engineering | 2021-06-18 |  75000 |          9 |
|  6 | Diana      | Miller    | Marketing   | 2017-05-14 |  60000 |       NULL |
|  7 | Eve        | Wilson    | HR          | 2023-02-25 |  48000 |       NULL |
|  8 | Frank      | Moore     | Marketing   | 2020-09-30 |  62000 |          6 |
|  9 | Grace      | Taylor    | Engineering | 2021-04-02 |  77000 |       NULL |
| 10 | Hank       | Anderson  | Sales       | 2019-12-12 |  55000 |          1 |
+----+------------+-----------+-------------+------------+--------+------------+
10 rows in set (0.00 sec)

mysql> SELECT * FROM contracts;
+----+-----------------------------+-------------+------------+------------+-----------+--------+
| id | contract_name               | employee_id | start_date | end_date   | status    | budget |
+----+-----------------------------+-------------+------------+------------+-----------+--------+
|  1 | Sales Expansion Project     |           1 | 2023-04-15 | 2023-10-15 | Completed |  18000 |
|  2 | AI Development Initiative   |           9 | 2024-01-01 | NULL       | Active    |  50000 |
|  3 | Employee Onboarding Program |           3 | 2023-06-01 | 2023-09-01 | Completed |  12000 |
|  4 | Rebranding Strategy         |           8 | 2023-09-15 | 2024-03-15 | Active    |  25000 |
|  5 | Data Migration to Cloud     |           5 | 2023-11-01 | NULL       | Active    |  40000 |
+----+-----------------------------+-------------+------------+------------+-----------+--------+
5 rows in set (0.00 sec)

Inner join

An inner join returns all the records containing identical values in the columns being compared.

If one of the specified columns contains no value, the record is not included in the result set.

To perform an inner join on two tables, you can use the INNER JOIN clause:

SELECT column_name [, column_name]
FROM table_name_A
INNER JOIN table_name_B
ON table_name_A.column_name = table_name_B.column_name;

Where:

  • column_name [, column_name] are the names of the columns to retrieve from either one of the specified tables.
  • table_name_A.column_name is the name of the column to compare in the first table.
  • table_name_B.column_name is the name of the column to compare in the second table.

Example

This SQL statement will perform an inner join to retrieve the first_name and last_name columns of the employees table, and the contract_name column of the contracts table, in all the rows where the value of the id column of the customers table matches the value of the employee_id column of the contracts table:

mysql> SELECT first_name, last_name, contract_name FROM employees INNER JOIN contracts ON employees.id = contracts.employee_id;
+------------+-----------+-----------------------------+
| first_name | last_name | contract_name               |
+------------+-----------+-----------------------------+
| John       | Doe       | Sales Expansion Project     |
| Grace      | Taylor    | AI Development Initiative   |
| Alice      | Johnson   | Employee Onboarding Program |
| Frank      | Moore     | Rebranding Strategy         |
| Charlie    | Davis     | Data Migration to Cloud     |
+------------+-----------+-----------------------------+
5 rows in set (0.00 sec)

Note that, if we wanted to also include the id column of the contracts table using this query:

mysql> SELECT first_name, last_name, id, contract_name FROM employees INNER JOIN contracts ON employees.id = contracts.employee_id;

SQL would throw the following error, since the id column exists in both tables:

ERROR 1052 (23000): Column 'id' in field list is ambiguous

To help SQL determine which column to choose, we can explicitly specify the table name for each column:

mysql> SELECT employees.first_name, employees.last_name, contracts.id, contracts.contract_name FROM employees INNER JOIN contracts ON employees.id = contracts.employee_id;

Left outer join

A left outer join returns all the records from the left table and the matching records from the right table.

If there is no match in the right table between the columns being compared, the value of the specified columns in the right table is replaced by NULL.

To perform a left outer join on two tables, you can use the LEFT JOIN clause:

SELECT column_name [, column_name]
FROM table_name_A
LEFT JOIN table_name_B
ON table_name_A.column_name = table_name_B.column_name;

Example

This SQL statement will perform a left outer join on the employees and contracts tables, which will fill the contract_name column with the NULL value if it doesn't match any record in the employees table:

mysql> SELECT first_name, last_name, contract_name FROM employees LEFT JOIN contracts ON employees.id = contracts.employee_id;
+------------+-----------+-----------------------------+
| first_name | last_name | contract_name               |
+------------+-----------+-----------------------------+
| John       | Doe       | Sales Expansion Project     |
| Jane       | Smith     | NULL                        |
| Alice      | Johnson   | Employee Onboarding Program |
| Bob        | Brown     | NULL                        |
| Charlie    | Davis     | Data Migration to Cloud     |
| Diana      | Miller    | NULL                        |
| Eve        | Wilson    | NULL                        |
| Frank      | Moore     | Rebranding Strategy         |
| Grace      | Taylor    | AI Development Initiative   |
| Hank       | Anderson  | NULL                        |
+------------+-----------+-----------------------------+
10 rows in set (0.00 sec)

Right outer join

A right outer join returns all the records from the right table and the matching records from the left table.

If there is no match in the left table between the columns being compared, the value of the specified columns in the left table is replaced by NULL.

To perform a right outer join on two tables, you can use the RIGHT JOIN clause:

SELECT column_name [, column_name]
FROM table_name_A
RIGHT JOIN table_name_B
ON table_name_A.column_name = table_name_B.column_name;

Example

This SQL statement will perform a left outer join on the employees and contracts tables, which will fill the first_name and last_name columns with the NULL value if it doesn't match any record in the employees table:

mysql> SELECT first_name, last_name, contract_name FROM employees RIGHT JOIN contracts ON employees.id = contracts.employee_id;
+------------+-----------+-----------------------------+
| first_name | last_name | contract_name               |
+------------+-----------+-----------------------------+
| John       | Doe       | Sales Expansion Project     |
| Grace      | Taylor    | AI Development Initiative   |
| Alice      | Johnson   | Employee Onboarding Program |
| Frank      | Moore     | Rebranding Strategy         |
| Charlie    | Davis     | Data Migration to Cloud     |
+------------+-----------+-----------------------------+
5 rows in set (0.01 sec)

Natural join

A natural join returns all the records from both tables and automatically matches columns with identical names and data types.

To perform a natural join on two tables, you can use the NATURAL JOIN clause:

SELECT column_name [, column_name]
FROM table_name_A
NATURAL JOIN table_name_B;

Note: While natural joins are more convenient for simple queries than inner joins, they can be problematic if tables have multiple columns with the same names that are not meant to be joined on.

Example

This SQL statement will perform an inner join on the employees and contracts tables, which will regroup all the columns from both tables with matching records:

mysql> SELECT * FROM employees NATURAL JOIN contracts;
+----+------------+-----------+-------------+------------+--------+------------+-----------------------------+-------------+------------+------------+-----------+--------+
| id | first_name | last_name | department  | hire_date  | salary | manager_id | contract_name               | employee_id | start_date | end_date   | status    | budget |
+----+------------+-----------+-------------+------------+--------+------------+-----------------------------+-------------+------------+------------+-----------+--------+
|  1 | John       | Doe       | Sales       | 2022-01-15 |  50000 |       NULL | Sales Expansion Project     |           1 | 2023-04-15 | 2023-10-15 | Completed |  18000 |
|  2 | Jane       | Smith     | Engineering | 2020-03-22 |  70000 |          9 | AI Development Initiative   |           9 | 2024-01-01 | NULL       | Active    |  50000 |
|  3 | Alice      | Johnson   | HR          | 2019-07-30 |  45000 |       NULL | Employee Onboarding Program |           3 | 2023-06-01 | 2023-09-01 | Completed |  12000 |
|  4 | Bob        | Brown     | Sales       | 2018-11-10 |  52000 |          1 | Rebranding Strategy         |           8 | 2023-09-15 | 2024-03-15 | Active    |  25000 |
|  5 | Charlie    | Davis     | Engineering | 2021-06-18 |  75000 |          9 | Data Migration to Cloud     |           5 | 2023-11-01 | NULL       | Active    |  40000 |
+----+------------+-----------+-------------+------------+--------+------------+-----------------------------+-------------+------------+------------+-----------+--------+
5 rows in set (0.01 sec)

Self join

A self join involves creating a relationship between different records within the same table based on related column values.

To perform a self join, you can use any of the aforementioned join clauses.

Example

This SQL statement will perform a self-join on the employees table to retrieve the last_name column of each employee alongside with their manager's name:

mysql> SELECT E.last_name AS employee, M.last_name AS manager FROM employees E JOIN employees M ON E.manager_id = M.id ORDER BY manager;
+----------+---------+
| employee | manager |
+----------+---------+
| Brown    | Doe     |
| Anderson | Doe     |
| Moore    | Miller  |
| Smith    | Taylor  |
| Davis    | Taylor  |
+----------+---------+
5 rows in set (0.00 sec)

Summary

Here's a summary of what you've learned in this lesson:

  • The inner join is used to retrieve only the matching rows between two tables based on a specified condition.
  • The left join is used to retrieve all rows from the left table and matching rows from the right table, with NULL for non-matches from the right.
  • The right join is used to retrieve all rows from the right table and matching rows from the left table, with NULL for non-matches from the left.
  • The natural join is used to automatically join two tables based on columns with the same name and datatype in both tables.
  • The self join is used to join a table with itself using aliases to compare rows within the same table based on a specified relationship.

Enjoying the courses?

I've made these courses completely free so anyone can learn from them. If they've helped you and you'd like to actively support the work behind BackendBrewery, you can leave a tip:

Support BackendBrewery
Next lesson
Retrieving Records From Multiple Tables With Joins in MySQL | Backend Brewery