Understanding LEFT JOIN in MySQL

Course: MySQL Training

Topic: Understanding LEFT JOIN

Course Link: MySQL Training Course

Demo Link: Register for a Free Course Demo

1. Introduction to LEFT JOIN

In MySQL, a LEFT JOIN is used to retrieve related data from two tables. It returns all records from the left table and the matching records from the right table. If a record in the left table has no matching record in the right table, MySQL still returns that left-table record and displays NULL for the columns from the right table.

LEFT JOIN is especially useful when you need to display every record from one table, even if related information is missing from another table.

For example, a company may want to display all employees, including employees who have not been assigned to any department. A LEFT JOIN can retrieve every employee and show department information wherever a matching department exists.

Learning Objectives

• Understand the purpose and working of LEFT JOIN in MySQL.
• Learn the syntax and structure of a LEFT JOIN query.
• Combine related information from two tables.
• Understand how unmatched records are represented using NULL.
• Differentiate LEFT JOIN from INNER JOIN and RIGHT JOIN.
• Use LEFT JOIN with WHERE, ORDER BY, GROUP BY, aggregate functions, and multiple tables.
• Identify common mistakes and apply LEFT JOIN in practical database projects.

2. What Is LEFT JOIN in MySQL?

LEFT JOIN is a type of SQL join that combines rows from two tables based on a specified matching condition. It preserves every row from the left table, regardless of whether a corresponding row exists in the right table.

The left table is the table written before the LEFT JOIN keyword, while the right table is the table written after it.

Key Characteristics

• All left-table rows: Every row from the left table is included in the result.
• Matching right-table rows: Matching data from the right table is included when the join condition is satisfied.
• Unmatched rows: If no right-table match exists, the right-side columns contain NULL.
• Join condition: The ON clause normally specifies how records from the two tables are related.
• Duplicate results: If multiple right-table rows match one left-table row, the left-table row appears multiple times.

3. Basic Syntax of LEFT JOIN

The general syntax of a LEFT JOIN query is:

SELECT columns
FROM left_table
LEFT JOIN right_table
ON left_table.common_column = right_table.common_column;

Explanation of the Syntax

4. Creating Sample Tables for LEFT JOIN

To understand LEFT JOIN, create two related tables: employees and departments. The employees table stores employee details and their department identifiers. The departments table stores department names.

Step 1: Create the Departments Table

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
);

Step 2: Insert Department Records

INSERT INTO departments (department_id, department_name)
VALUES
(1, 'Human Resources'),
(2, 'Information Technology'),
(3, 'Finance'),
(4, 'Marketing');

Step 3: Create the Employees Table

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department_id INT NULL
);

Step 4: Insert Employee Records

INSERT INTO employees (employee_id, employee_name, department_id)
VALUES
(101, 'Amit Sharma', 1),
(102, 'Priya Verma', 2),
(103, 'Rahul Singh', 3),
(104, 'Neha Patel', NULL),
(105, 'Karan Mehta', 2);

Sample Data: Employees

Sample Data: Departments

Notice that Neha Patel does not have a department identifier. Also, the Marketing department does not have an employee assigned to it. These examples help demonstrate how LEFT JOIN treats unmatched records on each side.

5. Performing a Basic LEFT JOIN

Suppose you want to display all employees along with their department names. You can use the following query:

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

Expected Output

How the Query Works

1. MySQL reads records from the employees table, which is the left table.
2. It checks each employee's department_id against the department_id in the departments table.
3. When a match exists, the department name is included in the result.
4. When no match exists, the employee still appears, but the department name is NULL.

The Marketing department does not appear in this result because the query preserves all employees, not all departments. To list every department, including departments without employees, use departments as the left table instead.

6. Understanding the ON Clause

The ON clause defines how MySQL matches records between tables. It commonly compares a primary key in one table with a foreign key in another table.

SELECT
    e.employee_name,
    d.department_name
FROM employees AS e
LEFT JOIN departments AS d
ON e.department_id = d.department_id;

In this query, e and d are table aliases. The condition matches an employee's department identifier with the department table's identifier.

Why Is the ON Clause Important?

• It specifies the relationship between the tables.
• It helps MySQL determine which records match.
• It prevents unrelated rows from being combined unintentionally.
• It makes queries easier to read when several tables are involved.

If the matching condition is incorrect or missing, the query may produce misleading results or an unexpectedly large number of rows.

7. Using Table Aliases with LEFT JOIN

Table aliases are short names assigned to tables for use within a query. They reduce repetition and improve readability, particularly when table names are long.

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

