SingleStore Managed Service

NULL Handling

Unless otherwise noted, NULL handling is as defined in the SQL standard; the result of a comparison with NULL is NULL, which, when used to qualify rows, is treated as false.

IS NULL, IS NOT NULL

IS NULL and IS NOT NULL are postfix operators; the argument appears on the left. They return true if the argument is NULL or NOT NULL, respectively. For example:

CREATE TABLE s(b BOOLEAN, t TEXT);
INSERT s VALUES(true, "blue"), (false, "red"), (true, NULL);
SELECT * FROM s WHERE t IS NULL;
****
+------+------+
| b    | t    |
+------+------+
|    1 | NULL |
+------+------+
SELECT 'x' IS NULL, NULL IS NULL, NULL IS NOT NULL;
****
+-------------+--------------+------------------+
| 'x' IS NULL | NULL IS NULL | NULL IS NOT NULL |
+-------------+--------------+------------------+
|           0 |            1 |                0 |
+-------------+--------------+------------------+
ISNULL()

The ISNULL() function behaves the same as IS NULL except it uses the standard function syntax where the single argument appears on the right, e.g. ISNULL(expr) returns true if expr is NULL and false otherwise.

NULL-safe equal operator: <=>

The NULL-safe equal operator, <=> returns true if both arguments are NULL. Otherwise, it is the same as the = operator. For example:

SELECT * FROM s AS s1, s AS s2 WHERE s1.t <=> s2.t;
****
+------+------+------+------+
| b    | t    | b    | t    |
+------+------+------+------+
|    1 | blue |    1 | blue |
|    1 | NULL |    1 | NULL |
|    0 | red  |    0 | red  |
+------+------+------+------+