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:

where row-expression is:

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

Mimer SQL Engine
Mimer SQL Engine does only support the value-expression part of the row-expression syntax. (E.g. multi-expression row-expressions are not supported.)

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-expression 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-expression used results in an empty result set.

A comparison involving row expressions requires that the two row expressions have the same number of elements, and that each element in the first row expression is comparable with the corresponding element in the second row expression.

The comparison will be done from left to right and will continue until all elements have been compared or the predicate is false.

As an example, consider this predicate

 (a1,a2,a3) < (b1,b2,b3)
 

which is equivalent to

 a1 < b1 or (a1 <= b1 and a2 < b2) or (a1 <= b1 and a2 <= b2 and a3 < b3)
 

For instance

 (1,date '1956-04-23', false) < (1,date '1956-04-23', true)
 

would evaluate to TRUE since false is less than true.

Null values are handled analogously with comparisons with single values. Thus

 (1,cast(null as int)) < (1,2)
 

would become null, but

 (1,cast(null as int)) < (2,2)
 

would become true since the second elements are never compared in this case.

Row-expression examples

 select * from tabA where (c1, c2) = (select k1, k2 from tabB fetch 1);
 
 select * from tabA where (abs(c1), c2) > (c3, lower(c4));

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-expression 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-expression.

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

The result is unknown if any of the values returned by the select-expression 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-expression.

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

The result is unknown if any of the values returned by the select 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 <> c

If the set of values is given as a select-expression, 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.

Note: NOT IN is undefined if the subselect's result contains a NULL value. E.g. SELECT * FROM tab WHERE 1 NOT IN (3, <null>, 4) will return an empty result set.

The BETWEEN Predicate

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

It has the form:

The BETWEEN predicate can always be expressed in terms of basic predicates.

If neither SYMMETRIC nor ASYMMETRIC is specified, then ASYMMETRIC is implicit.

Thus:

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 > b
x BETWEEN SYMMETRIC a AND b	(x >= a AND x <= b) OR (x >= b AND x <= a)
x NOT BETWEEN SYMMETRIC a AND b	(x > a AND x > b) OR (x < a AND x < b)

Examples

Expression	Result
2 BETWEEN 1 AND 3	TRUE
2 BETWEEN 3 AND 1	FALSE
2 BETWEEN SYMMETRIC 1 AND 3	TRUE
2 BETWEEN SYMMETRIC 3 AND 1	TRUE
(1,2) BETWEEN (1,1) AND (1,3)	TRUE
(1,2) BETWEEN (1,1) AND (1,0)	FALSE

All 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).

It has the form:

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.

The escape character-value must be a string expression of length 1. To search for the escape character itself it must appear twice in immediate succession in the like pattern.

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

LIKE predicate	Matches
LIKE 'A%'	any string beginning with A
LIKE '_A%'	any string, where the second character is A
LIKE '%A%'	any string containing an A
LIKE '%\%%' ESCAPE '\'	any string containing a %
LIKE '%A\%\\' ESCAPE '\'	any string ending with A%\
LIKE '_ABC'	any 4-character string ending in ABC

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.

Thus:

'artist ' = 'artist' is true

'artist ' LIKE 'artist ' is true

'artist ' LIKE 'artist%' is true

but

'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 can not determine the LIKE pattern characteristics, and possible optimizations will not be applied. The built-in function BEGINS will overcome this issue.

It has the form:

Examples

BEGINS function	Is equivalent to
BEGINS(col,'AB')	col LIKE 'AB%'
BEGINS(col,?), where ? contains 'XYZ'	col LIKE 'XYZ%'

Regular Expressions

The REGEXP_MATCH predicate compares the value in a string expression with a character string pattern which may contain different meta-characters.

Compared to LIKE, the regular expression provides a much more flexible way to match strings of text, such as complex patterns of characters.

It has the form:

Regular expression constructs:

Characters
x	The character x
\	Escape for meta characters: $ & ( ) * + , - . ? [ ] ^ { | }
\\	The backslash character
\t	The tab character
\n	The newline character
\v	The vertical tab character
\f	The form feed character
\r	The carriage return character
\x{h...h}	The character with hex value 0xh...h (<= 0x10FFFF)

Character classes
[abc]	a, b, or c (simple class)
[^abc]	Any character except a, b, or c (negation)
[a-zA-Z]	a through z or A through Z, inclusive (range)
[[a-d][m-p]]	a through d, or m through p (union)
[[a-z]&&[def]]	d, e, or f (intersect)
[[a-z]--[bc]]	a through z, except for b and c (minus)

Predefined character classes
.	Any character
\d	A digit character
\D	Not a digit character ([^\d])
\s	A whitespace character
\S	Not a whitespace character ([^\s])
\w	A word character
\W	Not a word character ([^\w])

Boundary matchers
^	The beginning of string
$	The end of string

Quantifiers
X?	X, once or not at all
X*	X, zero or more times
X+	X, one or more times
X{n}	X, exactly n times
X{n,}	X, at least n times
X{n,m}	X, at least n but not more than m times

Logical operators
XY	X followed by Y
X|Y	Either X or Y
(X)	X, as a capturing group

