Beginner

661. Filter in JOIN ON Clause

Learn how placing a filter inside the ON clause changes which rows are matched.

📘 Lesson 661 of 728 ⏱ 14 min 🎯 Advanced

A condition placed in the ON clause controls which rows from the joined table are considered matches.

For OUTER JOINs, filtering in ON can preserve unmatched rows while restricting the related records.

Show every customer but only orders above ₹10,000.

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

Used in customer reporting, financial analysis, transaction filtering, and dashboard queries.

  • ON controls matching logic.
  • LEFT JOIN still preserves customers without matching orders.
  • The filter affects only the right-side matches.
  • This differs from putting the same condition in WHERE.
  • Useful for conditional OUTER JOINs.
What happens when a filter is placed in ON?

It restricts which rows qualify as matches during the JOIN.

Why is this useful with LEFT JOIN?

It can filter related rows while still preserving unmatched rows from the left table.

Have you finished this lesson?

Introduction to SQL – All Lessons