Once an alias is assigned, use it consistently to qualify the table's columns in that query. Aliases are especially helpful when joining multiple tables that contain columns with the same name.

8. LEFT JOIN with WHERE Clause

The WHERE clause filters the rows produced by the join. It is useful when you want to display only employees who satisfy a particular condition.

Example 1: Find Employees in the IT Department

SELECT
    e.employee_name,
    d.department_name
FROM employees AS e
LEFT JOIN departments AS d
ON e.department_id = d.department_id
WHERE d.department_name = 'Information Technology';

This query returns employees assigned to the Information Technology department. Employees with no matching department are excluded because their department name is NULL and does not satisfy the WHERE condition.

Example 2: Find Employees Without a Matching Department

SELECT
    e.employee_id,
    e.employee_name,
    e.department_id
FROM employees AS e
LEFT JOIN departments AS d
ON e.department_id = d.department_id
WHERE d.department_id IS NULL;

This query identifies employees who have no matching department record. It includes employees whose department identifier is NULL and can also detect non-NULL department identifiers that do not match any row in the departments table.

Important Difference: ON and WHERE

In a LEFT JOIN, conditions placed in ON and WHERE can produce different results. A condition in ON determines which right-table rows qualify as matches while preserving left-table rows. A condition in WHERE filters the completed result and may remove left-table rows that have no matching right-side values.

For example, this query keeps all employees but matches only departments with an ID of 2:

SELECT
    e.employee_name,
    d.department_name
FROM employees AS e
LEFT JOIN departments AS d
ON e.department_id = d.department_id
AND d.department_id = 2;

Employees assigned to other departments remain in the output, but their department columns will be NULL because those departments were excluded from the matching condition.

9. LEFT JOIN with ORDER BY

The ORDER BY clause sorts the rows returned by a LEFT JOIN. You can sort by a column from either table, provided it is available to the query.

Example: Sort Employees by Name

SELECT
    e.employee_name,
    d.department_name
FROM employees AS e
LEFT JOIN departments AS d
ON e.department_id = d.department_id
ORDER BY e.employee_name ASC;

ASC sorts values in ascending order, while DESC sorts them in descending order.

Example: Sort by Department Name

SELECT
    e.employee_name,
    d.department_name
FROM employees AS e
LEFT JOIN departments AS d
ON e.department_id = d.department_id
ORDER BY d.department_name ASC, e.employee_name ASC;

Rows with NULL department names are also retained. Their position in the sorted result depends on the sort direction and MySQL's NULL sorting behavior.

10. LEFT JOIN with GROUP BY and Aggregate Functions

LEFT JOIN can be combined with aggregate functions to summarize related data. For example, you can calculate how many employees belong to each department.

Example: Count Employees in Each Department

SELECT
    d.department_id,
    d.department_name,
    COUNT(e.employee_id) AS total_employees
FROM departments AS d
LEFT JOIN employees AS e
ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name
ORDER BY d.department_id;

Expected Output

Here, the departments table is the left table, so every department is included. Marketing appears even though it has no employees.

Why Use COUNT(e.employee_id) Instead of COUNT(*)?

When a department has no matching employee, the LEFT JOIN still produces a row for the department, with NULL values in the employee columns. COUNT(*) counts that result row, which can incorrectly give the department a count of 1. COUNT(e.employee_id) counts only non-NULL employee identifiers and correctly returns 0 for a department without employees.

11. LEFT JOIN with Multiple Tables

You can use multiple LEFT JOIN clauses to retrieve related information from three or more tables. Each join adds another table to the result while preserving the rows from the left-side result at that stage.

Example: Employees, Departments, and Locations

Suppose a locations table stores the location of each department.

CREATE TABLE locations (
    location_id INT PRIMARY KEY,
    city VARCHAR(100) NOT NULL
);

ALTER TABLE departments
ADD COLUMN location_id INT NULL;

INSERT INTO locations (location_id, city)
VALUES
(10, 'Mumbai'),
(20, 'Pune'),
(30, 'Delhi');

After assigning location identifiers to departments, you can retrieve employee, department, and location information together.

SELECT
    e.employee_name,
    d.department_name,
    l.city
FROM employees AS e
LEFT JOIN departments AS d
ON e.department_id = d.department_id
LEFT JOIN locations AS l
ON d.location_id = l.location_id;

This query preserves all employees. If an employee has no matching department, the department and city fields may be NULL. If the department exists but has no matching location, the employee and department details remain available while the city is NULL.

