How to Use MySQL JOIN to Query Data from Multiple Tables

By 

Dejan Panovski
•

Published on

•

11 min read

MySQL JOIN

MySQL is the most popular open-source relational database management system. In relational databases, data is stored in separate tables to minimize redundancy and improve data integrity. The JOIN clause allows you to combine rows from two or more tables based on a related column, making it one of the most important SQL operations to learn.

In this tutorial, you’ll learn how to use MySQL JOINs to query data from multiple tables, with clear explanations and practical examples.

Before You Begin

We assume you already have MySQL or MariaDB installed on your system.

All commands are executed inside the MySQL shell as an administrative user. To access the MySQL shell, run the following command and enter your MySQL root password when prompted:

Terminal
mysql -u root -p
Info
If you haven’t set a password for your MySQL root user, you can omit the -p option.

MySQL JOIN Types Overview

MySQL JOIN types diagram showing INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, CROSS JOIN, and SELF JOIN

MySQL supports several types of JOINs, each returning different result sets based on how rows are matched between tables:

JOIN TypeReturnsUse Case
INNER JOINOnly matching rows from both tablesGet related data that exists in both tables
LEFT JOINAll rows from the left table + matches from the rightFind records with or without related data
RIGHT JOINAll rows from the right table + matches from the leftSame as LEFT JOIN with reversed table order
FULL OUTER JOINAll rows from both tables (via UNION)Get complete data from both tables
CROSS JOINCartesian product (all combinations)Generate all possible combinations
SELF JOINTable joined with itselfQuery hierarchical or self-referencing data

Setting Up Sample Tables

To follow along with the examples in this tutorial, create a database and the sample tables using the statements below.

Create the Database

Terminal
CREATE DATABASE company_db;
USE company_db;

Create the Tables

sql
CREATE TABLE developers (
    dev_id INT PRIMARY KEY,
    dev_name VARCHAR(50),
    role VARCHAR(50),
    office VARCHAR(50),
    manager_id INT
);

CREATE TABLE projects (
    proj_id INT PRIMARY KEY,
    proj_name VARCHAR(50),
    dev_id INT
);

CREATE TABLE salaries (
    dev_id INT PRIMARY KEY,
    salary INT,
    stock_options VARCHAR(3)
);

Insert Sample Data

sql
INSERT INTO developers VALUES
(1, 'Tom', 'DevOps Engineer', 'San Francisco', 2),
(2, 'Lisa', 'Engineering Lead', 'Austin', NULL),
(3, 'Dave', 'Senior Developer', 'Seattle', 2),
(4, 'Nina', 'Backend Developer', 'New York', 3);

INSERT INTO projects VALUES
(1001, 'API Gateway', 1),
(1002, 'Cloud Migration', 2),
(1003, 'CI/CD Pipeline', 3),
(1004, 'Kubernetes Setup', 1),
(1005, 'Monitoring Dashboard', 2),
(1006, 'Mobile App', 5);

INSERT INTO salaries VALUES
(1, 95000, 'NO'),
(2, 145000, 'YES'),
(3, 120000, 'YES'),
(4, 85000, 'NO');

Sample Data Overview

Here’s what the sample tables look like:

Developers Table

Terminal
SELECT * FROM developers;
output
+--------+--------------+-------------------+---------------+------------+
| dev_id | dev_name     | role              | office        | manager_id |
+--------+--------------+-------------------+---------------+------------+
|      1 | Tom    | DevOps Engineer   | San Francisco |          2 |
|      2 | Lisa | Engineering Lead  | Austin        |       NULL |
|      3 | Dave | Senior Developer  | Seattle       |          2 |
|      4 | Nina   | Backend Developer | New York      |          3 |
+--------+--------------+-------------------+---------------+------------+

Projects Table

Terminal
SELECT * FROM projects;
output
+---------+----------------------+--------+
| proj_id | proj_name            | dev_id |
+---------+----------------------+--------+
|    1001 | API Gateway          |      1 |
|    1002 | Cloud Migration      |      2 |
|    1003 | CI/CD Pipeline       |      3 |
|    1004 | Kubernetes Setup     |      1 |
|    1005 | Monitoring Dashboard |      2 |
|    1006 | Mobile App           |      5 |
+---------+----------------------+--------+

