## Predicates

A predicate is a single conditional expression which evaluates to either true, false or unknown. Predicates are used in constructing search conditions, see Search Conditions.

## Predicate Syntax

The general predicate syntax is shown below:

Each individual predicate construction is explained in more detail in the following sections.

## The Basic Predicate

A basic predicate compares a value with one and only one other value, and has the syntax:

The comparison operators,

`comp-operator`

, are described in Comparison and Relational Operators.The expressions on either side of the comparison operator must have compatible data types, see Comparisons.

Within the context of a basic predicate, a

`select-specification`

must result in either an empty set or a single value.The result of the predicate is unknown if either of the expressions used evaluates to

`NULL`

, or if the`select-specification`

used results in an empty set.## The Quantified Predicate

A quantified predicate compares an expression with a set of values addressed by a subselect (as opposed to a basic predicate which compares two single-valued expressions).

The form of the quantified expression is:

The comparison operators,

`comp-operator`

, are described in Comparison and Relational Operators.Within the context of a quantified predicate, a

`select-specification`

must result in either an empty set or a set of single values.## ALL

The result is true if the select-specification results in an empty set or if the comparison is true for every value returned by the

`select-specification`

.The result is false if the comparison is false for at least one value returned by the

`select-specification`

.The result is unknown if any of the values returned by the

`select-specification`

is NULL and no value is false.## ANY or SOME

The keywords

`ANY`

and`SOME`

are equivalent.The result is true if the comparison is true for at least one value returned by the

`select-specification`

.The result is false if the select-specification results in an empty set or if the comparison is false for every value returned by the

`select-specification`

.The result is unknown if any of the values returned by the select-specification is

`NULL`

and no value is true.Quantified predicates may always be replaced by alternative formulations using

`EXISTS`

, which can often clarify the meaning of the predicates.## The IN Predicate

The

`IN`

predicate tests whether a value is contained in a set of discrete values and has the form:If the set of values on the right hand side of the comparison is given as an explicit list, an

`IN`

predicate may always be expressed in terms of a series of basic predicates linked by one of the logical operators`AND`

or`OR`

:

## IN predicate

## Equivalent basic predicates

x IN (a,b,c) x = a OR x = b OR x = c x NOT IN (a,b,c) x <> a AND x <> b AND x <> cIf the set of values is given as a

`select-specification`

, an`IN`

predicate is equivalent to a quantified predicate:

## IN predicate

## Equivalent quantified predicates

x IN (subselect) x = ANY (subselect) x NOT IN (subselect) x <> ALL (subselect)The result of the

`IN`

predicate is unknown if the equivalent predicates give an unknown result.## The BETWEEN Predicate

A

`BETWEEN`

predicate tests whether or not a value is within a range of values (including the given limits).The

`BETWEEN`

predicate can always be expressed in terms of two basic predicates.

## Between predicate

## Equivalent basic predicates

x BETWEEN a AND b x >= a AND x <= b x NOT BETWEEN a AND b x < a OR x > bAll expressions in the predicate must have compatible data types.

The result of the predicate is unknown if the equivalent basic predicates give an unknown result.

## The LIKE Predicate

The

`LIKE`

predicate compares the value in a string expression with a character string pattern which may contain wildcard characters (meta-characters).The

`string-value`

on the left hand side of the`LIKE`

operator must be a string expression.The

`character-pattern`

on the right hand side of the`LIKE`

operator is a string expression that can be specified as a string literal or by using a host variable.The

`character-value`

must be a string expression of length 1. To search for the escape character itself it must appear twice in immediate succession.## Meta-characters/Wildcards

The following meta-characters (wildcards) may be used in the

`character-pattern`

:

`_`

stands for any single character

`%`

stands for any sequence of zero or more characters.## Note: Wildcard characters are only used as such in LIKE predicates. In any other context, the characters _ and % have their exact values.

## Escape Characters

The optional escape character is used to allow matching of the special characters

`_`

and`%`

. When the escape character prefixes`_`

and`%`

, they are interpreted without any special meaning.An escape character used in a pattern string may only be followed by another escape character or one of the wildcard characters, unless it is itself escaped (i.e. preceded by an escape character).

## Examples

A

`LIKE`

predicate where the pattern string does not contain any wildcard characters is essentially equivalent to a basic predicate using the`=`

operator.The comparison strings in the

`LIKE`

predicate are not conceptually padded with blanks, in contrast to the basic comparison.

`'artist ' LIKE 'artist '`

is true

`'artist ' LIKE 'artist%'`

is true

`'artist ' LIKE 'artist'`

