WHERE clause

The WHERE clause inputs the bindings that have been produced from the FROM clause and outputs the ones that satisfy its condition.

The boolean predicates follow SQL’s 3-valued logic. Recall, PartiQL has two kinds of absent values: NULL and MISSING. As far as the boolean connectives and IS NULL are concerned a NULL input and a MISSING input behave identically. For example, MISSING AND TRUE is equivalent to NULL AND TRUE: they both result into NULL.

Alike SQL, when the expression of the WHERE clause expression evaluates to an absent value or a value that is not a Boolean, PartiQL eliminates the corresponding binding.

For example,

SELECT VALUE v.a
FROM [
    { 'a' : 1, 'b': true },
    { 'a': 2, 'b': null },
    { 'a': 3 }
] v
WHERE v.b

-- result
<< 1 >>

The predicate IS MISSING allows distinguishing between NULL and MISSING.

NULL IS MISSING results to false
MISSING IS MISSING results to true