Predicates in SQL

Predicates are keywords that specify a relationship between two expressions. SQL supports the following types of predicates:

Comparison
Like
Between
In
Any-or-All
Exists
Is null

These predicates are described in the following sections.

Note: The null predicate is the only predicate that can be used with long varchar, long byte, and long nvarchar data.

Comparison Predicate

The syntax for comparison predicates is as follows:

expression_1 comparison_operator expression_2

In a comparison predicate, expression2 can be a subquery. If expression2 is a subquery and does not return any rows, the comparison predicate evaluates to false. For information about subqueries, see Subqueries. For details about comparison operators, see Comparison Operators.

Like Predicate

The like predicate performs pattern matching for the character data types (char, varchar, c, and text) and Unicode data types (nchar and nvarchar). The like predicate has the following syntax:

expression [not] like pattern [escape escape_character]

The expression can be a column name or an expression containing string functions.

The pattern parameter must be a string literal. The pattern-matching (wild card) characters are the percent sign (%) to denote 0 or more arbitrary characters, and the underscore (_) to denote exactly one arbitrary character.

The like predicate does not ignore trailing blanks. If you are matching a char value (that is padded with blanks when it is inserted) or if the value has trailing blanks that were entered by the user, include these trailing blanks in your pattern. For example, if searching a char(10) column for rows that contain the name harold, specify the trailing blanks in the pattern to be matched:

name like 'harold '

Four blanks are added to the pattern after the name to include the trailing blanks.

Because blanks are not significant when performing comparisons of c data types, the like predicate returns a correct result whether or not trailing blanks are included in the pattern.

If the escape clause is specified, the escape character suppresses any special meaning for the following character, allowing the character to be entered literally. The following characters can be escaped:

The pattern matching characters % and _.
The escape character itself. To enter the escape character literally, type it twice.
Square brackets [ ]. Within escaped square brackets ( [ and ] ), a series of individual characters or a range of characters separated by a dash (-) can be specified.

The following examples illustrate some uses of the pattern matching capabilities of the like predicate.

To match any string starting with a:

name like 'a%'

To match any string starting with A through Z:

name like '\[A-Z\]%' escape '\'

To match any two characters followed by 25%:

name like '__25\%' escape '\'

To match a string starting with a backslash:

name like '\%'

Because there is no escape clause, the backslash is taken literally.

To match a string starting with a backslash and ending with a percent:

name like '\\%\%' escape '\'

To match any string starting with 0 through 4, followed by an uppercase letter, then a [, any two characters and a final ]:

name like '\[01234\]\[A-Z\][__]' escape '\'

To detect names that start with S and end with h, disregarding any leading or trailing spaces:

trim(name) like 'S%h'

To detect a single quote, repeat the quote:

name like ''''

Between Predicate

The following table explains the operators between and not between:

Operator	Meaning
y between [asymmetric] x and z	x < = y and y < = z
y not between [asymmetric] x and z	not (y between x and z)
y between symmetric x and z	(x < = y and y < = z) or (z < = y and y < = x)
y not between asymmetric x and z	not (y between symmetric x and z)

x, y, and z are expressions, and cannot be subqueries.

In Predicate

The following table explains the operators in and not in:

Operator	Meaning
y in (x, ..., z)	The in predicate returns true if y is equal to one of the values in the list (x, ..., z). (x, ..., z) represents a list of expressions, each of which must evaluate to a single value. If there is only one expression in the list, the parentheses are optional. None of the expressions (y, x, or z) can be subqueries.
y not in (x, ..., z)	Returns true if y is not equal to any of the values in the list (x, ..., z). (x, ..., z) is a list of expressions, each of which must evaluate to a single value. If there is only one expression in the list, the parentheses are optional. None of the expressions (y, x, or z) can be subqueries.
y in (subquery)	Returns true if y is equal to one of the values returned by the subquery. The subquery must be parenthesized and can reference only one column in its select clause.
y not in (subquery)	Returns true if y is not equal to any of the values returned by the subquery. The subquery must be specified in parentheses and can reference only one column in its select clause.

Any-or-All Predicate

The any-or-all predicate takes the following form:

any-or-all-operator (subquery)

The subquery must have exactly one element in the target list of its outermost subselect (so that it evaluates to a set of single values rather than a set of rows). The any-or-all operator must be one of the following:

=any
<>any
<any
<=any
>any
>=any

=all
<>all
<all
<=all
>all
>=all

The != (instead of <>) can also be used to specify not equal. Include a space between the comparison operator and the keyword any or all.

A predicate that includes the any operator is true if the specified comparison is true for at least one value y in the set of values returned by the subquery. If the subquery returns no rows, the any comparison is false.

A predicate that includes the all operator is true if the specified comparison is true for all values y in the set of values returned by the subquery. If the subquery returns no rows, the all comparison is true.

The operator =any is equivalent to the operator in. For example:

select ename
from employee
where dept = any
       (select dno
       from dept
       where floor = 3);

can be rewritten as:

select ename
from employee
where dept in
       (select dno
       from dept
       where floor = 3);

The operator some is a synonym for operator any. For example:

select name
from employee
where dept = some
       (select dno
       from dept
       where floor = 3);

Exists Predicate

The exists predicate takes the following form:

[not] exists (subquery)

It evaluates to true if the set returned by the subquery is not empty. For example:

select ename
from employee
where exists
       (select *
       from dept
        where dno = employee.dept
       and floor = 3);

It is typical, but not required, for the subquery argument to exists to be of the form select *.

Is Null Predicate

The is null predicate takes the following form:

is [not] null

For example:

x is null

is true if x is a null. Use this predicate to determine whether an expression is null, because you cannot test for null by using the = comparison operator.