NULLs are a frequently misunderstood concept within databases. This confusion often arises because NULL is mistakenly considered equivalent to empty or mathematically equal to zero, which is not the case in the context of a database. NULL signifies the absence of information, representing a state of being everything and nothing simultaneously. It’s essential to view NULL as encompassing every possible value, making it unequal to any specific value.
Consider a scenario where an individual’s gender is entered into a system. While there are acceptable values for gender (M, F, and X), allowing individuals not wishing to disclose their gender to have no information entered, rather than defaulting to an empty string, is crucial. In such cases, we insert a NULL. Identifying instances where NULLs exist becomes useful in recognizing individuals who haven’t provided information. The complexity of handling NULLs is evident even in ANSI standards, employing special logic and terminology like SET ANSI_NULLS or CONCAT_NULL_YIELDS_NULL.
NULL BASICS
Before delving deeper, let’s cover some fundamental information about NULLs:
- Normalization is the optimal way to mitigate the impact of nulls on a database, but even the best-designed databases inevitably have some nulls.
- A null value is not a default value; it’s the opposite, necessitating the use of DEFAULT syntax when inserting a NULL.
- NULLs occupy space in a table, even if filled with NULL values.
- SQL math functions disregard NULLs, as they are not counted.
SET ANSI_NULLS ON
Let’s look at some examples below.
In the first query we see that all rows including nulls are considered when we do a count (*) on the table. H
SET ANSI_NULLS ON
Let’s explore examples with ANSI_NULLS set to ON:
- The first query considers all rows, including nulls, when counting (*) on the table. Nulls have no effect as other rows have values.
- Using IS NULL syntax in the second query checks for NULLs, working well due to ANSI_NULLS ON.
- The third query, using IS NOT NULL syntax, works due to the ANSI_NULLS ON condition.
- The fourth query, using = NULL syntax, treats NULL like a value and fails as it’s not ANSI compliant.
- The fifth query uses a column with NULLs, demonstrating that NULLs are ignored implicitly.
When NULLs are ignored implicitly, the database engine issues a warning in the query output.
The text below shows the expected error message when NULLs are detected by the SUM function:
Warning: Null value is eliminated by an aggregate or other SET operation.
The optimizer’s interesting aspect is noticeable when explicitly expecting values that are not NULL; in this case, there is no warning.
SET ANSI_NULLS OFF
Turning ANSI_NULLS OFF and rerunning the queries shows that the fourth query no longer returns 0. With ANSI_NULLS OFF, the equal to operator checks for NULL like any other value.
We now set ANSI_NULLS OFF and run the same queries as before. Notice that the fourth query no longer returns 0 , the rest of the queries behave the same as usual. Basically with ASNI_NULLS OFF the equal to operator performs a check for NULL like it would for any other value.
Impact on Logic: ISNULL, NULLIF
Queries testing the NULL logic handling in functions like ISNULL and NULLIF reveal their impact on the execution plan. The presence of the Compute Scalar operator indicates that NULLs are treated as NULLs during data access, replaced by 0 only during presentation.
The placement of these functions plays a critical role in how the query handles the output, as shown in the following example:
- The first query handles NULLs during grouping, converting them and resulting in a total value of 4.19.
- The second query groups data by the actual value of NULL and 1, followed by NULLIF logic. The change takes effect during output presentation, resulting in two rows with NULLs.
Where we use the function plays a critical role in how the query handles the output. This is visible in the query below: –
This behavior remains consistent regardless of SET options ANSI_NULLS ON or OFF.