A week ago I wrote about six queries that run fine until they don't. Cursed SQL. That one struck a nerve, so here's the sequel. Same idea, but this time NULL is the one villain behind every example, and the three-valued logic that rides along with it.
These queries are worse in a quieter way. They don't run slow. They never throw an error. They just give you the wrong answer every time, and SQL Server never says a word. That is the curse.
First, the One Rule
SQL Server does not deal in just true and false. It uses a three-valued logic system, or '3VL', in which conditions must evaluate to TRUE, FALSE, or UNKNOWN. That third one comes to the table whenever NULL is involved in a comparison because NULL is not a value. NULL is not zero and it does not equal anything -- not even another NULL. Any direct comparison to NULL always evaluates to UNKNOWN. Never TRUE or FALSE. Just UNKNOWN.
That single rule is behind all six curses below. Keep it in your pocket and you'll start to see them coming.
The Setup
An Employee table with a few NULLs planted exactly where real data goes missing. A CEO with no manager, an employee not yet assigned a department, and an employee missing their salary. Simple omissions that happen all the time.
CREATE TABLE dbo.Employee
(
EmployeeID INT IDENTITY(1,1) PRIMARY KEY,
FullName NVARCHAR(100) NOT NULL,
ManagerID INT NULL, -- the CEO reports to no one
Department NVARCHAR(50) NULL, -- some not yet assigned
Salary DECIMAL(10,2) NULL -- some withheld
);
INSERT dbo.Employee (FullName, ManagerID, Department, Salary)
VALUES (N'Dana Reyes', NULL, N'Executive', 250000), -- CEO, no manager
(N'Sam Carter', 1, N'Sales', 90000),
(N'Priya Nair', 1, N'Sales', NULL), -- salary withheld
(N'Tom Becker', 1, NULL, 75000), -- department not assigned
(N'Lena Frost', 2, N'Sales', 82000);
See our data with the NULLs:
Curse 1: NOT IN Hits a NULL and Returns Nothing
You're reporting for HR and you want to know which Departments have nobody assigned yet. So you pass the department values into your query and ask the system which ones don't exist in the Employee table.
-- Which departments have nobody assigned? (ie., the empty ones) SELECT DeptName FROM (VALUES (N'Executive'), (N'Sales'), (N'Engineering')) AS d (DeptName) WHERE DeptName NOT IN (SELECT Department FROM dbo.Employee);
Dana is in Executive, three people are in Sales, and nobody has been assigned to the Engineering department yet. So that's the one you expect in the return, but you get this instead:
Nothing. Why isn't Engineering returned?
Here's the problem, step by step. First, we must recognize that the subquery actually returns every Department value in the table:
One of those values is NULL because Tom has not been assigned a department yet, and that NULL is the entire problem. Here's how NOT IN works: it takes a row's DeptName and compares it to every value in that list with 'not equal' (<>), joining the results with AND. So this:
DeptName NOT IN ('Executive', 'Sales', NULL)
Actually translates to this:
DeptName <> 'Executive' AND DeptName <> 'Sales' AND DeptName <> NULL
A result to that query only survives if its DeptName is not equal to all of them.
Now walk the Engineering row through it -- the one you actually expected back. For that row, DeptName is 'Engineering'. It is not equal to Executive or Sales, so both comparisons pass... but then it hits the NULL:
'Engineering' <> 'Executive' -> TRUE 'Engineering' <> 'Sales' -> TRUE 'Engineering' <> NULL -> UNKNOWN (any compare to NULL is UNKNOWN) TRUE AND TRUE AND UNKNOWN -> UNKNOWN
That's what gets you. Engineering genuinely has no employees, so without that NULL in the list, it would have been returned. But remember, TRUE + TRUE + UNKNOWN = UNKNOWN. Not TRUE. A row will only be returned when its WHOLE chain evaluates to TRUE, and that UNKNOWN is why your result came back empty.
Microsoft says so right in the docs: "Any null values returned by subquery or expression that are compared to test_expression using IN or NOT IN return UNKNOWN."
The fix is to stop using equality for this. NOT EXISTS tests for the existence of a matching row, not equality against a list, so a stray NULL doesn't poison it.
-- Fix 1: NOT EXISTS is immune to the NULL trap
SELECT d.DeptName
FROM (VALUES (N'Executive'), (N'Sales'), (N'Engineering')) AS d (DeptName)
WHERE NOT EXISTS (SELECT 1
FROM dbo.Employee e
WHERE e.Department = d.DeptName);
-- Fix 2: keep NOT IN, but strip the NULLs out of the subquery
SELECT DeptName
FROM (VALUES (N'Executive'), (N'Sales'), (N'Engineering')) AS d (DeptName)
WHERE DeptName NOT IN (SELECT Department
FROM dbo.Employee
WHERE Department IS NOT NULL);
Both return Engineering. Make NOT EXISTS your habit. It sidesteps this entirely, and often gets you better performance as a bonus.
Curse 2: The Inequality That Silently Drops the Row You Wanted
You want everyone who is not in Sales. Reasonable.
SELECT FullName, Department FROM dbo.Employee WHERE Department <> N'Sales';
You expected Dana in Executive and Tom, whose department is not assigned yet, but you get only Dana. Tom vanishes, because NULL <> 'Sales' is UNKNOWN, not true, and his row never qualifies. The employee with no department -- arguably the exact person you were hunting for -- silently falls out of the 'not in Sales' report. The NULL rows fall through the cracks and your data set is incomplete.
Use this method to account for any NULL values, and Tom comes back:
SELECT FullName, Department FROM dbo.Employee WHERE Department <> N'Sales' OR Department IS NULL;
Curse 3: The Average That Quietly Skips a Person
This one hands your boss a wrong number that looks completely right. You want the team's average salary -- one figure for a comp review.
SELECT AVG(Salary) AS AvgSalary,
COUNT(*) AS HeadCount,
COUNT(Salary) AS PaidCount
FROM dbo.Employee;
Five people on the team, but watch the counts. COUNT(*) returns 5 -- every row. COUNT(Salary) returns 4 -- only the rows with a salary. Priya's is NULL, and AVG skipped her, because aggregates ignore NULL instead of treating it as zero. So your 'average' added up four salaries and divided by four, not five. The figure looks fine, but it quietly left a person out.
SQL Server does warn you, exactly once, in a message most tools tuck out of sight:
Warning: Null value is eliminated by an aggregate or other SET operation.
That warning is the entire story. A row was removed from your math and the engine mentioned it on the way out. The point is not that one number is right and the other wrong. The point is that the default decided for you. If a withheld salary should count as zero, say so:
SELECT AVG(ISNULL(Salary, 0)) AS AvgSalary_NullAsZero FROM dbo.Employee;
Same data, divided by five this time, on purpose. Different answer, and now it's mathematically correct.
Curse 4: One NULL Poisons the Whole String
You want a tidy label for each employee.
SELECT FullName + ' - ' + Department AS Label FROM dbo.Employee;
Four rows look fine, but what happened to Tom? He came back as NULL. Curious why it didn't come back as 'Tom Becker - ' ? The entire string collapsed to NULL. This happens with the + operator. If any piece is NULL, the whole result becomes NULL. That is CONCAT_NULL_YIELDS_NULL doing its job, and since SQL Server 2017 it is always ON -- you can't turn it off anymore. So a single missing department wipes out the entire label.
The fix is CONCAT, which treats NULL as an empty string instead of a contaminant. Or guard each piece yourself if you'd rather show a placeholder.
-- CONCAT() ignores NULLs, treating them as empty strings SELECT CONCAT(FullName, ' - ', Department) AS Label FROM dbo.Employee;
Or you can use a default to show a placeholder:
SELECT FullName + ' - ' + ISNULL(Department, N'(unassigned)') AS Label FROM dbo.Employee;
CONCAT gives you 'Tom Becker - ' with an empty tail; the ISNULL version gives you 'Tom Becker - (unassigned)'. CONCAT arrived in SQL Server 2012, so it's most likely a non-issue on anything you're running today.
Curse 5: = NULL Is Not IS NULL
You want to list all employees with no department assigned.
SELECT FullName FROM dbo.Employee WHERE Department = NULL;
Zero rows. But what about Tom? Remember, Department = NULL means UNKNOWN for every row, including the ones that actually are NULL, because nothing equals NULL. Not even NULL equals NULL. The right tool is the predicate built for the job:
SELECT FullName FROM dbo.Employee WHERE Department IS NULL;
Now Tom comes back. Years ago, with ANSI_NULLS OFF, = NULL would behave like IS NULL, but that setting is deprecated and ANSI_NULLS has been forced ON since SQL Server 2017. Don't lean on it. Use IS NULL and trust your results.
Curse 6: The CHECK Constraint That Waves NULL Right Through
This is the sneaky one, and most people don't know it. You add a guard rail to keep salaries positive.
ALTER TABLE dbo.Employee ADD CONSTRAINT chk_salary_positive CHECK (Salary > 0);
It creates without question or error -- even though Priya already sits in the table with a NULLable salary. That should be your first clue. Now insert a brand new row with no salary at all:
INSERT dbo.Employee (FullName, Salary) VALUES (N'Ghost Worker', NULL);
It goes right in. The constraint you wrote to guarantee a positive salary just accepted a missing one. Here is why: a CHECK constraint rejects a row only when the condition evaluates to false. NULL > 0 is UNKNOWN, not false, so the row is allowed. CHECK treats true and UNKNOWN as the same verdict -- both pass. Your 'salary must be positive' rule quietly permits 'salary is missing entirely'.
The fix is to say what you actually mean. If a salary is required, the column belongs NOT NULL (clean the data first, then alter it). If NULL is genuinely allowed but any real value must be positive, spell that out in the constraint itself:
-- clear the ghost worker record
DELETE dbo.Employee WHERE FullName = N'Ghost Worker';
-- drop constraint
IF OBJECT_ID('chk_salary_positive') IS NOT NULL
ALTER TABLE dbo.Employee DROP CONSTRAINT chk_salary_positive;
-- clean up the data with null salary
DELETE dbo.Employee WHERE Salary IS NULL; -- this clears Priya too
-- make column not null
ALTER TABLE dbo.Employee ALTER COLUMN Salary DECIMAL(10,2) NOT NULL;
-- above prevents NULL, this enforces that all values are positive
ALTER TABLE dbo.Employee
ADD CONSTRAINT chk_salary_positive CHECK (salary > 0);
-- Now try that insert again
INSERT dbo.Employee (FullName, Salary) VALUES (N'Ghost Worker', 00000.00)
The lesson holds for every CHECK you write. The constraint does not enforce what you assume. It enforces what evaluates to false. The corrected constraint now blocks zero and negatives, and NOT NULL is what handles the missing values.
In Summary
Six shapes, one root cause. NULL means 'I don't know', 3VL logic is how the engine handles it, and UNKNOWN is the value that falls through the cracks more often than not. None of these throw errors or run slow. They just produce the wrong answers with every call.
The fixes aren't exotic. Pretty simple, actually. Reach for NOT EXISTS instead of NOT IN. Account for NULL on both sides of an inequality. Decide what your aggregates should do with missing values instead of letting AVG decide for you. Use CONCAT over +. Write IS NULL, never = NULL, and remember that a CHECK constraint only ever blocks false. Get those right and the curse lifts. Now you've got six more shapes you can spot on sight.
More to Read
sqlfingers inc: Cursed SQL -- Six Queries That Run Fine Until They Don't
Microsoft Learn: IN (Transact-SQL)
Microsoft Learn: SET ANSI_NULLS (Transact-SQL)
Microsoft Learn: SET CONCAT_NULL_YIELDS_NULL (Transact-SQL)
Microsoft Learn: CONCAT (Transact-SQL)