Classes for Unicode categories
\p{L}	Letter
\p{Ll}	Lowercase_Letter
\p{Lu}	Uppercase_Letter
\p{Lt}	Titlecase_Letter
\p{Lm}	Modifier_Letter
\p{Lo}	Other_Letter
\p{N}	Number
\p{Nd}	Decimal_Digit_Number
\p{Nl}	Letter_Number
\p{No}	Other_Number
\p{M}	Mark
\p{Mn}	Non_Spacing_Mark
\p{Mc}	Spacing_Combining_Mark
\p{Me}	Enclosing_Mark
\p{P}	Punctuation
\p{Pd}	Dash_Punctuation
\p{Ps}	Open_Punctuation
\p{Pe}	Close_Punctuation
\p{Pi}	Initial_Punctuation
\p{Pf}	Final_Punctuation
\p{Pc}	Connector_Punctuation
\p{Po}	Other_Punctuation
\p{S}	Symbol
\p{Sm}	Math_Symbol
\p{Sc}	Currency_Symbol
\p{Sk}	Modifier_Symbol
\p{So}	Other_Symbol
\p{Z}	Separator
\p{Zs}	Space_Separator
\p{Zl}	Line_Separator
\p{Zp}	Paragraph_Separator
\p{C}	Other
\p{Cc}	Control
\p{Cf}	Format
\p{Co}	Private_Use
\p{Cn}	Unassigned

Classes for Unicode scripts
\p{Arabic}	\p{Kannada}
\p{Armenian}	\p{Katakana}
\p{Bengali}	\p{Khmer}
\p{Bopomofo}	\p{Lao}
\p{Cherokee}	\p{Latin}
\p{Common}	\p{Malayalam}
\p{Cyrillic}	\p{Mongolian}
\p{Devanagari}	\p{Myanmar}
\p{Ethiopic}	\p{Oriya}
\p{Georgian}	\p{Sinhala}
\p{Greek}	\p{Syriac}
\p{Gujarati}	\p{Tamil}
\p{Gurmukhi}	\p{Telugu}
\p{Han}	\p{Thaana}
\p{Hangul}	\p{Thai}
\p{Hebrew}	\p{Tibetan}
\p{Hiragana}	\p{Yi}

Examples

 regexp_match(search_string,'abc')
 

The regexp_match function will return TRUE if the search_string anywhere has the sequence abc. Note the difference with the like predicate where the same criteria would need to be expressed as

 search_string like '%abc%'
 

Escape of meta characters are done using a backslash character:

 regexp_match(search_string,'\[abc\]')
 

would be true if search_string anywhere contains the string [abc], (including the square brackets).

By using the boundary characters ^ and $ it is possible to specify that a search string should start with or end with some specific characters. E.g.

 regexp_match(search_string,'^Mimer')
 

would return true if the search_string started with the letters Mimer. For this type of searches, the database will consider using an index if appropriate.

The regexp_match function is collation aware. Thus

 regexp_match('AAlborg' collate danish_1,'ålborg')
 

is true while

 regexp_match('AAlborg' collate danish_2,'ålborg')
 

is false since a collation for danish will match AA to Å, but the level 1 collation is case insensitive which the level 2 collation is not.

This far, all of the examples given, can also be expressed with the like predicate. The following examples will deal with ranges and quantifiers which can be used to specify more complex search patterns.

To search for non-printable characters the regular expression

 '[\x{0}-\x{1B}]'
 

could be used.

To find strings beginning with An or A, regardless of case, followed by a space and one or more arbitrary characters the pattern would be

 '^(A|an)|A|n .+'
 

The pattern

 '[a-zA-Z]{3}.[0-9]{3}'
 

would match a string containing three occurrences of a letter between a and z or A and Z, followed by an arbitrary character and three consecutive digits.

General information about the different classes for Unicode categories can be found at http://www.unicode.org/reports/tr18/ and http://www.unicode.org/reports/tr44/. Please note that these documents cover lots of functionality not supported by Mimer SQL.

The NULL Predicate

The NULL predicate is used to test if the specified expression is the NULL value, and has the form:

The result of the NULL predicate is never unknown.

Evaluation rules for the NULL predicate:

x	x IS NULL	x IS NOT NULL	NOT x IS NULL	NOT x IS NOT NULL
null	True	False	False	True
not null	False	True	True	False
(null, null)	True	False	False	True
(not null, null)	False	False	True	True
(not null, not null)	False	True	True	False

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.

Note: The actual operator(s) used in expressions in NULL predicates is irrelevant since all 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-expression 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-expression 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:

Example 1

 EXISTS (SELECT * FROM BOOK_GUEST
 WHERE GUEST = 'DATE')
 

   requires that at least one guest is called DATE.

Example 2

 NOT EXISTS (SELECT * FROM BOOK_GUEST
 WHERE GUEST = 'DATE')
 

   requires that no guest may be called DATE.

Example 3

 EXISTS (SELECT * FROM BOOK_GUEST
 WHERE NOT GUEST = 'DATE')
 

   requires that at least one guest is not called DATE.

Example 4

 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.

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.

Standard	Compliance	Comments
SQL-2011	Core	Fully compliant.
SQL-2011	Features outside core	Feature F052, "Intervals and datetime arithmetic" overlaps predicate. Feature F053, "OVERLAPS predicate". Feature F281, "LIKE enhancements". The arguments for a LIKE predicate may be value expressions. Feature F561, "Full value expression". Feature T022, "Advanced support for BINARY and VARBINARY data types". Feature T461, "Symmetric BETWEEN predicate". Feature T501, "Enhanced EXISTS predicate". Mimer SQL Experience: Feature 641, "Row and table constructors".
	Mimer SQL extension	The BEGINS and REGEXP_MATCH functions are Mimer specific.

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