Robert Eisele
Systems Engineer, Architect and DBA

Logical operations with NULL

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.

You might also be interested in the following

 

Sorry, comments are closed for this article. Contact me if you have an inventive contribution.