Important Rule

The order of joins and the join conditions affect how related data is matched. Use clear aliases and verify each relationship before adding more tables to a query.

12. LEFT JOIN vs INNER JOIN vs RIGHT JOIN

MySQL provides different join types to control which rows are retained when combining tables.

LEFT JOIN Example

SELECT e.employee_name, d.department_name
FROM employees AS e
LEFT JOIN departments AS d
ON e.department_id = d.department_id;

Returns all employees, whether or not they have a matching department.

INNER JOIN Example

SELECT e.employee_name, d.department_name
FROM employees AS e
INNER JOIN departments AS d
ON e.department_id = d.department_id;

Returns only employees with matching department records.

RIGHT JOIN Example

SELECT e.employee_name, d.department_name
FROM employees AS e
RIGHT JOIN departments AS d
ON e.department_id = d.department_id;

Returns all departments, including departments without employees. The employee columns are NULL when no employee matches.

13. Finding Records That Do Not Have a Match

One of the most common uses of LEFT JOIN is identifying missing relationships between tables. This technique is often called an anti-join pattern.

Example 1: Find Departments Without Employees

SELECT
    d.department_id,
    d.department_name
FROM departments AS d
LEFT JOIN employees AS e
ON d.department_id = e.department_id
WHERE e.employee_id IS NULL;

The query returns departments that do not have any matching employee. With the sample data, Marketing is returned.

Example 2: Find Employees Without Valid Department Records

SELECT
    e.employee_id,
    e.employee_name,
    e.department_id
FROM employees AS e
LEFT JOIN departments AS d
ON e.department_id = d.department_id
WHERE d.department_id IS NULL;

This query returns employees whose department identifier is NULL or does not match an existing department identifier.

Example 3: Find Customers Without Orders

Suppose a business has customers and orders tables. To find customers who have never placed an order, use:

SELECT
    c.customer_id,
    c.customer_name
FROM customers AS c
LEFT JOIN orders AS o
ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

This is a common reporting query for identifying inactive customers, provided that order_id is a non-NULL identifier for actual orders.

14. LEFT JOIN and NULL Values

NULL is used when a value is missing, unknown, or not applicable. With LEFT JOIN, NULL commonly appears in right-table columns when no matching row exists.

Checking for NULL Values

SELECT
    e.employee_name,
    d.department_name
FROM employees AS e
LEFT JOIN departments AS d
ON e.department_id = d.department_id
WHERE d.department_name IS NULL;

Use IS NULL to test for NULL values and IS NOT NULL to test for non-NULL values.

Using COALESCE to Display Alternative Text

You may want to display a readable label instead of NULL in reports.

SELECT
    e.employee_name,
    COALESCE(d.department_name, 'Not Assigned') AS department_name
FROM employees AS e
LEFT JOIN departments AS d
ON e.department_id = d.department_id;

COALESCE() returns the first non-NULL value in its argument list. In this example, employees without a matching department display the text Not Assigned.

Remember that replacing NULL with text changes the displayed value, not the underlying data or relationship between the tables.

15. Understanding Duplicate Rows in LEFT JOIN

A LEFT JOIN can return more rows than the left table when multiple records in the right table match one left-table record. This is expected behavior, not necessarily a database error.

Example: Customers and Orders

Suppose one customer has placed three orders. Joining customers to orders using customer_id returns that customer's information three times, once for each matching order.

SELECT
    c.customer_id,
    c.customer_name,
    o.order_id
FROM customers AS c
LEFT JOIN orders AS o
ON c.customer_id = o.customer_id;

If a customer has no orders, that customer still appears once with NULL in the order columns. If a customer has multiple orders, the customer appears once for each matching order.

How to Handle Duplicates

• Check whether the relationship is one-to-one or one-to-many.
• Use GROUP BY when you need summarized results.
• Use DISTINCT only when duplicate output rows should genuinely be removed.
• Aggregate child-table records before joining when that fits the reporting requirement.
• Avoid assuming that a LEFT JOIN always returns exactly the same number of rows as the left table.

16. LEFT JOIN with Aggregate Functions

Aggregate functions calculate summary values from matching records. LEFT JOIN is useful when summaries must include left-table records that have no related rows.

Example: Total Orders for Every Customer

SELECT
    c.customer_id,
    c.customer_name,
    COUNT(o.order_id) AS total_orders
FROM customers AS c
LEFT JOIN orders AS o
ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name
ORDER BY c.customer_name;

