The SQL Server ANSI_NULLS setting can change the outcome of comparisons to NULL in your database applications.
Consider the following table:Â
What do you think will be returned by the following query?:
SELECT * FROM Employees WHERE MiddleName = NULL
Here’s the result:
Nothing at all!
By default, anything compared to NULL yields the logic value of *Unknown*
SQL Server uses three valued logic: True, False, and *Unknown*. Only rows that evaluate to True are returned from a query.
Couple this with another factoid about SQL Server: Any comparison to NULL yields a logical result of *Unknown*. This is valid even when comparing NULL to NULL!
In our example, we’re asking for MiddleName values that equal NULL. Anything we compare to this NULL will yield a result of *Unknown*, and since only True rows get returned from a query, this means that no rows at all will get returned from our query!
Disabling the ANSI_NULLS setting changes the logical result of a comparison of NULL to NULL
Understand that the ANSI_NULLS setting is what tells SQL Server to yield a result of *Unknown* when comparing NULL to NULL.
When this setting is disabled, instead of yielding a result of *Unknown* for this type of comparison, the logical result will be True.
Here’s an example:
Suddenly, those NULL names are returned! When ANSI_NULLS is disabled, a comparison of NULL to NULL yields a logical value of True, meaning rows are returned!
Next Steps:
Leave a comment if you found this tutorial helpful!
NULL can be a tricky thing to understand in SQL Server, but it’s very important to understand how it works. Check out the full beginner-friendly tutorial to learn more:
SQL Server NULL: Are you making these 7 mistakes?
Thank you very much for reading!
Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!
If you have any questions, or if you are struggling with a different topic related to SQL Server, I’d be happy to discuss it. Leave a comment or visit my contact page and send me an email!
