Tutorials›SQL›LEFT JOIN
🟢 Free Demo
SQL TutorialTopic 12 of 20

LEFT JOIN

Keep all rows from the left table

✅ What You Will Learn

›How LEFT JOIN preserves all rows from the left table
›What NULL means in LEFT JOIN results
›How to use COALESCE to replace NULLs in LEFT JOIN output
›When to use LEFT JOIN instead of INNER JOIN
›How to find rows with no match (anti-join pattern)

LEFT JOIN returns all rows from the left (first) table, and the matching rows from the right table. If there is no match in the right table, the right table columns come back as NULL.

This is the second most used JOIN in analytics. Use it when you want to keep all records from your main table even if some do not have matching records in the second table — for example, showing all customers including those who have never placed an order.

📋 LEFT JOIN: all customers kept — Amit has no orders (NULL shown)

customer_idcustomer_namecityorder_idproductamount
1Rahul SharmaDelhi1001Laptop45000
2Priya VermaNoida1002Mobile Phone18000
3Amit KumarGurgaonNULLNULLNULL
4Sneha KapoorDelhi1004Laptop52000
5Vikram SinghNoida1005Tablet28000

Syntax

SQL SYNTAX
SELECT t1.column, t2.column
FROM table1 t1
LEFT JOIN table2 t2
  ON t1.key_column = t2.key_column;

Examples

Example 1All customers — including those with no orders
SELECT c.customer_name,
       c.city,
       o.product,
       o.amount
FROM customers c
LEFT JOIN orders o
  ON c.customer_id = o.customer_id;
OUTPUT
customer_name | city    | product      | amount
--------------|---------|--------------|-------
Rahul Sharma  | Noida   | Laptop       | 45000
Rahul Sharma  | Noida   | Headphones   | 3500
Priya Verma   | Delhi   | Mobile Phone | 18000
Amit Kumar    | Noida   | NULL         | NULL
Sneha Kapoor  | Gurgaon | NULL         | NULL
💡

Amit Kumar and Sneha Kapoor appear with NULL for order columns because they have no orders. INNER JOIN would have excluded them.

Example 2Find customers who have NEVER ordered
SELECT c.customer_name, c.city
FROM customers c
LEFT JOIN orders o
  ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;
OUTPUT
customer_name | city
--------------|--------
Amit Kumar    | Noida
Sneha Kapoor  | Gurgaon
💡

This pattern — LEFT JOIN + WHERE right_table.column IS NULL — is the standard way to find records with no match in the second table. Very common in real analytics.

📌 Key Points to Remember

  • ✓LEFT JOIN keeps ALL rows from the left table
  • ✓Unmatched rows from the right table show NULL in those columns
  • ✓The "left" table is the one after FROM, the "right" table is after JOIN
  • ✓Use LEFT JOIN + IS NULL to find records with no match
  • ✓LEFT JOIN is used more often than INNER JOIN in most reporting scenarios

🏢 Real-World Application

LEFT JOIN is critical for finding gaps in data. "Show all customers and how much they spent — including customers who have never placed an order" — that is a LEFT JOIN with customers on the left. Customers with no orders get NULL for the order columns. A common analytics pattern is LEFT JOIN + WHERE right_table.id IS NULL — this finds all customers who have never ordered, users who never logged in, or products that have never been sold. This "anti-join" is used everywhere in churn analysis, retention reporting, and gap analysis.

⚠️ Common Mistakes to Avoid

WRONGPutting the wrong table on the left
FIXThe left table is the one whose rows you want to KEEP even without a match. If you want all customers (including those with no orders), customers must be on the left: FROM customers LEFT JOIN orders.
WRONGFiltering on right table columns in WHERE instead of ON
FIXWHERE right.col = value converts a LEFT JOIN into an INNER JOIN by removing NULL rows. Put right-table filters in the ON clause: LEFT JOIN orders ON c.id = o.customer_id AND o.amount > 1000.
WRONGForgetting that aggregate functions ignore NULLs in LEFT JOIN results
FIXSUM(o.amount) correctly returns NULL (not 0) for customers with no orders. Use COALESCE(SUM(o.amount), 0) to display 0 instead of NULL.

❓ Frequently Asked Questions

What is LEFT JOIN in SQL?

LEFT JOIN returns all rows from the left (first) table and matching rows from the right (second) table. When there is no match in the right table, the right-table columns appear as NULL in the result.

When should I use LEFT JOIN instead of INNER JOIN?

Use LEFT JOIN when you want to keep all rows from the left table even if they have no match on the right. Use INNER JOIN when you only want rows that have matches in both tables.

What is the anti-join pattern in SQL?

An anti-join finds rows in the left table that have no match in the right table. Use LEFT JOIN and then filter WHERE right_table.id IS NULL. This is how you find customers with no orders, products with no sales, etc.

Is LEFT JOIN the same as LEFT OUTER JOIN?

Yes. LEFT JOIN and LEFT OUTER JOIN are identical — OUTER is optional. Both return all rows from the left table with NULLs for unmatched right table columns.

How does LEFT JOIN handle multiple matches?

If the right table has multiple rows matching one left-table row, the left-table row is duplicated once per match, just like INNER JOIN. For example, one customer with three orders will appear three times.

✏️ Practice Exercise

Using LEFT JOIN, write a query to show all customers and their total spend. Customers with no orders should show 0 as their total (hint: use COALESCE to replace NULL with 0).

← PreviousINNER JOINNext →Subqueries
🎓 Level Up Faster

Learn SQL with Live Trainer Guidance

These tutorials give you the theory. Our live SQL course at EVIKA Academy, Noida teaches you to apply SQL on real company datasets — with a trainer who uses it daily at MakeMyTrip.