Logical Functions

Logical operators of SQL can return TRUE, FALSE or NULL (unknown) values.

Note:

• Valentina considers any non-zero value as TRUE.
• Valentina returns 1 as TRUE, 0 as FALSE and NULL.

NOT
!

Returns NULL if operand is NULL. Returns 1 if the operand is 0, otherwise returns 0.

SELECT IF(NOT 1,1,0), IF(NOT 2,1,0);
=> 0, 0
SELECT IF(NOT 0,1,0), IFNULL(NOT NULL);
=> 1, 1
SELECT IF(! 1, 1, 0), IF(! 2, 1, 0);
=> 0, 0
SELECT IF(! 0, 1, 0), IFNULL(! NULL, 1);
=> 1, 1

AND
&&

Returns NULL if any operand is NULL. Returns 1 if all operands are non-zero, otherwise returns 0.

SELECT IF(1 &&1, 1, 0);
=> 1
SELECT IF(1 &&0, 1, 0);
=> 0
 
SELECT IFNULL(1 && NULL, 1);
=> 1
 
SELECT IF(0 && NULL, 1, 0);
=> 0
 
SELECT IF(NULL && 0, 1, 0);
=> 0

OR
||

Returns NULL if any operand is NULL. Returns 1 if any operand is non-zero, otherwise returns 0.

SELECT IF(1 || 1, 1, 0);
=> 1
SELECT IF(1 || 0, 1, 0);
=> 1
SELECT IF(0 || 0, 1, 0);
=> 0
SELECT IFNULL(0 || NULL, 1);
=> 1
SELECT IFNULL(1 || NULL, 0);
=> 1

XOR

Returns NULL if any operand is NULL. Returns 1 if an odd number of operands is non-zero, otherwise returns 0.

SELECT IF(1 XOR 1, 1, 0);
=> 0
SELECT IF(1 XOR 0, 1, 0);
=> 1
SELECT IFNULL(1 XOR NULL, 1);
=> 1
SELECT IF(1 XOR 1 XOR 1, 1, 0);
=> 1

a XOR b is mathematically equal to (a AND (NOT b)) OR ((NOT a) and b).