• +91-9142290450

  • Hajipur, Bihar, 844101

☰
❮
❯

MySQL LEFT JOIN

← Previous Next →

When working with relational databases, you’ll often need to retrieve data that exists in one table and may or may not have a matching record in another. For example, you might want to see a list of all customers, including those who haven’t placed any orders yet.

This is where the MySQL LEFT JOIN becomes very useful. It allows you to get all records from the left table and the matching records from the right table. If there’s no match, MySQL fills the missing columns from the right table with NULL.

What Is a LEFT JOIN in MySQL?

A LEFT JOIN (also known as LEFT OUTER JOIN) returns all rows from the left table and only the matching rows from the right table.

If a row in the left table doesn’t have a corresponding match in the right table, MySQL still includes that row in the result and fills in NULL values for the right table columns.

This join is helpful when you want to ensure that all records from the left table appear, even if related data in another table doesn’t exist.

Syntax of LEFT JOIN

The general syntax of a LEFT JOIN is:

SELECT columns
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

Explanation

  • table1 → The left table (all rows from this table will appear in the result).

  • table2 → The right table (only matching rows will appear).

  • ON → Defines the relationship between both tables.

Example Tables

Let’s take two simple tables — customers and orders.

customers

customer_id customer_name city
1 Ananya Delhi
2 Priya Mumbai
3 Isha Pune
4 Riya Kolkata

orders

order_id customer_id amount
101 1 500
102 2 700
103 1 350

Example: LEFT JOIN Query

Now, let’s join these two tables to get a list of all customers and their orders.

SELECT customers.customer_name, orders.order_id, orders.amount
FROM customers
LEFT JOIN orders
ON customers.customer_id = orders.customer_id;

Result:

customer_name order_id amount
Ananya 101 500
Ananya 103 350
Priya 102 700
Isha NULL NULL
Riya NULL NULL

Explanation

  • The first three rows show customers with matching orders.

  • Isha and Riya appear in the result even though they haven’t placed any orders.

  • Since there are no matching records in orders for them, MySQL fills NULL for those columns.

Using Table Aliases

You can make the query shorter and cleaner using table aliases:

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

This is exactly the same query as before, but easier to read—especially when dealing with long table names or multiple joins.

LEFT JOIN with WHERE Clause

You can use a WHERE clause to filter results after joining tables.

For example, to find customers from Delhi along with their orders:

SELECT c.customer_name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
WHERE c.city = 'Delhi';

Result:

customer_name order_id amount
Ananya 101 500
Ananya 103 350

Handling NULL Values

Since LEFT JOIN can produce NULL values for unmatched rows, it’s common to replace them with something more readable using the IFNULL() function.

For example, to display “No Orders Yet” when a customer has no order:

SELECT c.customer_name,
       IFNULL(o.order_id, 'No Orders Yet') AS order_id,
       IFNULL(o.amount, 0) AS amount
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id;

Result:

customer_name order_id amount
Ananya 101 500
Ananya 103 350
Priya 102 700
Isha No Orders Yet 0
Riya No Orders Yet 0

LEFT JOIN with Aggregate Functions

You can use aggregate functions like COUNT(), SUM(), or AVG() to summarize data.

For example, to show the total number of orders each customer has placed:

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

Result:

customer_name total_orders
Ananya 2
Priya 1
Isha 0
Riya 0

Even customers with no orders appear here, which is the key feature of LEFT JOIN.

LEFT JOIN with Multiple Tables

You can join more than two tables in a single query.
Let’s add another table — payments:

payments

payment_id order_id payment_status
501 101 Paid
502 102 Pending

Now, let’s write a query to get all customers and their order payment status:

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

Result:

customer_name order_id payment_status
Ananya 101 Paid
Ananya 103 NULL
Priya 102 Pending
Isha NULL NULL
Riya NULL NULL

Explanation:

  • All customers are listed.

  • Orders without a payment record (like order 103) show NULL.

  • Customers without any orders (Isha and Riya) also appear with NULL values.

Difference Between INNER JOIN and LEFT JOIN

Feature INNER JOIN LEFT JOIN
Matching rows Returns only rows that have matches in both tables Returns all rows from the left table
Unmatched rows Excluded from result Included with NULL values
Common use When only related records are needed When all records from one table must appear
Output size Smaller Equal to or larger than INNER JOIN

Performance Considerations

  • Always index the columns used in the ON clause to make joins faster.

  • Avoid unnecessary LEFT JOINs when an INNER JOIN can do the job.

  • Handle NULL values properly, especially when using aggregate functions.

  • Use EXPLAIN to analyze and optimize complex join queries.

Common Mistakes to Avoid

  1. Filtering on right table columns in WHERE clause:
    This can accidentally convert a LEFT JOIN into an INNER JOIN.
    Instead, move such filters to the ON condition if you still want unmatched rows.

    ❌ Incorrect:

    SELECT * FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.amount > 500;

    ✅ Correct:

    SELECT * FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id AND o.amount > 500;

  2. Using SELECT *** with many joins:
    It can cause confusion if columns have the same names. Always specify column names clearly.

When to Use LEFT JOIN

Use a LEFT JOIN when:

  • You want all records from one table, even if they don’t have related data.

  • You’re generating reports like “Customers without Orders” or “Employees without Departments.”

  • You need a full picture, not just matched data.

Summary of the Tutorial

The MySQL LEFT JOIN is a powerful tool for combining data while keeping unmatched records visible. It’s ideal when you want to include all entries from one table and only matching ones from another.

By understanding how LEFT JOIN works—and how it differs from INNER JOIN—you can build flexible queries that display both complete and missing relationships in your data. This becomes especially useful in analytics, reporting, and audit systems.

Practice Questions

  1. Write a query to display all employees and their department names using LEFT JOIN, even if some employees are not assigned to any department.

  2. Using a LEFT JOIN, list all customers and their corresponding order IDs from the customers and orders tables. Show customers even if they have not placed any orders.

  3. Write a query using LEFT JOIN to find all students and their course names from students and courses tables, including students who are not enrolled in any course.

  4. Write a LEFT JOIN query to display product names and their supplier names from products and suppliers tables. Show products even if they don’t have a supplier.

  5. Create a query that shows all authors and their book titles using a LEFT JOIN, including authors who haven’t written any books.

  6. Write a query using LEFT JOIN to show each customer’s name and total number of orders they’ve placed. Include customers with zero orders.

  7. Display all departments and the names of employees working in each using LEFT JOIN, showing departments even if they have no employees.

  8. Write a query that lists all users and their last login date from the users and logins tables. Show users even if they’ve never logged in.

  9. Using LEFT JOIN, list all teachers and the subjects they teach from teachers and subjects tables. Include teachers who aren’t assigned any subject.

  10. Write a query to show all movies and their corresponding ratings from movies and reviews tables, displaying movies even if they have no reviews.

← Previous Next →

Try a Short Quiz.

coding learning websites codepractice

No quizzes available.

MySQL

online coding class codepractice

Introduction to MySQL

MySQL SQL Basics

MySQL Functions & Operators

MySQL Joins & Unions

MySQL Grouping & Filtering

Database & Table Management

MySQL Constraints

Date & Views

References

codepractice logo
Login
Go Back Top