Logical Operators - Amazon Kinesis Data Analytics SQL Reference
Three State Boolean LogicOther Logical Operators

Logical Operators

Logical operators let you establish conditions and test their results.

Operator Unary/Binary Description Operands

NOT

U

Logical negation

Boolean

AND

B

Conjunction

Boolean

OR

B

Disjunction

Boolean

IS

B

Logical assertion

Boolean

IS NOT UNKNOWN

U

Negated unknown comparison:

<expr> IS NOT UNKNOWN

Boolean

IS NULL

U

Null comparison:

<expr> IS NULL

Any

IS NOT NULL

U

Negated null comparison:

<expr> IS NOT NULL

Any

=

B

Equality

Any

!=

B

Inequality

Any

<>

B

Inequality

Any

>

B

Greater than

Ordered types (Numeric, String, Date, Time)

>=

B

Greater than or equal to (not less than)

Ordered types

<

B

Less than

Ordered types

<=

B

Less than or equal to (not more than)

Ordered types

BETWEEN

Ternary

Range comparison:

col1 BETWEEN expr1 AND expr2

Ordered types

IS DISTINCT FROM

B

Distinction

Any

IS NOT DISTINCT FROM

B

Negated distinction

Any

Three State Boolean Logic

SQL boolean values have three possible states rather than the usual two: TRUE, FALSE, and UNKNOWN, the last of which is equivalent to a boolean NULL. TRUE and FALSE operands generally function according to normal two-state boolean logic, but additional rules apply when pairing them with UNKNOWN operands, as the tables that follow will show.

Note

UNKOWN represents "maybe TRUE, maybe FALSE" or, to put it another way, "not definitely TRUE and not definitely FALSE." This understanding may help you clarify why some of the expressions in the tables evaluate as they do.

Negation (NOT)
Operation Result

NOT TRUE

FALSE

NOT FALSE

TRUE

NOT UNKNOWN

UNKNOWN
Conjunction (AND)
Operation Result

TRUE AND TRUE

TRUE

TRUE AND FALSE

FALSE

TRUE AND UNKNOWN

UNKNOWN

FALSE AND TRUE

FALSE

FALSE AND FALSE

FALSE

FALSE AND UNKNOWN

FALSE

UNKNOWN AND TRUE

UNKNOWN

UNKNOWN AND FALSE

FALSE

UNKNOWN AND UNKNOWN

UNKNOWN
Disjunction (OR)
Operation Result

TRUE OR TRUE

TRUE

TRUE OR FALSE

TRUE

TRUE OR UNKNOWN

TRUE

FALSE OR TRUE

TRUE

FALSE OR FALSE

FALSE

FALSE OR UNKNOWN

UNKNOWN

UNKNOWN OR TRUE

TRUE

UNKNOWN OR FALSE

UNKNOWN

UNKNOWN OR UNKNOWN

UNKNOWN
Assertion (IS)
Operation Result

TRUE IS TRUE

TRUE

TRUE IS FALSE

FALSE

TRUE IS UNKNOWN

FALSE

FALSE IS TRUE

FALSE

FALSE IS FALSE

TRUE

FALSE IS UNKNOWN

FALSE

UNKNOWN IS TRUE

FALSE

UNKNOWN IS FALSE

FALSE

UNKNOWN IS UNKNOWN

TRUE
IS NOT UNKNOWN
Operation Result

TRUE IS NOT UNKNOWN

TRUE

FALSE IS NOT UNKNOWN

TRUE

UNKNOWN IS NOT UNKNOWN

FALSE

IS NOT UNKNOWN is a special operator in and of itself. The expression "x IS NOT UNKNOWN" is equivalent to "(x IS TRUE) OR (x IS FALSE)", not "x IS (NOT UNKNOWN)". Thus, substituting in the table above:

x Operation Result Result of substituting for x in "(x IS TRUE) OR (x IS FALSE)"

TRUE

TRUE IS NOT UNKNOWN

TRUE

becomes

"(TRUE IS TRUE) OR (TRUE IS FALSE)" -- hence TRUE

FALSE

FALSE IS NOT UNKNOWN

TRUE

becomes

"(FALSE IS TRUE) OR (FALSE IS FALSE)" -- hence TRUE

UNKNOWN

UNKNOWN IS NOT UNKNOWN

FALSE

becomes

"(UNKNOWN IS TRUE) OR (UNKNOWN IS FALSE)" -- hence FALSE,

since UNKNOWN is neither TRUE not FALSE

Since IS NOT UNKNOWN is a special operator, the operations above are not transitive around the word IS:

Operation Result

NOT UNKNOWN IS TRUE

FALSE

NOT UNKNOWN IS FALSE

FALSE

NOT UNKNOWN IS UNKNOWN

TRUE
IS NULL and IS NOT NULL
Operation Result

UNKNOWN IS NULL

TRUE

UNKNOWN IS NOT NULL

FALSE

NULL IS NULL

TRUE

NULL IS NOT NULL

FALSE
IS DISTINCT FROM and IS NOT DISTINCT FROM
Operation Result

UNKNOWN IS DISTINCT FROM TRUE

TRUE

UNKNOWN IS DISTINCT FROM FALSE

TRUE

UNKNOWN IS DISTINCT FROM UNKNOWN

FALSE

UNKNOWN IS NOT DISTINCT FROM TRUE

FALSE

UNKNOWN IS NOT DISTINCT FROM FALSE

FALSE

UNKNOWN IS NOT DISTINCT FROM UNKNOWN

TRUE

Informally, "x IS DISTINCT FROM y" is similar to "x <> y", except that it is true even when either x or y (but not both) is NULL. DISTINCT FROM is the opposite of identical, whose usual meaning is that a value (true, false, or unknown) is identical to itself, and distinct from every other value. The IS and IS NOT operators treat UNKOWN in a special way, because it represents "maybe TRUE, maybe FALSE".

Other Logical Operators

For all other operators, passing a NULL or UNKNOWN operand will cause the result to be UNKNOWN (which is the same as NULL).

Examples
Operation Result

TRUE AND CAST( NULL AS BOOLEAN)

UNKNOWN

FALSE AND CAST( NULL AS BOOLEAN)

FALSE

1 > 2

FALSE

1 < 2

TRUE

'foo' = 'bar'

FALSE

'foo' <> 'bar'

TRUE

'foo' <= 'bar'

FALSE

'foo' <= 'bar'

TRUE

3 BETWEEN 1 AND 5

TRUE

1 BETWEEN 3 AND 5

FALSE

3 BETWEEN 3 AND 5

TRUE

5 BETWEEN 3 AND 5

TRUE

1 IS DISTINCT FROM 1.0

FALSE

CAST( NULL AS INTEGER ) IS NOT DISTINCT FROM CAST (NULL AS INTEGER)

TRUE