The Difference Between INNER JOIN, LEFT JOIN, and RIGHT JOIN in MySQL

When working with a relational database, it's almost impossible to work with just one table. You'll definitely need to combine data from two or more tables. In MySQL, this table-joining operation is done with JOIN. There are a few most commonly used types of JOIN: INNER JOIN, LEFT JOIN, and RIGHT JOIN. This article explains the differences practically with real examples.

Preparing the Example Tables

Before discussing the JOIN differences, let's prepare two example tables: the employees table and the departments table.

-- Create the departments table
CREATE TABLE departments (
    dept_id INT AUTO_INCREMENT PRIMARY KEY,
    dept_name VARCHAR(100) NOT NULL
) ENGINE=InnoDB;

-- Create the employees table
CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    dept_id INT,
    salary DECIMAL(10, 2)
) ENGINE=InnoDB;

-- Insert department data
INSERT INTO departments (dept_name) VALUES
('Engineering'),
('Marketing'),
('HR'),
('Finance');

-- Insert employee data (some have no department)
INSERT INTO employees (name, dept_id, salary) VALUES
('Andi',   1, 8000000),
('Budi',   1, 7500000),
('Citra',  2, 6000000),
('Deni',   NULL, 5500000),
('Eka',    5, 6500000);  -- dept_id 5 doesn't exist in the departments table

Note that Deni has no department (NULL), and Eka has a dept_id that doesn't exist in the departments table. These conditions are important for understanding the difference between each type of JOIN.

INNER JOIN – Only Matching Data

INNER JOIN returns rows that have a match in both tables. If one side has no match, that row won't appear in the result.

-- INNER JOIN: only show employees with a valid department
SELECT
    e.name AS employee_name,
    d.dept_name AS department,
    e.salary
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id
ORDER BY e.name;

Result: Only Andi, Budi, and Citra appear. Deni (dept_id NULL) and Eka (invalid dept_id) don't appear because there's no match in the departments table.

LEFT JOIN – All Data from the Left Table

LEFT JOIN returns all rows from the left table, plus matching rows from the right table. If there's no match in the right table, its columns are filled with NULL.

-- LEFT JOIN: show all employees, including those without a department
SELECT
    e.name AS employee_name,
    d.dept_name AS department,
    e.salary
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
ORDER BY e.name;

Result: All 5 employees appear. Deni and Eka still show up, but their department column contains NULL.

Finding Unmatched Data with LEFT JOIN

LEFT JOIN is very useful for finding data that has no match in another table.

-- Find employees who don't have a department yet
SELECT
    e.name AS employee_name,
    e.dept_id
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
WHERE d.dept_id IS NULL;

RIGHT JOIN – All Data from the Right Table

RIGHT JOIN is the opposite of LEFT JOIN. It returns all rows from the right table, plus matching rows from the left table. The columns from the left table will be NULL if there's no match.

-- RIGHT JOIN: show all departments, including those with no employees
SELECT
    e.name AS employee_name,
    d.dept_name AS department
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id
ORDER BY d.dept_name;

Result: The Finance and HR departments appear even though they have no employees, with the employee_name column containing NULL.

Finding Departments Without Employees with RIGHT JOIN

-- Find departments that don't have any employees yet
SELECT
    d.dept_id,
    d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id
WHERE e.employee_id IS NULL;

JOIN with More than Two Tables

You can join more than two tables in one query. For example, adding a projects table:

-- Join three tables at once
SELECT
    e.name AS employee,
    d.dept_name AS department,
    p.project_name AS project
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id
LEFT JOIN projects p ON e.employee_id = p.employee_id
ORDER BY e.name;

Quick Comparison

• INNER JOIN: Only rows that have a match in both tables. Good for retrieving data that is definitely connected.
• LEFT JOIN: All rows from the left table + matches from the right table. Ideal for finding data that may not have a match yet.
• RIGHT JOIN: All rows from the right table + matches from the left table. Rarely used; can be replaced with a LEFT JOIN with the table order swapped.

Practical Tips

1. Use table aliases (like e for employees) to make queries more concise and readable.
2. A RIGHT JOIN can always be converted into a LEFT JOIN by swapping the table positions, so many developers only use LEFT JOIN for consistency.
3. Always include the proper ON condition to avoid a Cartesian Product (multiplying all rows).
4. Add an index to the columns used as the JOIN condition for better performance.

Conclusion

INNER JOIN, LEFT JOIN, and RIGHT JOIN are the three most commonly used types of JOIN in MySQL. INNER JOIN only retrieves matching data in both tables, LEFT JOIN retrieves all data from the left table, and RIGHT JOIN retrieves all data from the right table. Understanding when to use each type of JOIN is the key to writing efficient queries and producing accurate data for your application's needs.