Boolean Functions and Operators​

db> 

select (True, true, TRUE);

{(true, true, true)}

db> 

select (False, false, FALSE);

{(false, false, false)}

db> 

select true and 2 < 3;

{true}

db> 

select '!' IN {'hello', 'world'};

{false}

db> 

select <bool>('true');

{true}

db> 

select <bool>to_json('false');

{false}

select User
filter .name ilike 'alice';

db> 

select false or true;

{true}

db> 

select false and true;

{false}

db> 

select not false;

{true}

The and and or operators are commutative.

The truth tables are as follows:

The operators and/or and the functions all()/any() differ in the way they handle an empty set ({}). Both and and or operators apply to the cross-product of their operands. If either operand is an empty set, the result will also be an empty set. For example:

db> 

select {true, false} and <bool>{};

{}

db> 

select true and <bool>{};

{}

Operating on an empty set with all()/any() does not return an empty set:

db> 

select all(<bool>{});

{true}

db> 

select any(<bool>{});

{false}

all() returns true because the empty set contains no false values.

any() returns false because the empty set contains no true values.

The all() and any() functions are generalized to apply to sets of values, including {}. Thus they have the following truth table:

Since all() and any() apply to sets as a whole, missing values (represented by {}) are just that - missing. They don’t affect the overall result.

To understand the last line in the above truth table it’s useful to remember that all({a, b}) = all(a) and all(b) and any({a, b}) = any(a) or any(b).

For more customized handling of {}, use the ?? operator.