Null handling¶

Blue handles NULL values similar to other RDBMSs, with some minor differences.

Tip

When using Blue sql NULL values are displayed as \N. Different clients may show other values, including an empty string.

Comparisons¶

Any comparison between a NULL and a value will result in NULL.

For example, writing WHERE a = NULL will never match to TRUE or FALSE, because the comparison results in NULL. Use IS NULL to check for NULL values in result sets.

Conditionals¶

Functions like ISNULL and COALESCE evaluate arguments in their given order, so they may never evaluate some arguments.

For example, COALESCE(a,b,c,d,NULL) will never evalulate b, c, d, or NULL if a is not NULL.

Operations¶

Scalar functions¶

With all scalar functions, a NULL input to any one of the arguments means the result is NULL.

t=> SELECT x, y, z, x+y as "x+y", x*y as "x*y", y*z as "y*z", z-y as "z-y"  FROM n;
x | y  | z  | x+y | x*y | y*z | z-y
--+----+----+-----+-----+-----+----
1 |  0 |  1 |   1 |   0 |   0 |   1
2 |  1 |  1 |   3 |   2 |   1 |   0
3 | \N |  4 |  \N |  \N |  \N |  \N
4 | \N | \N |  \N |  \N |  \N |  \N
5 |  6 | \N |  11 |  30 |  \N |  \N

Aggregates¶

With aggregates, NULL values are ignored, so they do not affect the result set.

t=> SELECT SUM(x) AS "sum(x)", SUM(y) AS "sum(y)", SUM(z) AS "sum(z)" FROM n;
sum(x) | sum(y) | sum(z)
-------+--------+-------
    15 |      7 |      6

t=> SELECT COUNT(x) AS "count(x)", COUNT(y) AS "count(y)", COUNT(z) AS "count(z)" FROM n;
count(x) | count(y) | count(z)
---------+----------+---------
       5 |        3 |        3

NULL values are not included in COUNT() of a column. COUNT(x) shows the full row-count because it’s a non-nullable column. COUNT(y) returns 3 - just the non-NULL values.
With MIN, MAX, and AVG - NULL values are completely ignored.

Distincts¶

NULL values are considered distinct, but only counted once.

t=> SELECT DISTINCT z FROM n;
z
--
 1
 4
\N

Running COUNT DISTINCT however, ignores the NULL values:

NULL values are considered distinct, but only counted once.

t=> SELECT COUNT(DISTINCT z) FROM n;
count
-----
    2

Sorting¶

When sorting a column containing NULL values, Blue sorts NULL values first with ASC and last with DESC.

Blue does not implement NULLS FIRST or NULLS LAST, so where NULL appears cannot change where NULL values appear in the sort order.

t=> SELECT * FROM n ORDER BY z ASC;
x | y  | z
--+----+---
4 | \N | \N
5 |  6 | \N
1 |  0 |  1
2 |  1 |  1
3 | \N |  4

t=> SELECT * FROM n ORDER BY z DESC;
x | y  | z
--+----+---
3 | \N |  4
1 |  0 |  1
2 |  1 |  1
4 | \N | \N
5 |  6 | \N