Null Issues and Fixes

Null Display

Most operations with SQL’s NULL can either fail or return unpredictable results. We analyzed them and tried to provide some (optional) fixes.

An empty cell should be the proper representation for Null, but this could get easily confused with an empty string value. We show instead a customizable grayed (Null) text, that could be completely hidden by the Hide Nulls toolbar button.

Compare with Null

You should  never compare something with NULL (like = NULL or <> NULL) using equality operators! It’s possible to choose such a test from the query builder, but it is immediately translated into a proper Is Null or Is Not Null operation. Just in case something goes through, on SQL query generation there is another line of defense with a similar check and eventual translation.

Null in Lists

For the vast majority of database systems, value IN (NULL, …) will simply ignore the NULL and return no match. We always warn at compile time about usage of NULL in lists of values. Remove NULL from Lists option will also automatically discard NULL from the dynamic values found for PIVOT queries and warn about it. Following image shows what equivalent translation would make more sense and return NULL matches as well:

Null Functions

Take quickly a different path of action when you encounter a Null, with following generic contextual functions:

• Is[Not]Null – shortcut functions for Is [Not] Null operators.
• IfNull(expression) – show expression instead on NULL context.
• NullIf(condition) – return NULL instead on condition.

Null Operations

Platforms like MySQL, MariaDB, Amazon Aurora and SQLite return NULL when you attempt a division by zero (like in 1 / 0). All other platforms throw an error instead. Eliminate this element of surprise by setting Fix Math Problems true, in which case we transparently add a NULLIF call to divide by NULL instead of zero (like in 1 / NULLIF(0, 0)), and always return NULL for this particular case. This query will never fail.

Fields involved in numeric or string operations may be NULL, in which case they may lead to erroneous results. Try to concatenate several fields together and, on most platforms, if at least one of them is NULL, the whole result is NULL. Fix Null Operations switch allows us to automatically append internal logic to convert NULLs into inoffensive values: 0 for additions and subtractions, 1 for multiplication and division, empty string for concatenation.

Last column in the following query performs a simple math operation using the other fields. With Fix Null Operations off, all highlighted cells would be Null, because of the Null in COMM (one Null spoils the whole result). With it on, COMM becomes 1 when NULL and used in multiplication:

Null Aggregations

Except for Count(*), NULLs are not considered by the aggregate functions. And this could be rightly so, because NULL is not a value (it rather means “no value”). However, this could be confusing and, if you want to treat NULLs as values in your aggregates (to be counted, in Count or Average), turn Fix Null Aggregates switch on.

Null Parameters

Comparisons with query parameters, such as in WHERE … = @param1, are error prone when @param1 is NULL (and all our custom parameters are Null by default!). We have the Fix Null Parameters switch to automatically and transparently fix this (additional code is added to the generated SQL, to consider the NULL scenario too).

Sorting with Nulls

Some database platforms append NULLS FIRST/LAST to their ORDER BY clauses. That’s because, by default, you never know when your NULLs will come in front or at the end in your sorted fields, it all depends on your platform. We generalized Nulls First/Last to all our supported platforms. When they are not supported already by the platform, or when the Always Emulate Nulls First/Last option is ON, we emulate them.