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 ODAS:

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