10.3 LEFT JOIN

Level: 10 | Version: v1.1 | Author: Meptrasoft

Overview

LEFT JOIN returns all rows from the left table and the matching rows from the right table.

When no match exists, the right-side columns contain NULL.

LEFT TABLE
    +
RIGHT TABLE
    ↓
LEFT JOIN
    ↓
ALL LEFT ROWS
+ MATCHING RIGHT ROWS

LEFT JOIN → Keep everything from the left table.

Practice Tables

This topic reuses the customers and orders tables from 10.2 INNER JOIN.

Some customers have no orders:

Anjali Verma
Rahul Das
Divya Nair

These customers are important for understanding LEFT JOIN.

Basic LEFT JOIN

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

Unlike INNER JOIN, customers without orders are also returned.

Example:

No matching order → NULL on the right side.

LEFT JOIN vs INNER JOIN

INNER JOIN
→ MATCHING ROWS ONLY

LEFT JOIN
→ ALL LEFT ROWS
  + MATCHING RIGHT ROWS

For the customers and orders example:

• INNER JOIN excludes customers without orders.
• LEFT JOIN keeps those customers.

Using Aliases

Aliases make the query easier to read:

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

Here:

c → customers
o → orders

Finding Customers Without Orders

A common use of LEFT JOIN is finding rows that have no match.

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

This returns customers who have no matching order.

LEFT JOIN
   ↓
KEEP ALL CUSTOMERS
   ↓
WHERE order_id IS NULL
   ↓
CUSTOMERS WITHOUT ORDERS

Common Beginner Mistakes

Placement Quick Points

LEFT JOIN
→ ALL LEFT ROWS
→ MATCHING RIGHT ROWS

NO MATCH
→ RIGHT SIDE = NULL

• LEFT JOIN keeps every row from the left table.
• Matching rows from the right table are added.
• If no match exists, right-side columns become NULL.
• LEFT JOIN is useful for finding records with and without matches.
• WHERE right_table.key IS NULL can identify unmatched left rows.

Interview Questions

What is a LEFT JOIN?

A LEFT JOIN returns all rows from the left table and matching rows from the right table.

What happens when there is no match?

The left row is still returned, and the right-table columns contain NULL.

What is the difference between INNER JOIN and LEFT JOIN?

TEXT

INNER → Only matching rows
LEFT  → All left rows + matching right rows

How do you find customers without orders?

Use a LEFT JOIN and check the right-side key for NULL:

SQL

WHERE o.order_id IS NULL;

Which table is the left table?

The table written immediately after FROM.

Practice & Hands-On Exercises

Using customers and orders:

1. Display all customers with their order IDs:

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

2. Identify customers who have no orders:

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

3. Display customer names and order amounts.
4. Rewrite the query using aliases.
5. Explain the difference between INNER JOIN and LEFT JOIN.

💡 Tip: Test your queries using the in-browser interactive runner above.

Key Takeaway

LEFT JOIN
    ↓
ALL LEFT ROWS
    +
MATCHING RIGHT ROWS
    ↓
NO MATCH → NULL

LEFT JOIN keeps every row from the left table and adds matching data from the right table; unmatched right-side values become NULL.

End of LEFT JOIN