Beginner

662. Filter in JOIN WHERE Clause

Understand how filtering a joined table in WHERE can change an OUTER JOIN result.

📘 Lesson 662 of 728 ⏱ 14 min 🎯 Advanced

A WHERE condition is applied after the JOIN result is formed, which can remove rows containing NULL values from an OUTER JOIN.

Incorrect filter placement can unintentionally eliminate unmatched records.

Return only customers having orders above ₹10,000.

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

Used in reporting, transaction analysis, and troubleshooting unexpected JOIN results.

  • WHERE filters the final result.
  • NULL right-side values fail the condition.
  • This can make a LEFT JOIN behave like an INNER JOIN.
  • Use ON when unmatched left rows must be preserved.
  • Use WHERE when unmatched rows should be removed.
Why can WHERE remove unmatched LEFT JOIN rows?

Unmatched rows contain NULL values on the right side, and a normal comparison in WHERE does not evaluate to TRUE for NULL.

When should a filter be placed in WHERE?

Use WHERE when the final result should exclude rows that do not satisfy the condition.

Have you finished this lesson?

Introduction to SQL – All Lessons