This query includes every customer. Customers without orders receive a count of 0 because COUNT(o.order_id) ignores NULL values.

Example: Total Order Amount for Every Customer

SELECT
    c.customer_id,
    c.customer_name,
    COALESCE(SUM(o.order_amount), 0) AS total_order_amount
FROM customers AS c
LEFT JOIN orders AS o
ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name;

SUM() returns NULL when there are no non-NULL values to sum. COALESCE() changes that result to 0 for reporting purposes. Make sure the chosen zero-value representation is appropriate for the business requirement.

17. Common Mistakes When Using LEFT JOIN

Mistake 1: Filtering Right-Table Values in WHERE

Consider this query:

SELECT e.employee_name, d.department_name
FROM employees AS e
LEFT JOIN departments AS d
ON e.department_id = d.department_id
WHERE d.department_id = 2;

This query removes employees who do not match department 2. Although it uses LEFT JOIN, the WHERE condition excludes NULL department identifiers from the final result.

Correction: If you want to keep all employees but match department 2 only when available, move the condition into the ON clause.

SELECT e.employee_name, d.department_name
FROM employees AS e
LEFT JOIN departments AS d
ON e.department_id = d.department_id
AND d.department_id = 2;

Mistake 2: Using the Wrong Join Condition

Joining on unrelated columns can produce incorrect matches or excessive rows.

Correction: Verify that the join condition uses the correct primary-key and foreign-key relationship, or another valid business relationship.

Mistake 3: Using COUNT(*) for Unmatched Rows

When grouping the results of a LEFT JOIN, COUNT(*) may count the preserved left-side row even when no right-side record exists.

Correction: Count a non-NULL identifier from the right table, such as COUNT(e.employee_id), when counting matching employees.

Mistake 4: Ignoring One-to-Many Relationships

If multiple right-table rows match a single left-table row, the output contains multiple rows for that left-side record.

Correction: Confirm the expected relationship and aggregate the results when the requirement is to produce one summary row per left-side record.

Mistake 5: Using = NULL

SQL does not use ordinary equality to test whether a value is NULL.

Incorrect:

WHERE d.department_id = NULL;

Correct:

WHERE d.department_id IS NULL;

Mistake 6: Selecting Ambiguous Column Names

Two tables may both contain a column called id or department_id. Using an unqualified column name can make the query ambiguous.

Correction: Qualify columns with table names or aliases, such as e.department_id and d.department_id.

18. Performance Tips for LEFT JOIN

For large databases, query design and indexing can affect the performance of joins.

• Index join columns: Ensure that the columns used in join conditions are appropriately indexed when beneficial. Primary keys are indexed automatically.
• Use suitable data types: Related key columns should use compatible data types.
• Select required columns: Avoid selecting every column when only a few are needed.
• Use meaningful aliases: Aliases improve readability in queries involving multiple tables.
• Filter carefully: Apply filters that match the actual reporting requirement, and understand whether they belong in ON or WHERE.
• Review execution plans: Use EXPLAIN to inspect how MySQL plans to execute the query.
• Aggregate when appropriate: If you only need summaries, consider whether pre-aggregating related data can reduce the number of rows processed.
• Keep statistics current: Updated table statistics can help the optimizer choose an effective execution plan.

Example: Inspect a LEFT JOIN Query

EXPLAIN
SELECT
    e.employee_name,
    d.department_name
FROM employees AS e
LEFT JOIN departments AS d
ON e.department_id = d.department_id;

The EXPLAIN output helps you examine the tables, possible indexes, selected access methods, and estimated rows. The best optimization depends on table size, data distribution, indexes, and the specific query.

19. Real-World Applications of LEFT JOIN

LEFT JOIN is used in many practical database operations where all records from a primary entity must be retained even when related information is unavailable.

20. Practical Project: Employee and Department Report

Project Objective: Create a report showing every employee, their department, and whether they have been assigned to a department. Also, identify departments that have no employees.

Task 1: Display All Employees and Their Departments

SELECT
    e.employee_id,
    e.employee_name,
    COALESCE(d.department_name, 'Not Assigned') AS department_name
FROM employees AS e
LEFT JOIN departments AS d
ON e.department_id = d.department_id
ORDER BY e.employee_id;

Task 2: Find Employees Without a Matching Department

SELECT
    e.employee_id,
    e.employee_name
FROM employees AS e
LEFT JOIN departments AS d
ON e.department_id = d.department_id
WHERE d.department_id IS NULL;

Task 3: Count Employees in Each Department

SELECT
    d.department_id,
    d.department_name,
    COUNT(e.employee_id) AS employee_count
