raw code

Logical operations with NULL

Robert Eisele

While NULL in C is a macro with different implementations for a NULL-Pointer such as "0" or a void* cast for "0" (void *)0, NULL in PHP and MySQL has a special meaning. If you test in PHP NULL against an empty string or an empty array you will get true, assumed you don't use typesafe relational operators (=== or !==).

The title of this article is "logical operations with NULL" - so let's see how the results will look like, when we have a real NULL as an operand.

var_dump(1 || 1);
var_dump(0 || 1);
var_dump(1 || 0);
var_dump(0 || 0);
var_dump(1 || NULL);
var_dump(0 || NULL);
var_dump(NULL || 1);
var_dump(NULL || 0);
var_dump(NULL || NULL);

The output is the following:

bool(true)
bool(true)
bool(true)
bool(false)
bool(true)
bool(false)
bool(true)
bool(false)
bool(false)

A simple definition could be: PHP cast the given operands to the accordant logical value and runs the operation between the operands.

Quite easy I think - so let's see how this is implemented in MySQL:

SELECT (1 || 1);
SELECT (0 || 1);
SELECT (1 || 0);
SELECT (0 || 0);
SELECT (1 || NULL);
SELECT (0 || NULL);
SELECT (NULL || 1);
SELECT (NULL || 0);
SELECT (NULL || NULL);

Hmm...

(1 || 1): 1
(0 || 1): 1
(1 || 0): 1
(0 || 0): 0
(1 || NULL): 1
(0 || NULL): NULL
(NULL || 1): 1
(NULL || 0): NULL
(NULL || NULL): NULL

The first results are the same - but what happens with the results where we have a NULL operand? When you go the list from top to bottom you could think about the following defintion: "MySQL implements logical operations by look at the first operand and decide if you use the first or second operand". But this definition is depricated when you arrived (NULL || 0): NULL. Now you could think MySQL looks at a casted version of NULL - 0 - so skip this value and look what is in the second. This is 0 too so use the first operand. But no; The only logical conclusion is that MySQL gives NULL a special meaning. My definition for this manner looks as follows: "A expression is ever NULL if one operator is NULL and the interpreter arrives this without a logical exclusion." By the way - this behavior for SQL was described by Edgar Codd.