LEFT JOIN

#Introduction

LEFT JOIN is extremely important in real-world backend development because it helps you find missing or optional data. If you've ever needed to answer questions like:

• Which users haven't placed an order?
• Which employees don't have managers?
• Which events have no tickets sold?
• Which customers never made a payment?

…you're looking for LEFT JOIN.

#Sample Tables

Notice: Neha and Ankit have never placed an order.

#Why Do We Need LEFT JOIN?

Your manager asks: show me all users, even if they've never placed an order. INNER JOIN can't do this, because it returns only rows with matches in both tables. This is where LEFT JOIN is used.

#INNER JOIN vs LEFT JOIN

INNER JOIN returns only matching records — Neha and Ankit disappear because they have no matching orders.

LEFT JOIN returns:

• Every row from the left table
• Matching rows from the right table
• NULL for right-table columns when no match exists

#LEFT JOIN Syntax

SELECT columns
FROM table1 t1
LEFT JOIN table2 t2
    ON t1.id = t2.id;

Real example:

SELECT u.name,
       o.order_id
FROM users u
LEFT JOIN orders o
    ON u.user_id = o.user_id;

Notice: Rahul appears twice because he has two matching orders.

#How LEFT JOIN Works

Unlike INNER JOIN, a LEFT JOIN preserves rows from the left table even when no matching row exists in the right table.

#Why Does NULL Appear?

Does Neha have an order? No. Instead of removing Neha from the result, MySQL returns Neha | NULL. Here NULL means no matching row exists in the right table — Neha exists in users, no matching user_id exists in orders, so order_id and amount are NULL.

#Finding Missing Records (Anti-Join)

One of the most common SQL interview patterns.

SELECT u.user_id,
       u.name
FROM users u
LEFT JOIN orders o
    ON u.user_id = o.user_id
WHERE o.order_id IS NULL;

Why Does This Work?

After the LEFT JOIN, the rows are Rahul–101, Rahul–103, Amit–102, Neha–NULL, Ankit–NULL. Then WHERE o.order_id IS NULL keeps only the rows where no matching order exists.

The Anti-Join Pattern

Common interview questions: customers without orders, employees without managers, products never sold, students without courses, events with no bookings. The common solution:

SELECT t1.*
FROM table1 t1
LEFT JOIN table2 t2
    ON t1.id = t2.id
WHERE t2.id IS NULL;

(Test a column that can't be NULL in a real match — usually the right table's primary key. NOT EXISTS is the equivalent subquery form.)

#LEFT JOIN with WHERE

One of the most important LEFT JOIN concepts. Requirement: show all users, but return only their orders where the amount is greater than 500.

SELECT u.name,
       o.amount
FROM users u
LEFT JOIN orders o
    ON u.user_id = o.user_id
WHERE o.amount > 500;

Neha and Ankit disappear! Why? For unmatched users, o.amount is NULL, and NULL > 500 doesn't evaluate to TRUE — so the WHERE clause removes those rows. The query effectively returns only rows with qualifying orders.

Correct Way — Filter in the ON Clause

To preserve all users, move the right-table filter into the ON condition:

SELECT u.name,
       o.amount
FROM users u
LEFT JOIN orders o
    ON u.user_id = o.user_id
   AND o.amount > 500;

AND o.amount > 500 controls which rows from orders are allowed to match. It doesn't remove users from the left table.

ON vs WHERE in a LEFT JOIN

An extremely common interview question.

#Multiple LEFT JOINs

Real applications often contain several optional relationships — users → orders → payments → coupons:

SELECT u.name,
       o.order_id,
       p.payment_status,
       c.coupon_code
FROM users u
LEFT JOIN orders o
    ON u.user_id = o.user_id
LEFT JOIN payments p
    ON o.order_id = p.order_id
LEFT JOIN coupons c
    ON o.order_id = c.order_id;

Even if a user has no order, an order has no payment, or an order has no coupon, the earlier left-side rows still appear, with missing right-side values as NULL.

#LEFT JOIN vs INNER JOIN

-- Only users who have orders
SELECT u.name, o.order_id
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id;

-- All users, whether they have orders or not
SELECT u.name, o.order_id
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id;

#Spring Boot / JPA Example

@Entity
class User {

    @OneToMany(mappedBy = "user")
    private List<Order> orders;
}

Rahul has orders; Neha has none. JPQL:

@Query("""
        SELECT DISTINCT u
        FROM User u
        LEFT JOIN FETCH u.orders
        """)
List<User> findAllUsersWithOrders();

Hibernate generates SQL similar to:

SELECT ...
FROM users u
LEFT JOIN orders o
    ON u.user_id = o.user_id;

Every user is returned; orders are loaded when available. DISTINCT is used in JPQL fetch joins to avoid duplicate root entities when a user has multiple orders. (Hibernate 6 de-duplicates fetch-join roots automatically, so it's optional there.)

#Production Examples

Find Inactive Users

Business requirement: find users who have never placed an order.

SELECT u.user_id,
       u.name
FROM users u
LEFT JOIN orders o
    ON u.user_id = o.user_id
WHERE o.order_id IS NULL;

Find Events with No Bookings

SELECT e.event_id,
       e.event_name
FROM events e
LEFT JOIN tickets t
    ON e.event_id = t.event_id
WHERE t.ticket_id IS NULL;

Find Employees Without a (Valid) Manager

SELECT e.name
FROM employees e
LEFT JOIN employees m
    ON e.manager_id = m.employee_id
WHERE m.employee_id IS NULL;

This is also a self join — the employees table is joined with itself (next chapter). It returns employees with a NULL manager_id and those pointing to a manager who doesn't exist.

#Performance Tips

1. Index the JOIN Columns

users.user_id (primary key) is already indexed; orders.user_id (foreign key) should be indexed when it's frequently used for joins.

2. Understand WHERE Filters on the Right Table

WHERE o.amount > 100 after a LEFT JOIN removes rows where o.amount is NULL, making the query behave like an INNER JOIN for that condition. If you need to preserve every left row but only match qualifying right rows, use LEFT JOIN orders o ON u.user_id = o.user_id AND o.amount > 100.

3. Select Only Required Columns

Prefer SELECT u.name, o.amount over SELECT * — less data transferred, less memory, cleaner results and easier maintenance.

#Hands-On Assignment

Create users (user_id, name) and orders (order_id, user_id, amount). Insert 5 users, of which only 3 have orders. Then:

1. Display all users with their orders.
2. Display users without orders.
3. Display users whose orders exceed 500, while still showing users with no qualifying orders.
4. Count how many users have no orders.
5. Display each user with their total order amount.

Challenge Problems

1. Find products that have never been ordered.
2. Find customers who never made a payment.
3. Find events with no ticket bookings.
4. Return all users and their latest order.
5. Explain why moving a filter from WHERE to ON changes the result of a LEFT JOIN.
6. Find employees who don't have a valid manager.
7. Find all customers and show only their successful payments, while preserving customers without successful payments.