Applies to: ✅ Data 360 SQL ✅ Tableau Hyper API
Predicates that test for NULL values and boolean truth values. Unlike ordinary comparison operators, these predicates always return true or false, never NULL.
1<a> IS DISTINCT FROM <b>
2<a> IS NOT DISTINCT FROM <b>
3<expression> IS NULL
4<expression> IS NOT NULL
5<expression> ISNULL -- nonstandard syntax
6<expression> NOTNULL -- nonstandard syntax
7<boolean_expression> IS TRUE
8<boolean_expression> IS NOT TRUE -- is false or unknown
9<boolean_expression> IS FALSE
10<boolean_expression> IS NOT FALSE -- is true or unknown
11<boolean_expression> IS UNKNOWN
12<boolean_expression> IS NOT UNKNOWN -- is true or falseOrdinary comparison operators yield NULL (signifying “unknown”) if either input is NULL. For example, 7 = NULL returns NULL. When you need to treat NULL as an ordinary value, use the IS DISTINCT FROM predicates:
a IS DISTINCT FROM b: For non-NULL inputs, this is the same as the <> operator. If both inputs are NULL, it returns false. If one input is NULL, it returns true.a IS NOT DISTINCT FROM b: Identical to = for non-NULL inputs. It returns true if both inputs are NULL, and false if one input is NULL.These predicates handle NULL as a normal data value rather than an unknown.
To check whether a value is or isn’t NULL, use these predicates:
expression IS NULLexpression IS NOT NULLexpression ISNULL (nonstandard syntax)expression NOTNULL (nonstandard syntax)To test the truth value of a boolean expression, use these predicates:
IS TRUE / IS NOT TRUE (is false or unknown)IS FALSE / IS NOT FALSE (is true or unknown)IS UNKNOWN / IS NOT UNKNOWN (is true or false)IS UNKNOWN and IS NOT UNKNOWN are effectively the same as IS NULL and IS NOT NULL, respectively, except that the input expression must be of boolean type.
1SELECT (NULL IS NOT DISTINCT FROM NULL) AS result;Returns true, whereas NULL = NULL returns NULL.
1SELECT col1
2FROM tab1
3WHERE col1 IS NOT NULL;
j