FROM departments AS d
LEFT JOIN employees AS e
ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name
ORDER BY d.department_id;

Task 4: Find Departments Without Employees

SELECT
    d.department_id,
    d.department_name
FROM departments AS d
LEFT JOIN employees AS e
ON d.department_id = e.department_id
WHERE e.employee_id IS NULL;

Expected Learning Outcomes

• Retrieve all rows from a chosen left table.
• Identify matching and unmatched relationships.
• Replace NULL values with readable labels when appropriate.
• Count related records correctly.
• Use LEFT JOIN to build practical reports.

21. Practice Exercises

Complete the following exercises to strengthen your understanding of LEFT JOIN.

1. Display every employee with their employee ID, name, and department name.
2. Display all departments, including those without employees.
3. Find employees who do not have a matching department.
4. Find departments that do not have any employees.
5. Count the number of employees in each department.
6. Display every employee and replace missing department names with Not Assigned.
7. Sort employees by department name and then employee name.
8. Use LEFT JOIN with three tables to display employees, departments, and cities.
9. Explain why a LEFT JOIN may return more rows than the left table.
10. Write a customers-and-orders query that lists customers who have never placed an order.

22. Interview Questions and Answers

Q1. What is LEFT JOIN in MySQL?

LEFT JOIN returns all records from the left table and matching records from the right table. If no match exists, the right-table columns contain NULL.

Q2. What is the difference between LEFT JOIN and INNER JOIN?

LEFT JOIN preserves every left-table record, while INNER JOIN returns only records with a matching row in both tables.

Q3. What happens when there is no matching row in the right table?

The left-table row remains in the result, and the selected columns from the right table contain NULL.

Q4. Can LEFT JOIN return duplicate rows?

Yes. If multiple right-table rows match one left-table row, the left-table information appears once for each match.

Q5. What is the purpose of the ON clause?

The ON clause defines the condition used to match rows between the tables.

Q6. Can LEFT JOIN be used with more than two tables?

Yes. Multiple LEFT JOIN clauses can be used to combine data from several related tables.

Q7. How do you find rows without a match?

Use LEFT JOIN and filter for NULL in a non-NULLable identifier from the right table, for example WHERE d.department_id IS NULL when that column is a primary key.

Q8. What is the difference between ON and WHERE in LEFT JOIN?

ON determines which right-table records qualify as matches. WHERE filters the joined result and may remove left-table rows that have no match.

Q9. Why is COUNT(right_table.id) useful with LEFT JOIN?

COUNT(expression) ignores NULL values. Counting a non-NULL identifier from the right table therefore returns zero for a left-side group without matching right-side records.

Q10. How can you replace NULL with a meaningful label?

Use COALESCE(), for example COALESCE(d.department_name, 'Not Assigned').

Q11. Does LEFT JOIN guarantee that the result has the same number of rows as the left table?

No. It preserves all left-table rows, but one left-side row can appear multiple times if multiple right-side rows match it.

Q12. How can you improve LEFT JOIN performance?

Use appropriate indexes, compatible join-column data types, selective column lists, suitable query conditions, and EXPLAIN to inspect the execution plan.

23. Quick Revision Checklist

• LEFT JOIN returns every row from the left table.
• Matching right-table records are included.
• Unmatched right-table columns contain NULL.
• The ON clause defines the matching condition.
• WHERE filters the final result and may exclude unmatched rows.
• Use IS NULL to find missing matches.
• Use COALESCE() to display a replacement for NULL.
• Use COUNT(right_table.id) to count matching right-side records.
• Multiple matching right-side records can produce repeated left-side information.
• LEFT JOIN can be combined with GROUP BY, ORDER BY, and aggregate functions.
• Choose the left table according to which records must always be included.
• Use EXPLAIN and appropriate indexes when investigating performance.

24. Conclusion

LEFT JOIN is an essential MySQL technique for combining related information while preserving all records from the left table. It is particularly useful when some records may not have related data, such as employees without departments, customers without orders, or departments without employees.

To use LEFT JOIN effectively, understand the roles of the left and right tables, write the correct ON condition, handle NULL values properly, and be aware of how WHERE filters and one-to-many relationships affect the results. Practicing these concepts will help you create accurate database queries and meaningful reports.

To learn more about MySQL and database querying, explore the MySQL Training Course at JustAcademy or register for a course demo.

25. Additional Learning Resource

For the official reference on MySQL join syntax, visit the MySQL JOIN Clause Documentation.