Ternary Logic¶
As specified in the SQL standard, ternary logic, or threevalued logic (3VL), is a logic system with three truth values: TRUE, FALSE, and UNKNOWN. In Snowflake, UNKNOWN is represented by NULL. Ternary logic applies to the evaluation of Boolean expressions, as well as predicates, and affects the results of logical operations such as AND, OR, and NOT:
When used in expressions (e.g. SELECT list), UNKNOWN results are returned as NULL values.
When used as a predicate (e.g. WHERE clause), UNKNOWN results evaluate to FALSE.
In this Topic:
Truth Tables¶
This section describes the truth tables for the comparison and logical operators.
Comparison Operators¶
If any operand for a comparison operator is NULL, the result is NULL. Comparison operators are:
Logical Operators¶
Given a BOOLEAN column C
:
If 




TRUE 
NULL 
TRUE 
FALSE 
FALSE 
FALSE 
NULL 
TRUE 
NULL 
NULL 
NULL 
NULL 
In addition:
If 




TRUE 
FALSE 
TRUE 
FALSE 
FALSE 
FALSE 
TRUE 
NULL 
NULL 
NULL 
NULL 
NULL 
Usage Notes for Conditional Expressions¶
This section describes behavior specific to the following conditional expressions.
IFF Behavior¶
The IFF function returns the following results for ternary logic. Given a BOOLEAN column C
:
If 


TRUE 

FALSE 

NULL 

[ NOT ] IN Behavior¶
The [ NOT ] IN functions return the following results for ternary logic. Given 3 numeric columns c1
, c2
, and c3
:
c1 IN (c2, c3, ...)
is syntactically equivalent to(c1 = c2 or c1 = c3 or ...)
.As a result, when the value of
c1
is NULL, the expressionc1 IN (c2, c3, NULL)
always evaluates to FALSE.c1 NOT IN (c2, c3, ... )
is syntactically equivalent to(c1 <> c2 AND c1 <> c3 AND ...)
.Therefore, even if
c1 NOT IN (c2, c3)
is TRUE,c1 NOT IN (c2, c3, NULL)
evaluates to NULL.