A customer without a qualifying order vanishes from a report that was meant to show everyone. The LEFT JOIN is undone by a right-table filter in WHERE. Put a match condition in ON when unmatched left rows must remain.
Show the LEFT JOIN Losing Customers
Create a tiny customer and order set. One customer has no qualifying order. The outer join first produces a NULL-extended order row for that customer, but the WHERE predicate on the order status evaluates to UNKNOWN and removes it. The output contains only customers with a shipped order.
CREATE TABLE #Customer (CustomerID int PRIMARY KEY, Name varchar(30));
CREATE TABLE #Orders (OrderID int PRIMARY KEY,
CustomerID int, Status varchar(12));
INSERT #Customer VALUES (1,'Ava'),(2,'Ben'),(3,'Cora');
INSERT #Orders VALUES (10,1,'Shipped'),(11,2,'Pending');
SELECT c.CustomerID,c.Name,o.OrderID
FROM #Customer AS c
LEFT JOIN #Orders AS o ON o.CustomerID = c.CustomerID
WHERE o.Status = 'Shipped';Only Ava remains. Ben has an order with another status, and Cora has no order, but both disappear. I ask the report owner whether those two customers should appear with a NULL OrderID. That answer decides where the filter belongs; syntax alone cannot define the report.
Move the Condition Into ON
Put the right-table status condition in the join predicate. Now SQL Server looks for a shipped order for each customer and preserves the customer when none exists. Ava has a matching order; Ben and Cora remain with NULL order columns. This is a different result contract from filtering completed joined rows.
SELECT c.CustomerID,c.Name,o.OrderID
FROM #Customer AS c
LEFT JOIN #Orders AS o
ON o.CustomerID = c.CustomerID
AND o.Status = 'Shipped';Run the comparison snippets in the same session after the setup block creates the temporary tables. The two queries use the same tables and status value. The clause position changes which rows are kept. For a child-table filter that is meant to limit matches but retain every parent, ON is the clear location. A WHERE predicate on a column of the preserved table still filters parents afterward and belongs in WHERE when that is intended.
Read the Plan of a Filtered LEFT JOIN
On a real indexed table, view the actual execution plan for both forms. SQL Server can simplify the first query to an Inner Join because its WHERE condition rejects NULL-extended rows. Even on these temporary tables, the first plan showed an Inner Join, while the ON version kept its outer join. The plan is allowed to change the physical join type while preserving the query's actual semantics. The corrected ON version needs the outer-row behavior, though physical plan details can vary.
An execution plan is helpful evidence but the row test is decisive. A graphical Left Outer Join does not guarantee every left row survives later Filter operators. Follow each downstream predicate. I compare actual output row counts, estimated rows, and the join property for both versions before changing a report.
Beware the IS NULL Shortcut in WHERE
A common attempted fix is WHERE o.Status = 'Shipped' OR o.OrderID IS NULL. That can restore customers with no orders, but it still loses a customer whose only order is Pending. The join found an order, so OrderID is not NULL, and the status branch is false. The ON placement correctly treats a non-shipped order as no qualifying match.
SELECT c.CustomerID,c.Name,o.OrderID
FROM #Customer AS c
LEFT JOIN #Orders AS o ON o.CustomerID = c.CustomerID
WHERE o.Status = 'Shipped' OR o.OrderID IS NULL;This returns Ava and Cora, but not Ben. I keep that three-customer case in a regression test because a two-customer example with only matched and wholly unmatched rows misses the problem. The SQL expresses which orders qualify, not merely whether any order exists.
Search Stored Modules for Candidates
Look for modules that contain LEFT JOIN and WHERE. This text search is only a shortlist: it cannot parse SQL grammar or tell whether a right-table filter is intentional. Dynamic SQL and client-generated queries can be absent. Review each candidate's aliases and predicates manually, then test expected unmatched rows.
SELECT OBJECT_SCHEMA_NAME(object_id) AS schema_name,
OBJECT_NAME(object_id) AS module_name
FROM sys.sql_modules
WHERE definition LIKE '%LEFT%JOIN%'
AND definition LIKE '%WHERE%';The search can return false positives from comments, strings, or unrelated WHERE clauses. It can miss encrypted modules and differently formatted code. I use it to prioritize review, not to auto-rewrite every occurrence. Query Store or application traces can show which candidate queries actually run and what business output they feed.
Check Aggregates and Duplicate Rows
Moving a condition into ON can increase returned parent rows and change aggregates. A COUNT(*) over the corrected query counts each preserved parent row, including one with no matching child. COUNT(o.OrderID) counts only matched orders. If the report asks how many shipped orders each customer has, use the latter and group by the customer key.
SELECT c.CustomerID,COUNT(o.OrderID) AS shipped_orders
FROM #Customer AS c
LEFT JOIN #Orders AS o
ON o.CustomerID = c.CustomerID AND o.Status = 'Shipped'
GROUP BY c.CustomerID;A customer with several shipped orders will appear in several joined rows before aggregation. If the report needs one row per customer, aggregate or use EXISTS as appropriate. I inspect both unmatched rows and multiplicity after moving the predicate; preserving rows can reveal a second query-shape issue.
Decide Whether You Need a LEFT JOIN
Right-table predicates can hide inside expressions. WHERE COALESCE(o.Status, 'Pending') = 'Shipped' still rejects the NULL-extended row, while a predicate that explicitly accepts NULL can preserve it. The reliable review is to evaluate the predicate against an unmatched row, not to search only for simple o.Status = text. Check filters in outer query layers and views too, since a later WHERE can undo an earlier LEFT JOIN.
The optimizer can rearrange joins and push predicates while preserving semantics. That is why the physical plan can look different from the written SQL. Keep a row-level regression test for qualifying, nonqualifying, and missing children; it is more durable than asserting one fixed graphical operator for every version.
Sometimes the WHERE filter is deliberate. If the result should contain only customers with shipped orders, write INNER JOIN to state that rule plainly. If all customers should appear, keep the outer join and put the qualifying order predicate in ON. Choose based on the product requirement and test all three cases: qualifying match, nonqualifying match, and no match.
I have seen dashboards lose customers silently after a new status filter was added to WHERE. The SQL compiled and the plan looked efficient, but the data contract changed. A three-row table and one expected-output list caught it immediately. Keep that example near the report query so future edits preserve its intent.
Should a customer with only a nonqualifying order remain in this result?
Related reading on this blog: Interesting Observation of ON Clause on LEFT JOIN: How ON Clause affects Resultset in LEFT JOIN and Differences Between Left Join and Left Outer Join.
A LEFT JOIN is not a guarantee of kept rows, it is a join whose rows a later WHERE can still remove.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
