In SQL, predicates are logical conditions that are used to influence what a query is returning.
Their return value is a Boolean value of either
They are used wherever a decision can be made, for example in
For example, in a
WHERE clause predicates might be used to filter rows based on expressions that act on column values.
Example: Filtering rows using an expression predicate
> SELECT * FROM table1 WHERE col1 < 100;
The most basic predicates are called binary, which means they take two operands in the form of
<operand1> <operator> <operand2>.
The following operators for binary predicates are supported by Okera:
||Not Equal predicate|
||Less Than or Equal predicate|
||Great Than or Equal predicate|
||Less Than predicate|
||Greater Than predicate|
||Special Not Equal predicate|
||Special Equal predicate|
The latter two functions handle
NULL more specifically, based on the fact that in SQL
NULLs are not equal to anything.
For that reason, there are special predicates that handle
NULLs so that the result is always a valid Boolean value.
The following examples show how common comparisons with
NULL values return no Boolean value, and how the special predicates address that.
Example: Handling of
NULL using basic predicates
> SELECT NULL = NULL; null > SELECT NULL IS DISTINCT FROM NULL; false
Note that the comparision of, for instance, a
DECIMAL with a
TINYINT value is still a numerical comparision, meaning
2 are considered the same number.
Example: Comparision of numbers
> SELECT typeof(2.0), typeof(2); DECIMAL(2,1), TINYINT > SELECT 2.0 IS DISTINCT FROM 2; false > SELECT 2.0 = 2; true
Tne following Boolean operators can be used to combine expressions into more complex predicates:
||Boolean AND operator|
||Boolean OR operator|
||Boolean NOT operator|