T-SQL WHERE Clause FAQ: NULLs, Errors & Performance

The short answer to most WHERE clause confusion in T-SQL (Transact-SQL, Microsoft's SQL dialect for SQL Server) comes down to one thing: NULL doesn't behave like a normal value, and SQL Server's optimizer is far pickier about how you write predicates than most developers assume. Queries that "should" return rows silently return nothing, NOT IN filters quietly exclude everything, and perfectly reasonable-looking WHERE clauses turn a fast index seek (a targeted lookup using an index) into a full table scan (reading every row).

This article is written as a direct FAQ rather than a tutorial, because that's how most developers actually arrive here — they have a specific, broken query and a specific question, not a desire to read a WHERE clause primer from scratch. Each section below answers one recurring question, with a wrong version of the query, a corrected version, and an explanation of the mechanism behind the fix. The examples target current, supported versions of SQL Server; the syntax and behavior discussed here has been stable across SQL Server 2016 through SQL Server 2022, so none of this is version-fragile.

Why does WHERE column = NULL return no rows?

Because = is a comparison operator, and in SQL's three-valued logic, comparing anything to NULL — including another NULL — doesn't evaluate to TRUE or FALSE. It evaluates to UNKNOWN. A WHERE clause only keeps rows where the condition evaluates to TRUE, so any row producing UNKNOWN gets filtered out, exactly like a row producing FALSE.

-- Wrong: this returns zero rows even if ManagerID is genuinely NULL
SELECT EmployeeID, FirstName, ManagerID
FROM Employees
WHERE ManagerID = NULL;

-- Correct
SELECT EmployeeID, FirstName, ManagerID
FROM Employees
WHERE ManagerID IS NULL;

IS NULL and IS NOT NULL are not comparison operators — they're a dedicated syntax specifically designed to test for the absence of a value, and they always return TRUE or FALSE, never UNKNOWN. This is the single most common WHERE clause mistake developers make when moving from application code (where null == null is often true) into T-SQL.

What is three-valued logic, and why does it silently break NOT IN?

Three-valued logic means every WHERE clause predicate resolves to TRUE, FALSE, or UNKNOWN, rather than the two-valued TRUE/FALSE logic most programming languages use. It matters most with NOT IN, because a single NULL in the list turns the entire predicate UNKNOWN for every row, not just the row containing NULL.

-- Wrong: if CategoryID contains even one NULL, this returns zero rows,
-- even for products whose CategoryID clearly isn't 1, 2, or 3
SELECT ProductID, ProductName
FROM Products
WHERE CategoryID NOT IN (
    SELECT CategoryID FROM DiscontinuedCategories
);

-- Correct: filter out NULLs explicitly before negating
SELECT ProductID, ProductName
FROM Products
WHERE CategoryID NOT IN (
    SELECT CategoryID FROM DiscontinuedCategories
    WHERE CategoryID IS NOT NULL
);

Here's why this happens mechanically. NOT IN (1, 2, NULL) expands logically to <> 1 AND <> 2 AND <> NULL. The last comparison is UNKNOWN, and AND-ing anything with UNKNOWN can never produce TRUE — the best case is UNKNOWN, the worst is FALSE. Since the WHERE clause only keeps TRUE rows, the whole query silently returns nothing, with no error and no warning. This is arguably the most dangerous WHERE clause gotcha in T-SQL because it fails quietly rather than loudly. A safer default in most cases is NOT EXISTS, which handles NULLs correctly without requiring you to remember to filter them out:

SELECT ProductID, ProductName
FROM Products p
WHERE NOT EXISTS (
    SELECT 1 FROM DiscontinuedCategories d
    WHERE d.CategoryID = p.CategoryID
);

Does ANSI_NULLS setting change how WHERE clause NULL comparisons behave?

Yes, but not in a way most developers will ever need to touch deliberately. ANSI_NULLS is a session-level setting that controls whether = NULL and <> NULL follow the ANSI SQL standard (always UNKNOWN) or a legacy SQL Server behavior where = NULL behaves like IS NULL. When ANSI_NULLS is ON — the standard, current default in supported versions — column = NULL always evaluates to UNKNOWN, which is the behavior described above.

Setting ANSI_NULLS OFF is deprecated and Microsoft has signaled for a long time that this legacy behavior may eventually be removed entirely; new code should never rely on it. If you inherit an older codebase and see unexpected NULL-matching behavior with =, checking for an explicit SET ANSI_NULLS OFF somewhere in the connection or script is a reasonable troubleshooting step, but the fix should always be to rewrite the predicate with IS NULL rather than to lean on the legacy setting.

Why is my WHERE clause causing a table scan instead of an index seek?

This usually means your predicate is non-sargable. Sargable (a shorthand for "Search ARGument ABLE") describes a predicate the optimizer can resolve directly against an index — typically a column compared to a constant or variable with no function wrapped around the column. The moment you wrap an indexed column in a function, or apply arithmetic to it, SQL Server generally can't use an index seek and falls back to scanning every row.

-- Non-sargable: the function on the column forces a scan
SELECT OrderID, OrderDate, CustomerID
FROM Orders
WHERE YEAR(OrderDate) = 2024;

-- Sargable: same result, but the optimizer can seek on OrderDate directly
SELECT OrderID, OrderDate, CustomerID
FROM Orders
WHERE OrderDate >= '2024-01-01'
  AND OrderDate < '2025-01-01';

The rewritten version expresses the exact same logical filter but leaves OrderDate untouched, so if there's an index on OrderDate, the optimizer can seek directly to the relevant range instead of evaluating YEAR() against every row in the table. On a small table the difference is invisible. On a table with millions of rows, this single change can be the difference between a query that returns in milliseconds and one that takes seconds, because a scan reads every page of the table (or index) rather than jumping straight to the matching rows.

Other common non-sargable patterns worth recognizing: wrapping a column in ISNULL() or COALESCE(), applying string concatenation to a column, using LIKE '%value%' with a leading wildcard (which can't use a standard index seek at all), and comparing a column to an expression instead of a literal or parameter.

What causes "Conversion failed when converting the varchar value to data type int" in a WHERE clause?

This error means SQL Server tried to implicitly convert a string value into a numeric type to satisfy a comparison, and the string wasn't a valid number. Implicit conversion happens automatically when you compare columns or values of different data types, following SQL Server's data type precedence rules, and it's a common source of both errors and hidden performance problems.

-- Wrong: OrderID is int, but comparing it to a varchar column with mixed content
-- can throw a conversion error if any row's value isn't numeric
SELECT o.OrderID, o.OrderDate
FROM Orders o
JOIN LegacyOrderRefs r ON o.OrderID = r.OrderRefCode
WHERE r.OrderRefCode = 'ORD-12345';

-- Correct: convert safely instead of letting SQL Server throw on bad data
SELECT o.OrderID, o.OrderDate
FROM Orders o
JOIN LegacyOrderRefs r ON o.OrderID = TRY_CAST(r.OrderRefCode AS INT)
WHERE r.OrderRefCode = 'ORD-12345';

The underlying issue is a schema mismatch — an int column being joined or compared against a varchar column that sometimes holds non-numeric data. The real fix is usually at the schema level: standardize the column types so comparisons don't require conversion at all. When you can't change the schema immediately, be explicit with TRY_CAST() or TRY_CONVERT() so a bad value returns NULL instead of throwing an error, and be aware that implicit conversions on an indexed column can also silently defeat sargability, similar to wrapping the column in a function directly.

Why do I get "Ambiguous column name" errors in a WHERE clause with joins?

This happens when a column name exists in more than one table referenced by the query and you don't qualify it with a table name or alias. SQL Server has no way to know which table's column you mean.

-- Wrong: both Orders and Customers have a CreatedDate column
SELECT o.OrderID, c.CustomerName
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID
WHERE CreatedDate >= '2024-01-01';

-- Correct: qualify the column with its table alias
SELECT o.OrderID, c.CustomerName
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID
WHERE o.CreatedDate >= '2024-01-01';

The fix is always the same: prefix the column with the correct table alias. It's worth adopting a habit of aliasing every table in a multi-table query and qualifying every column reference in the WHERE clause, even when a column name currently happens to be unique — schemas change, and an unqualified reference that works today can break the moment a new column with the same name is added to another joined table.

Can I use an aggregate function like COUNT() or SUM() directly in a WHERE clause?

No — and if you try, SQL Server will raise an error like "An aggregate may not appear in the WHERE clause." The WHERE clause filters individual rows before any grouping happens, but aggregate functions only make sense once rows have been grouped. That's exactly why the HAVING clause exists: it filters groups after aggregation, while WHERE filters rows before it.

-- Wrong: COUNT() isn't valid in WHERE
SELECT CustomerID, COUNT(*) AS OrderCount
FROM Orders
WHERE COUNT(*) > 5
GROUP BY CustomerID;

-- Correct: aggregate filtering belongs in HAVING
SELECT CustomerID, COUNT(*) AS OrderCount
FROM Orders
GROUP BY CustomerID
HAVING COUNT(*) > 5;

A useful mental model: WHERE runs conceptually before GROUP BY, and HAVING runs after it. If your filter needs a SUM(), COUNT(), AVG(), MIN(), or MAX(), it belongs in HAVING. If it's filtering on a raw column value that exists on each individual row, it belongs in WHERE — and putting it there rather than in HAVING is also better for performance, since it reduces the row count before the (often more expensive) aggregation step runs.

Does the order of conditions in my WHERE clause affect performance?

Not directly, and this is a common misconception carried over from procedural programming. SQL Server's query optimizer builds an execution plan based on statistics, index structure, and estimated selectivity (how many rows a predicate is expected to filter out), not on the left-to-right order you typed your conditions. Writing WHERE Status = 'Active' AND CustomerID = 42 versus WHERE CustomerID = 42 AND Status = 'Active' produces the same plan in the vast majority of cases, because the optimizer reorders predicates based on cost, not source order.

What does matter is whether each individual predicate is sargable and whether it aligns with your indexes. A query with ten well-written, sargable conditions in "the wrong order" will still outperform a query with two non-sargable conditions in "the right order." If you want to actually confirm what the optimizer chose, look at the estimated or actual execution plan (viewable in SQL Server Management Studio or Azure Data Studio) rather than reasoning about condition order from the query text.

Why does adding a WHERE clause condition make my query slower, not faster?

This is usually a sign of one of two things: an out-of-date statistics problem, or parameter sniffing (where SQL Server caches an execution plan built for the first parameter value it saw, and reuses that plan even when a wildly different value comes through later, producing a plan that's badly suited to the new value). Adding a condition on a column with skewed data distribution — say, a Status column where 95% of rows are 'Completed' and 5% are 'Pending' — can trigger very different plans depending on which value was used the first time the query was compiled.

-- This proc's plan may be optimized for whichever @Status value first
-- caused it to compile, and reused inappropriately for other values
CREATE PROCEDURE dbo.GetOrdersByStatus
    @Status VARCHAR(20)
AS
BEGIN
    SELECT OrderID, OrderDate, CustomerID
    FROM Orders
    WHERE Status = @Status;
END;

A common mitigation is OPTION (RECOMPILE) on the specific statement, which forces a fresh plan based on the actual parameter value each execution — at the cost of extra compilation overhead on every run. Another approach is OPTION (OPTIMIZE FOR UNKNOWN), which asks the optimizer to build a plan based on average statistical distribution rather than the specific first parameter value. Neither is a universal fix; which one helps depends on your data distribution and how often the query runs, and it's worth testing both against your actual execution plans rather than assuming one is always better.

Best Practices for Writing WHERE Clauses

A few habits consistently prevent the problems above rather than requiring you to debug them after the fact.

Always use IS NULL / IS NOT NULL for NULL checks, never = or <>. Prefer NOT EXISTS over NOT IN whenever the list side of the comparison could ever contain NULL — and in practice, it's safer to default to NOT EXISTS for subqueries generally, since you won't always know in advance whether the underlying data is clean. Avoid wrapping indexed columns in functions or implicit conversions; rewrite range and pattern conditions to leave the column bare wherever possible.

Keep data types consistent across joined and compared columns so the optimizer never has to perform implicit conversions silently. Qualify every column reference with a table alias once more than one table is involved, even if the name looks safe today. And when a query's performance doesn't match your expectations, look at the actual execution plan rather than guessing — SQL Server will tell you directly whether it chose an index seek or a scan, and why.

Conclusion

Most confusing WHERE clause behavior in T-SQL traces back to one root cause: NULL doesn't participate in comparisons the way most developers assume, and that ripples into NOT IN, equality checks, and even join conditions if you're not deliberate about it. The second major theme is sargability — keeping columns bare and comparisons type-consistent so the optimizer can actually use your indexes instead of falling back to a scan. Working through this T-SQL WHERE clause FAQ should leave you able to diagnose the specific symptom you're hitting — silent empty results, a conversion error, or an unexpectedly slow query — rather than guessing at fixes. As a next step, open the actual execution plan for a query you're unsure about; seeing whether SQL Server chose a seek or a scan will teach you more about your specific schema and indexes than any general rule can.