is false## BEGINS function

LIKE predicates, addressing the "begins with" functionality, are very common.

However, when a parameter marker is used for the LIKE pattern, the SQL compiler cannot determine the LIKE pattern characteristics, and possible optimizations will not be applied. The builtin function BEGINS will overcome this issue.

## Examples

## BEGINS function

## Is equivalent to

`BEGINS(col,'AB')`

`col LIKE 'AB%'`

`BEGINS(col,?)`

,

where ? contains '`XYZ`

'`col LIKE 'XYZ%'`

## The NULL Predicate

The

`NULL`

predicate is used to test if the specified expression is the`NULL`

value, and has the form:If the predicate specifies

`expression IS NULL`

, then the result is true if any operand in the expression is`NULL`

.The result is false if no operand in the expression is

`NULL`

.The result of the

`NULL`

predicate is never unknown.The use of composite expressions in

`NULL`

predicates provides a shorthand for testing whether any of the operands is`NULL`

.Thus the predicate

`A+B IS NULL`

is an alternative to`A IS NULL OR B IS NULL`

, provided that`A+B`

does not result in overflow.## Note: The actual arithmetical operator(s) used in numerical expressions in NULL predicates is irrelevant since all arithmetical operations involving a NULL value evaluate to the NULL value.

The

`NULL`

predicate is the only way to test for the presence of the`NULL`

value in a column, since all other predicates where at least one of the operands is`NULL`

evaluate to unknown.## The EXISTS Predicate

The

`EXISTS`

predicate tests whether the set of values addressed by a`select-specification`

is empty or not, and has the form:The result of the predicate is true if the

`select-specification`

does not result in an empty set. Otherwise the result of the predicate is false. A set containing only`NULL`

values is not empty. The result is never unknown.The

`EXISTS`

predicate is the only predicate which does not compare a value with one or more other values. The columns selected in the`select-specification`

of an`EXISTS`

predicate are irrelevant. Most commonly, the`SELECT *`

shorthand is used.The

`EXISTS`

predicate may be negated in the construction of search conditions. Observe however that`NOT EXISTS`

predicates must be handled with care, particularly if empty result sets arise in the selection condition.## Examples

Consider the four following examples, and note particularly that the last example is true if all guests have undefined names:

EXISTS (SELECT * FROM BOOK_GUEST WHERE GUEST = 'DATE')requires that at least one guest is called DATE

NOT EXISTS (SELECT * FROM BOOK_GUEST WHERE GUEST = 'DATE')requires that no guest may be called DATE

EXISTS (SELECT * FROM BOOK_GUEST WHERE NOT GUEST = 'DATE')requires that at least one guest is not called DATE

NOT EXISTS (SELECT * FROM BOOK_GUEST WHERE NOT GUEST = 'DATE')requires that no guest may not be called DATE, i.e. every guest must be called DATE (or be

`NULL`

).## The OVERLAPS Predicate

The

`OVERLAPS`

predicate tests whether two `events' cover a common point in time or not, and has the form:Each of the two events specified on either side of the

`OVERLAPS`

keyword is a period of time between two specified points on the time-line. The two points can be specified as a pair of datetime values or as one datetime value and an`INTERVAL`

offset.The first column in each row value expression must be a

`DATE`

,`TIME`

or`TIMESTAMP`

and the value in the first column of the first event must be comparable, see Datetime Assignment Rules, to the value in the first column of the second event.The second column in each row value expression may be either a

`DATE`

,`TIME`

or`TIMESTAMP`

that is comparable with the value in the first column or an`INTERVAL`

with a precision that allows it to be added to the value in the first column.The value in the first column of each row value expression defines one of the points on the time-line for the event.

If the value in the second column of the row value expression is a datetime, it defines the other point on the time-line for the event.

If the value in the second column of the row value expression is an

`INTERVAL`

, the other point on the time-line for the event is defined by adding the values in the two column of the row value to expression together.The

`NULL`

value is assumed to be a point that is infinitely late in time.Either of the two points may be the earlier point in time.

If the value in the first column of the row value expression is the

`NULL`

value, then this is assumed to be the later point in time.The result of

`(S1,T1) OVERLAPS (S2,T2)`

is the result of the following expression:(S1 > S2 AND NOT (S1 >= T2 AND T1 >= T2)) OR (S2 > S1 AND NOT (S2 >= T1 AND T2 >= T1)) OR (S1 = S2 AND (T1 <> T2 OR T1 = T2))## Standard Compliance

This section summarizes standard compliance concerning predicates.

Mimer Information Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 info@mimer.se |