SQL Server and the (not so mysterious) Mystery of Three-Valued Logic

A concept that can often be overlooked in development is SQL Server three-valued logic.

In SQL Server, this is a concept that extends the traditional two-valued logic (True/False or 1/0) to include a third value, known as NULL. NULL represents the absence of a value or an unknown value. It is not equivalent to an empty string or zero, and it is not considered equal to any other value, including another NULL. Three-valued logic is used to handle cases where the value of a column in a database table is unknown or not applicable.

The inclusion of NULL as a third value in SQL Server’s logic can sometimes lead to unexpected results when querying the database. For example, consider the following query:

Source code

SELECT * FROM Customers WHERE Age < 25

If the ‘Age’ column in the ‘Customers’ table contains NULL values, the query would not return any rows with a NULL value, because NULL is not equal to any other value, including not being less than 25. To include rows with NULL values in the query, you would need to use the IS NULL or IS NOT NULL operators explicitly, like this:

Source code

SELECT * FROM Customers WHERE Age < 25 OR Age IS NULL

Similarly, when using logical operators such as ‘AND’ and ‘OR’ in SQL Server queries, the behaviour of three-valued logic can be different from what one might expect. For example, consider the following query:

Source code

SELECT * FROM Orders WHERE Status = 'Shipped' AND Amount > 1000

If the ‘Status’ column in the ‘Orders’ table contains NULL values, the query may not return expected results. This is because NULL values are unknown, and their comparison with other values using logical operators can result in unpredictable outcomes. To handle such cases, you may need to use additional conditions with IS NULL or IS NOT NULL operators to properly filter the results.

Changing Behaviour using ANSI_NULL OFF

All of the logic above is assuming you’re using the default ANSI_NULL = ON setting. On the other hand, when ANSI_NULLS is set to OFF, NULL values are considered equal to other NULL values, but not equal to non-NULL values. That’s an important consideration. This means that if you use the equality (=) or inequality (<>, !=) operators in a query and compare a column value with NULL, it will return true for rows with NULL values in that column.

For example, consider the same query as above, but with ANSI_NULLS set to OFF:

Source code

SET ANSI_NULLS OFF;
 
SELECT * FROM Customers WHERE FirstName = NULL

If ANSI_NULLS is set to OFF, this query will return rows where the FirstName column contains NULL values, because NULL is considered equal to NULL when ANSI_NULLS is set to OFF.

It’s important to note that ANSI_NULLS is a connection-level setting, so it applies to the current session or connection, and not to the entire SQL Server instance or database. This means that different sessions or connections can have different ANSI_NULLS settings, and it can potentially result in inconsistent behaviour when querying NULL values.

ANSI_NULL = ON vs ANSI_NULL = OFF

For an easy comparison, let’s take a look these examples where we have ANSI_NULLS switched ON (which is the default);

If you’re a developer you’ve probably had it drilled into your head to use the IS NULL and IS NOT NULL operators and this is why. Shall we make things even more fun?

Here’s the table above but with ANSI_NULLS OFF;

As you can see, quite a difference when you consider the implications.

While we’re on the subject, when was the last time you checked your ANSI NULL database default setting? I bet a lot of people who are reading this haven’t checked it in a long time, if at all. You’re probably on the default set to ON (most people are) but have you actually checked?

Source code

DECLARE @ANSI_NULLS VARCHAR(3) = 'OFF';
 
IF ( (32 & @@OPTIONS) = 32 ) SET @ANSI_NULLS = 'ON';
 
SELECT @ANSI_NULLS AS ANSI_NULLS;

It’s worth noting that a few features of SQL Server require the ANSI_NULL setting to be ON (distributed queries, creating indexes on computed columns, indexed views) so it is far more common to see this set to ON.

I hope this post about SQL Server three-valued logic has been useful. I also highly recommend that you take a few minutes to read the official documentation from Microsoft and really understand how this is likely to affect you and your environment.