Predicates¶
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 true or false.
They are used wherever a decision can be made, for example in JOIN or WHERE clauses.
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;
Binary Predicates¶
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:
| Operator | Description |
|---|---|
= |
Equal predicate |
!= |
Not Equal predicate |
<= |
Less Than or Equal predicate |
>= |
Great Than or Equal predicate |
< |
Less Than predicate |
> |
Greater Than predicate |
IS DISTINCT FROM |
Special Not Equal predicate |
IS NOT DISTINCT FROM |
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.0 and 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
Compound Predicates¶
Tne following Boolean operators can be used to combine expressions into more complex predicates:
| Operator | Description |
|---|---|
AND |
Boolean AND operator |
OR |
Boolean OR operator |
NOT |
Boolean NOT operator |