Salaries Table

Terminal
SELECT * FROM salaries;
output
+--------+--------+---------------+
| dev_id | salary | stock_options |
+--------+--------+---------------+
|      1 |  95000 | NO            |
|      2 | 145000 | YES           |
|      3 | 120000 | YES           |
|      4 |  85000 | NO            |
+--------+--------+---------------+
Info
Notice that projects has a row with dev_id = 5, which doesn’t exist in developers. This is intentional to demonstrate how different JOINs handle unmatched rows.

MySQL INNER JOIN

The INNER JOIN returns only the rows that have matching values in both tables. This is the most commonly used JOIN type and is the default when you use JOIN without specifying a type.

Syntax:

sql
SELECT columns
FROM table1
INNER JOIN table2 ON table1.column = table2.column;

Example: Get the developer name for each project:

Terminal
SELECT p.proj_id, p.proj_name, d.dev_name
FROM projects p
INNER JOIN developers d ON p.dev_id = d.dev_id;
output
+---------+----------------------+--------------+
| proj_id | proj_name            | dev_name     |
+---------+----------------------+--------------+
|    1001 | API Gateway          | Tom    |
|    1002 | Cloud Migration      | Lisa |
|    1003 | CI/CD Pipeline       | Dave |
|    1004 | Kubernetes Setup     | Tom    |
|    1005 | Monitoring Dashboard | Lisa |
+---------+----------------------+--------------+
5 rows in set (0.00 sec)

The Mobile App project is not included because there is no matching developer with dev_id = 5.

Joining Multiple Tables:

You can chain multiple INNER JOIN clauses to retrieve data from three or more tables:

Terminal
SELECT p.proj_id, p.proj_name, d.dev_name, s.stock_options
FROM projects p
INNER JOIN developers d ON p.dev_id = d.dev_id
INNER JOIN salaries s ON d.dev_id = s.dev_id;
output
+---------+----------------------+--------------+---------------+
| proj_id | proj_name            | dev_name     | stock_options |
+---------+----------------------+--------------+---------------+
|    1001 | API Gateway          | Tom    | NO            |
|    1002 | Cloud Migration      | Lisa | YES           |
|    1003 | CI/CD Pipeline       | Dave | YES           |
|    1004 | Kubernetes Setup     | Tom    | NO            |
|    1005 | Monitoring Dashboard | Lisa | YES           |
+---------+----------------------+--------------+---------------+

MySQL LEFT JOIN

The LEFT JOIN returns all rows from the left table and matching rows from the right table. If no match exists, the result contains NULL values for the right table columns.

Syntax:

sql
SELECT columns
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;

Example: List all developers and their projects, including developers without projects:

Terminal
SELECT d.dev_name, p.proj_id, p.proj_name
FROM developers d
LEFT JOIN projects p ON d.dev_id = p.dev_id;
output
+--------------+---------+----------------------+
| dev_name     | proj_id | proj_name            |
+--------------+---------+----------------------+
| Tom    |    1001 | API Gateway          |
| Tom    |    1004 | Kubernetes Setup     |
| Lisa |    1002 | Cloud Migration      |
| Lisa |    1005 | Monitoring Dashboard |
| Dave |    1003 | CI/CD Pipeline       |
| Nina   |    NULL | NULL                 |
+--------------+---------+----------------------+

Nina appears with NULL values because she has no assigned projects.

Finding Unmatched Rows:

A common use of LEFT JOIN is finding records that don’t have matches in another table:

Terminal
SELECT d.dev_name
FROM developers d
LEFT JOIN projects p ON d.dev_id = p.dev_id
WHERE p.dev_id IS NULL;
output
+------------+
| dev_name   |
+------------+
| Nina |
+------------+

MySQL RIGHT JOIN

The RIGHT JOIN works the opposite way of LEFT JOIN. It returns all rows from the right table and matching rows from the left table.

Syntax:

sql
SELECT columns
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;

Example: List all projects and their assigned developers, including unassigned projects:

Terminal
SELECT d.dev_name, p.proj_id, p.proj_name
FROM developers d
RIGHT JOIN projects p ON d.dev_id = p.dev_id;
output
+--------------+---------+----------------------+
| dev_name     | proj_id | proj_name            |
+--------------+---------+----------------------+
| Tom    |    1001 | API Gateway          |
| Lisa |    1002 | Cloud Migration      |
| Dave |    1003 | CI/CD Pipeline       |
| Tom    |    1004 | Kubernetes Setup     |
| Lisa |    1005 | Monitoring Dashboard |
| NULL         |    1006 | Mobile App           |
+--------------+---------+----------------------+

Mobile App appears with NULL for dev_name because there is no developer with dev_id = 5.

Tip
In practice, LEFT JOIN is preferred over RIGHT JOIN. Any RIGHT JOIN can be rewritten as a LEFT JOIN by swapping the table order.

MySQL FULL OUTER JOIN

The FULL OUTER JOIN returns all rows from both tables, with NULL values where there is no match.

MySQL does not support FULL OUTER JOIN directly. However, you can simulate it by combining LEFT JOIN and RIGHT JOIN with UNION:

Syntax:

sql
SELECT columns FROM table1
LEFT JOIN table2 ON condition
UNION
SELECT columns FROM table1
RIGHT JOIN table2 ON condition;

Example: List all developers and all projects, showing unmatched rows from both:

Terminal
SELECT d.dev_name, p.proj_id, p.proj_name
FROM developers d
LEFT JOIN projects p ON d.dev_id = p.dev_id
UNION
SELECT d.dev_name, p.proj_id, p.proj_name
FROM developers d
RIGHT JOIN projects p ON d.dev_id = p.dev_id;
output
+--------------+---------+----------------------+
| dev_name     | proj_id | proj_name            |
+--------------+---------+----------------------+
| Tom    |    1001 | API Gateway          |
| Tom    |    1004 | Kubernetes Setup     |
| Lisa |    1002 | Cloud Migration      |
| Lisa |    1005 | Monitoring Dashboard |
| Dave |    1003 | CI/CD Pipeline       |
| Nina   |    NULL | NULL                 |
| NULL         |    1006 | Mobile App           |
+--------------+---------+----------------------+

This returns all developers (even those without projects) and all projects (even those without developers).

MySQL CROSS JOIN

The CROSS JOIN returns the Cartesian product of two tables—every row from the first table combined with every row from the second.

Syntax:

sql
SELECT columns
FROM table1
CROSS JOIN table2;

Example: Generate all possible developer-project combinations:

Terminal
SELECT d.dev_name, p.proj_name
FROM developers d
CROSS JOIN projects p;
output
+--------------+----------------------+
| dev_name     | proj_name            |
+--------------+----------------------+
| Tom    | API Gateway          |
| Lisa | API Gateway          |
| Dave | API Gateway          |
| Nina   | API Gateway          |
| Tom    | Cloud Migration      |
...
| Nina   | Mobile App           |
+--------------+----------------------+
24 rows in set (0.00 sec)

The result has 24 rows (4 developers × 6 projects).

Warning
Use CROSS JOIN with caution. With large tables, it can produce very large result sets and consume significant resources.

MySQL SELF JOIN

A SELF JOIN joins a table to itself. This is useful for querying hierarchical or self-referencing data, such as organizational structures.

Syntax:

sql
SELECT columns
FROM table1 alias1
JOIN table1 alias2 ON condition;

Example: The developers table has a manager_id column that references another developer’s dev_id. To list developers with their managers:

Terminal
SELECT d.dev_name AS developer, m.dev_name AS manager
FROM developers d
JOIN developers m ON d.manager_id = m.dev_id;
output
+--------------+--------------+
| developer    | manager      |
+--------------+--------------+
| Tom    | Lisa |
| Dave | Lisa |
| Nina   | Dave |
+--------------+--------------+

Lisa doesn’t appear as a developer because her manager_id is NULL.

Including rows without matches:

Use LEFT JOIN to include developers without managers:

Terminal
SELECT d.dev_name AS developer, COALESCE(m.dev_name, 'No Manager') AS manager
FROM developers d
LEFT JOIN developers m ON d.manager_id = m.dev_id;
output
+--------------+--------------+
| developer    | manager      |
+--------------+--------------+
| Tom    | Lisa |
| Lisa | No Manager   |
| Dave | Lisa |
| Nina   | Dave |
+--------------+--------------+

Common Use Cases

Here are some real-world scenarios where different JOINs are useful:

Find Orphan Records

Find projects that have no valid developer assigned:

Terminal
SELECT p.proj_name
FROM projects p
LEFT JOIN developers d ON p.dev_id = d.dev_id
WHERE d.dev_id IS NULL;
output
+------------+
| proj_name  |
+------------+
| Mobile App |
+------------+

Generate Reports with All Categories

List all developers with their total project count, including those with zero projects:

Terminal
SELECT d.dev_name, COUNT(p.proj_id) AS project_count
FROM developers d
LEFT JOIN projects p ON d.dev_id = p.dev_id
GROUP BY d.dev_id, d.dev_name
ORDER BY project_count DESC;
output
+--------------+---------------+
| dev_name     | project_count |
+--------------+---------------+
| Tom    |             2 |
| Lisa |             2 |
| Dave |             1 |
| Nina   |             0 |
+--------------+---------------+

Calculate Salary Statistics by Office

Combine multiple tables to get salary information grouped by office:

Terminal
SELECT d.office, AVG(s.salary) AS avg_salary, COUNT(*) AS dev_count
FROM developers d
INNER JOIN salaries s ON d.dev_id = s.dev_id
GROUP BY d.office
ORDER BY avg_salary DESC;
output
+---------------+------------+-----------+
| office        | avg_salary | dev_count |
+---------------+------------+-----------+
| Austin        |   145000.0 |         1 |
| Seattle       |   120000.0 |         1 |
| San Francisco |    95000.0 |         1 |
| New York      |    85000.0 |         1 |
+---------------+------------+-----------+

Performance Tips

When working with JOINs on large tables, keep these optimization tips in mind:

Index Your JOIN Columns

Always create indexes on columns used in JOIN conditions:

sql
CREATE INDEX idx_dev_id ON projects(dev_id);
CREATE INDEX idx_manager_id ON developers(manager_id);

Filter Early with WHERE

Apply WHERE conditions to reduce the dataset before joining:

Terminal
SELECT d.dev_name, p.proj_name
FROM developers d
INNER JOIN projects p ON d.dev_id = p.dev_id
WHERE d.office = 'Austin';

Select Only Required Columns

Avoid SELECT * in production queries:

sql
-- Good: Select specific columns
SELECT d.dev_name, p.proj_name FROM developers d JOIN projects p ON d.dev_id = p.dev_id;

-- Avoid: Select all columns
SELECT * FROM developers d JOIN projects p ON d.dev_id = p.dev_id;

Use EXPLAIN to Analyze Queries

Use EXPLAIN to understand how MySQL executes your queries:

Terminal
EXPLAIN SELECT d.dev_name, p.proj_name
FROM developers d
INNER JOIN projects p ON d.dev_id = p.dev_id;

Conclusion

In this MySQL JOIN tutorial, you learned how to combine data from multiple tables using different JOIN types:

  • INNER JOIN for matching rows only
  • LEFT/RIGHT JOIN for all rows from one table plus matches
  • FULL OUTER JOIN (via UNION) for all rows from both tables
  • CROSS JOIN for Cartesian products
  • SELF JOIN for hierarchical data

Understanding JOINs is essential for writing efficient SQL queries and working with relational databases. For related tasks, see our guides on listing databases and managing databases and users .

If you have any questions or feedback, feel free to leave a comment.

Tags

mysql

Linuxize Weekly Newsletter

A quick weekly roundup of new tutorials, news, and tips.

About the authors

Dejan Panovski

Dejan Panovski

Dejan Panovski is the founder of Linuxize, an RHCSA-certified Linux system administrator and DevOps engineer based in Skopje, Macedonia. Author of 800+ Linux tutorials with 20+ years of experience turning complex Linux tasks into clear, reliable guides.

View author page