.svg)
.svg)
.svg)
.svg)
.svg)
.svg)
.svg)
Updated and tested 12 October 2026
AND binds tighter than OR inside a search condition, and that one rule of precedence, combined with a missing pair of brackets, is enough to turn a weekly report that should count 1,204 rows into one that counts 8,930, with the engine running both of them and complaining about neither. This is where SQL Server stops protecting you. Almost every mistake in an MSSQL WHERE clause produces a result set instead of a message, and a wrong result set looks exactly like a right one.
These examples use the same schema as the other T-SQL tutorials on this site. dbo.orders holds about 2.4 million rows with order_id, customer_id, order_status, order_total, discount_pct (nullable) and placed_at as a datetime2(3). dbo.customers carries customer_id, email, company_name and country_code, and dbo.order_items holds the lines.
MSSQL WHERE with AND and OR: operator precedence
Here's the query that produced 8,930:
SELECT COUNT(*)
FROM dbo.orders
WHERE order_status = 'pending'
OR order_status = 'disputed'
AND placed_at >= '20260701';-----------
8930The indentation says the date filter applies to both statuses. The parser disagrees. AND has higher precedence than OR, so the engine reads it as:
WHERE order_status = 'pending'
OR (order_status = 'disputed' AND placed_at >= '20260701');Every pending order in the table qualifies, back to 2019. Add the brackets you meant:
SELECT COUNT(*)
FROM dbo.orders
WHERE (order_status = 'pending' OR order_status = 'disputed')
AND placed_at >= '20260701';-----------
1204Precedence in a search condition runs comparison operators first, then NOT, then AND, then OR. That is the whole of the MSSQL WHERE OR problem in one line: AND binds first and your indentation gets no vote. Knowing the order matters less than the habit, so bracket every OR group even when precedence already does what you want. The brackets cost nothing and they survive the next person who edits the clause without reading it carefully.
Two statuses is also a case where IN reads better and removes the ambiguity entirely:
WHERE order_status IN ('pending', 'disputed')
AND placed_at >= '20260701';Not equal MSSQL operators: <> versus !=
Both work and they do the same thing. Microsoft's comparison operators reference labels != as "Not equal to (not ISO standard)", along with !< and !>, which are the two nobody uses. <> is the standard form and it's what I write, mostly so the same query copies into another engine without editing.
NULL is what makes any negation awkward. discount_pct is nullable, and this excludes more rows than you'd expect:
SELECT COUNT(*)
FROM dbo.orders
WHERE discount_pct <> 10;Rows where discount_pct is NULL are not returned. NULL isn't 10, so intuition says they should be, but NULL <> 10 evaluates to UNKNOWN and only rows evaluating to TRUE come back. Spell it out when you want them:
WHERE (discount_pct <> 10 OR discount_pct IS NULL)This catches people on every negation. If you find yourself writing that OR over and over, the fix is upstream. Declare the column NOT NULL with a sensible default, which is what ISNULL, COALESCE and CASE argues for, and handle the absence somewhere else. It is the same reason a LEFT JOIN with a filter on the right table's column collapses into an inner join.
MSSQL IN with a list and with a subquery
The list form is a shorthand for a chain of ORs:
SELECT order_id, order_status
FROM dbo.orders
WHERE order_status IN ('pending', 'disputed', 'flagged');The list takes literals, variables or a mix of the two.
The subquery form is the one to look at:
SELECT c.customer_id, c.email
FROM dbo.customers AS c
WHERE c.customer_id IN (
SELECT o.customer_id
FROM dbo.orders AS o
WHERE o.order_total > 500.00
);The subquery returns one column, duplicates in it are irrelevant, and the optimizer usually turns this into a semi join, so the inner query doesn't run once per row. IN and EXISTS perform closely enough here that I pick on readability. There is one exception and it is next.
NOT IN with a NULL returns nothing at all
An imported suppression list, with one blank line in the source file:
CREATE TABLE dbo.marketing_optout (
email VARCHAR(255) NULL
);
INSERT INTO dbo.marketing_optout (email)
VALUES ('[email protected]'), ('[email protected]'), (NULL);Then the mailing list query:
SELECT c.email
FROM dbo.customers AS c
WHERE c.email NOT IN (SELECT m.email FROM dbo.marketing_optout AS m);(0 rows affected)Zero rows, from a table with 40,000 customers in it, where you expected everyone except the two opted-out addresses. The expansion explains it. x NOT IN (a, b, NULL) means x <> a AND x <> b AND x <> NULL, and that last comparison is UNKNOWN for every row, UNKNOWN ANDed with TRUE is UNKNOWN, and UNKNOWN rows don't come back. Microsoft documents the behaviour on the IN reference. One NULL anywhere in the subquery result silently empties the whole thing.
The fix is NOT EXISTS, which handles NULLs the way you meant:
SELECT c.email
FROM dbo.customers AS c
WHERE NOT EXISTS (
SELECT 1
FROM dbo.marketing_optout AS m
WHERE m.email = c.email
);NOT EXISTS asks if any row matched at all, and a NULL simply fails to match and leaves the result alone. The same pattern is what makes a load re-runnable, which is why INSERT INTO and idempotent inserts leans on it too. NOT IN only against a column declared NOT NULL. NOT EXISTS everywhere else.
Error 8623 and 8632 with enormous IN lists
Paste 30,000 SKUs into an IN list and the parser gives up before the optimizer starts:
Msg 8623, Level 16, State 1
The query processor ran out of internal resources and could not produce a query plan.
This is a rare event and only expected for extremely complex queries or queries that
reference a very large number of tables or partitions.Or its cousin, Msg 8632, "Internal error: An expression services limit has been reached." There's no fixed row count where this starts, since it depends on the complexity of the whole statement, and the documented workaround is the same either way: put the values in a table and use a subquery or a join.
CREATE TABLE #sku_filter (sku VARCHAR(40) NOT NULL PRIMARY KEY);
INSERT INTO #sku_filter (sku)
SELECT v.sku FROM (VALUES ('LD-RAM-32'), ('LD-NVME-1T')) AS v(sku);
SELECT oi.order_id, oi.sku, oi.quantity
FROM dbo.order_items AS oi
JOIN #sku_filter AS f ON f.sku = oi.sku;Better plans too, because the optimizer gets statistics on a table and gets nothing useful out of a wall of literals. Past a few hundred values I go straight to a temp table without waiting for the error.
MSSQL BETWEEN and the datetime trap
BETWEEN is defined as a closed range. Microsoft's words: it "returns TRUE if the value of test_expression is greater than or equal to the value of begin_expression and less than or equal to the value of end_expression". For integers that's exactly what people expect. For dates it drops the rows you meant to keep.
SELECT COUNT(*)
FROM dbo.orders
WHERE placed_at BETWEEN '20240101' AND '20240131';-----------
6133Now the version that's correct:
DECLARE @start DATE = '20240101';
DECLARE @end DATE = '20240131';
SELECT COUNT(*)
FROM dbo.orders
WHERE placed_at >= @start
AND placed_at < DATEADD(day, 1, @end);-----------
6341208 orders missing from the first query, every one of them placed on 31 January. placed_at is a datetime2(3), and the literal '20240131' carries no time part, so it becomes 2024-01-31 00:00:00.000. The docs say it plainly: "When the time part is unspecified, it defaults to 12:00 A.M. A row that contains a time part that is after 12:00 A.M. on January 5, 2002, isn't returned by this query." Everything after midnight on the last day of your range falls outside it.
The half-open pattern above, greater than or equal to the start and strictly less than the day after the end, is the one to use, and it carries on working when somebody widens the column to datetime2(7) or narrows it to date, which is more than can be said for a hand-written upper bound. Don't reach for <= '2024-01-31 23:59:59.997'. That value is the last representable tick of the old datetime type, and against a datetime2(7) column it drops anything in the final three milliseconds of the day. Fixing a data-loss bug with a smaller data-loss bug.
Two habits go with this, and both get more room in SQL Server date functions. Write date literals as 'YYYYMMDD' with no separators, since that form is interpreted the same way under every language and DATEFORMAT setting, unlike '01/02/2024'. Keep DATEADD on the parameter and off the column, for reasons the sargability section gets to. Microsoft's date and time reference lists what each type can hold.
DATEFORMAT is a session setting, which means two people can run the same saved query against the same server and read different numbers off it, depending on the default language attached to their login. I lost most of a fortnight to that once, in a shop where half the logins defaulted to British and the other half to us_english. It has nothing to do with BETWEEN.
LIKE and its four wildcards
Four wildcards, and most people know two of them. % matches any string including the empty one, _ matches exactly one character, [abc] matches one character from a set or a range and [^abc] matches one character not in it.
WHERE company_name LIKE 'Example%' -- prefix match
WHERE sku LIKE 'LD-___-32' -- three characters in the middle
WHERE country_code LIKE '[DF]E' -- DE or FEThe prefix match can use an index on company_name. The engine seeks to the first entry starting with "Example" and reads forward until the prefix stops matching. Put the wildcard at the front and that ability goes:
WHERE company_name LIKE '%Ltd'An index is sorted by leading characters, so a pattern with no fixed leading characters gives the engine nothing to seek to and it reads every row and tests each one. On 40,000 customers nobody notices. On a few million rows it's the difference between milliseconds and seconds. Full-text indexing and a persisted computed column holding REVERSE(company_name) with its own index both give suffix matching something seekable, and on a table small enough that nobody complains the scan is a perfectly reasonable thing to live with. When the literal you're matching contains a % or an _, escape it: LIKE '100!% off' ESCAPE '!'.
IS NULL versus = NULL
NULL marks the absence of a value, so it isn't one, and no comparison operator can test for it:
WHERE discount_pct = NULL -- returns no rows, ever
WHERE discount_pct IS NULL -- returns the rows you wantedWith SET ANSI_NULLS ON, both expr = NULL and expr <> NULL evaluate to False regardless of the data. That used to be switchable, and old code sometimes relies on the OFF behaviour where = NULL worked like IS NULL. Microsoft's deprecation notice closes the door: "SET ANSI_NULLS OFF and the ANSI_NULLS OFF database option are deprecated. Starting with SQL Server 2017 (14.x), ANSI_NULLS is always set to ON." The same documentation page still carries a legacy line saying the default for SET ANSI_NULLS is OFF, in the paragraph that explains the ODBC driver and OLE DB provider switch it ON when they connect, and that line no longer describes what you'll see on a modern connection. Treat ANSI_NULLS as permanently ON and write IS NULL.
WHERE versus HAVING in MSSQL
They filter at different points, and the logical processing order is what makes the difference make sense: FROM and its joins, then WHERE, then GROUP BY, then HAVING, then SELECT, then ORDER BY. WHERE filters individual rows before anything is grouped. HAVING filters groups after the aggregates have been computed.
I had to check that order while writing this, because I have always half-believed GROUP BY runs before WHERE.
SELECT o.customer_id,
COUNT(*) AS order_count,
SUM(o.order_total) AS lifetime_value
FROM dbo.orders AS o
WHERE o.order_status <> 'cancelled'
GROUP BY o.customer_id
HAVING COUNT(*) >= 5
AND SUM(o.order_total) > 2000.00
ORDER BY lifetime_value DESC;Both filters are doing necessary work. The WHERE drops cancelled orders before they contribute to any total, which is a per-row decision, and the HAVING drops customers whose totals came out too small, which can only be decided once the group exists. Move COUNT(*) >= 5 into the WHERE clause and you get:
Msg 147, Level 15, State 1
An aggregate may not appear in the WHERE clause unless it is in a subquery contained
in a HAVING clause or a select list, and the column being aggregated is an outer reference.Note also that lifetime_value works in ORDER BY and fails in HAVING with "Invalid column name". Aliases from the SELECT list aren't available to WHERE or HAVING, since both run before SELECT does. Repeat the expression, or wrap the whole query in a CTE or derived table and filter on the alias outside.
The performance rule follows the same order. Every predicate that doesn't involve an aggregate belongs in WHERE, because rows removed there never get grouped, sorted or aggregated. A HAVING clause holding a plain column comparison works fine, and it makes the server do the grouping first for no reason.
