0

I've had an unexpected outcome in a query that points to a subtlety that I'm probably not fully understanding.

I have a query like:

SELECT a.This, a.That, a.Another FROM tableA a WHERE a.Other <> 'Y'

a.Other is intended to operate as a Y/N flag column, but it also allows NULL values, which is way beyond my control (large commercial product).

I would expect that snippet to return all 'N' and NULL rows, but it is only returning rows with 'N'. If that column were set to NOT NULL, that wouldn't be an issue, but alas.

I'm getting the results desired by switching up that WHERE to

WHERE (a.Other IS NULL OR a.Other = 'N')

but why is the <> 'Y' excluding the NULL values? I've also phrased it using NOT IN ('Y'), and I still only get the 'N' values.

Can someone explain the reason behind that?

Data is stored on: Microsoft SQL Server 2017 (RTM-CU27) (KB5006944) - 14.0.3421.10 (X64)

Annie Voss
  • 21
  • 6
  • 2
    `NULL` can't be compared to anything else, not even another `NULL`. `NULL = NULL` returns `UNKNOWN`, which is not `TRUE` and hence in most circumstances treated as `FALSE`. – HoneyBadger Nov 10 '21 at 14:13
  • Does this answer your question? [Comparing a value to a NULL in t-SQL](https://stackoverflow.com/questions/11662651/comparing-a-value-to-a-null-in-t-sql) And for a solution with `coalesce`: https://stackoverflow.com/questions/50917152/sql-server-compare-to-null – Ocaso Protal Nov 10 '21 at 14:14
  • 1
    If you were told to find all people whose age is greater than 18 you'd use `age > 18`. Then you were told to find people 18 or less you would use `NOT (age > 18)`. In which group would SQL Sever put the people with NULL age? First or Second? And why? – Salman A Nov 10 '21 at 14:18
  • The behaviour is explicitly documented, both for [`<>`](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/not-equal-to-transact-sql-traditional?view=sql-server-ver15) and, in a **big red box**, for [`NOT IN`](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/in-transact-sql?view=sql-server-ver15#result-value) – Thom A Nov 10 '21 at 14:19
  • @OcasoProtal Yes, it does, thanks. – Annie Voss Nov 10 '21 at 14:48

0 Answers0