MSQL Reference – Logical Operators

The following are the built-in logical operators available in the MSQL language.
They are divided into two groups:

Boolean Operators - Logical AND, OR and NOT.
Subquery Operators - EXISTS and [NOT] IN operators.

Note on Boolean Logic

MSQL uses a three-valued logic system with values true, false and null (representing an unknown logical value).
The data type used for holding logical (i.e. boolean) values is bool.

Boolean Operators

AND LOGICAL AND

(a: bool) AND (b: bool) -> bool

Returns true when both operands are true.

Null handling

NULL operand values are treated according to the truth-table below.

Notes

The truth-table of the AND operator:

`a`	`b`	`a AND b`
`false`	`false`	`false`
`false`	`true`	`false`
`false`	`null`	`false`
`true`	`false`	`false`
`true`	`true`	`true`
`true`	`null`	`null`
`null`	`false`	`false`
`null`	`true`	`null`
`null`	`null`	`null`

The AND operator has a special behaviour regarding the evaluation of its operands called short-circuiting, whereby if the result of the operation can be deduced only from the first operand (i.e. if it's false), the second won't be evaluated at all. For example, the expression (x > 0) AND (10 / x > 1) won't cause a division-by-zero error even in the case x=0, as the evaluation of second AND operand will be skipped because the first evaluated to false.

Examples

SELECT true AND false;
  --> false

See also

OR NOT & operator

OR LOGICAL OR

(a: bool) OR (b: bool) -> bool

Returns true when either of the operands is true.

Null handling

NULL operand values are treated according to the truth-table below.

Notes

The truth-table of the OR operator:

`a`	`b`	`a OR B`
`false`	`false`	`false`
`false`	`true`	`true`
`false`	`null`	`null`
`true`	`false`	`true`
`true`	`true`	`true`
`true`	`null`	`true`
`null`	`false`	`null`
`null`	`true`	`true`
`null`	`null`	`null`

The OR operator has a special behaviour regarding the evaluation of its operands called short-circuiting, whereby if the result of the operation can be deduced only from the first operand (i.e. if it's true), the second won't be evaluated at all. For example, the expression (x < 1) OR (10 / x > 1) won't cause a division-by-zero error even in the case x=0, as the evaluation of second OR operand will be skipped because the first evaluated to true.

Examples

SELECT true OR false;
  --> true

See also

AND NOT | operator

NOT LOGICAL NOT

NOT (a: bool) -> bool

Returns false when the operand is true, true when the operand is false.

Null handling

NULL operand value is treated according to the truth-table below.

Notes

The truth-table of the NOT operator:

`a`	`NOT a`
`false`	`true`
`true`	`false`
`null`	`null`

Examples

SELECT NOT true;
  --> false

See also

AND OR NOT ~ operator

Subquery Operators

EXISTS

EXISTS (subquery) -> bool

Evaluates the sub-query and checks whether it returns any rows or not.

Arguments

The argument of the EXISTS operator is an arbitrary select sub-query.

Notes

The sub-query can refer to variables from the surrounding query, which act as constants during the evaluation of the subquery.

The sub-query won't necessarily execute all the way to completion - it might be executed just long enough to determine whether at least one row will be returned or not. This is left to the query optimizer to decide.

Since the result depends only on whether any rows are returned, and not on the contents of those rows, the select list of the subquery is usually not important.
Therefore, it is common to write EXISTS tests in the form EXISTS (SELECT 1 FROM ...).

Examples

WITH primes(p) AS (VALUES 2, 3, 5, 7)
SELECT x FROM (VALUES 1, 2, 3, 4) AS t(x)
  WHERE EXISTS(SELECT 1 FROM primes WHERE p=x);

--> +---+
--> | x |
--> +---+
--> | 2 |
--> | 3 |
--> +---+

See also

[NOT] IN

IN IN

(x: T) IN ((value1: T), (value2: T), ...) -> bool

(x: T) NOT IN ((value1: T), (value2: T), ...) -> bool

(x: T) IN (subquery) -> bool

(x: T) NOT IN (subquery) -> bool

Checks whether a given value is contained in a list of values or in the results of a subquery.

Notes

The two variants of the [NOT] IN operator differ in the type of the second argument. One variant takes a list of values, and the other one takes a parenthesized subquery, which must return exactly one column per row.

In both cases, the value of the first argument is tested for equality with the values in the list/subquery result one-by-one, until an equal item is found or there are no more values to compare with. If an equal item was found, IN operator will return true and NOT IN will return false; if it wasn't found, the results will be inverted.

If the left-hand expression evaluates to NULL, or if there are no right-hand values equal to it and at least one of the right-hand values is NULL, the result of the [NOT] IN operators will be NULL, not false / true as one might expect. This is in accordance with MSQL's three-valued Boolean logic rules, but might be surprising to first-time users.

x NOT IN y is equivalent to NOT (x IN y). However, using NOT IN more clearly expresses the intention of the check, and makes it easier to reason about it in special (and tricky) cases, such as when dealing with NULL values.

The value-list variant of the operators is most often expanded during query planning into (x = value1) OR (x = value2) OR ... (in case of IN operator) or (x <> value1) AND (x <> value2) AND ... (in case of NOT IN operator).

Examples

SELECT x FROM (VALUES 1, 2, 3, 4, 5) AS t(x)
  WHERE x NOT IN (2, 3, 5, 7);

--> +---+
--> | x |
--> +---+
--> | 1 |
--> | 4 |
--> +---+

WITH primes(p) AS (VALUES 2, 3, 5, 7)
SELECT x FROM (VALUES 1, 2, 3, 4, 5) AS t(x)
  WHERE x IN (SELECT p FROM primes);

--> +---+
--> | x |
--> +---+
--> | 2 |
--> | 3 |
--> | 5 |
--> +---+

See also

EXISTS