Beginner

558. LEFT JOIN Filter

Understand how filter placement changes the behavior of a LEFT JOIN.

📘 Lesson 558 of 728 ⏱ 13 min 🎯 Intermediate

A LEFT JOIN preserves all rows from the left table, but conditions placed in WHERE can remove rows where the right-side values are NULL. Conditions in the ON clause can preserve unmatched left-side rows.

Incorrect filter placement is a common SQL mistake and can unintentionally turn an OUTER JOIN into behavior similar to an INNER JOIN.

Return all customers but only match orders above ₹50,000.

SQL 
SELECT
    c.CustomerName,
    o.OrderAmount
FROM Customers c
LEFT JOIN Orders o
    ON c.CustomerID = o.CustomerID
   AND o.OrderAmount > 50000;

Used in customer reporting, exception reporting, data-quality analysis, and business dashboards.

  • LEFT JOIN preserves left-side rows.
  • ON filters control matching rows.
  • WHERE filters the final result.
  • WHERE conditions on right-side columns can remove NULL matches.
  • Filter placement is important with OUTER JOINs.
Why place the order filter in ON for a LEFT JOIN?

It allows the LEFT JOIN to preserve customers even when they have no matching order above the specified amount.

What can happen if the same filter is placed in WHERE?

Customers without a qualifying order may be removed from the result.

Have you finished this lesson?

Introduction to SQL – All Lessons