Predicates

In this section: Comparison Predicates Between Predicates Like Predicates Null Predicates In Predicates Quantified Predicates Existence Predicates Search Conditions

Predicates, which evaluate to true or false, can be applied to a row or a group of rows in an answer set, and display in data manipulation statements as well as in queries. There are seven types of predicates in SQL: comparison predicates, between predicates, like predicates, null predicates, in predicates, quantified predicates, and existence predicates.

Comparison Predicates

A comparison predicate compares two values-a scalar expression, and either some other scalar expression or a subquery result.

comparison-predicate ::= <scalar-exp> <relop> <comp-tail>
comp-tail            ::= <scalar-exp> | <subquery>
relop                ::= = | <> | < | > | <= | >=

Example: Comparing Values

SALARY > THRESHOLD
DEALER_COST < (SELECT AVG(DEALER_COST) FROM CAR)
DEPARTMENT = 100

Between Predicates

A between predicate is true if the value is greater than or equal to the first limit and less than or equal to the second limit. A between predicate specifies a range comparison.

between-predicate ::= <scalar-exp> [NOT] BETWEEN <limits>
limits            ::= <scalar-exp> AND <scalar-exp>

Example: Specifying a Range

SPEED NOT BETWEEN LEGAL_LIMIT AND 70
PRICE BETWEEN GOING_RATE AND 1.1 * GOING_RATE

Like Predicates

A like predicate is used to isolate string values that conform to a given pattern.

like-predicate ::= <column-ref> [NOT] LIKE <literal>

A like predicate is true for a given row whenever the specified column (which must be of type string) matches a particular pattern. Patterns are expressed as literal strings composed of printable characters. Special symbols ("_" and "%") are used to represent arbitrary characters and character strings. To be specific, an underscore represents a single character and the percent sign represents any sequence of zero or more characters. All other characters represent themselves.

Example: Isolating String Values

LAST_NAME LIKE 'SM_TH'
CITY LIKE '%TON'
LAST_NAME NOT LIKE 'JONES%'

The first predicate would be true for such values as SMITH and SMYTH but not for JONES. The second would be true for TRENTON, PRINCETON, SCRANTON, and TON but not for PHILADELPHIA or AKRON. The third predicate excludes all rows that begin with JONES.

Since the SQL Translator permits the use of the backslash as an escape character, patterns containing control symbols can be expressed as string literals.

Example: Full Support for ESCAPE Sequence

To search for rows containing isolated embedded escape characters, issue a query employing the following predicate:

SQL
SELECT * FROM ESC WHERE FLD01 LIKE '@%%' ESCAPE '@';
END

Null Predicates

A null predicate tests for a null value.

A null predicate is NULL if the referenced column of a given row contains a null value. A null predicate is NOT NULL if the referenced column of a given row contains a non-null value.

null-predicate ::= <column-ref> IS [NOT] NULL

Example: Testing for a Null Value

LAST_NAME IS NOT NULL
ACCOUNT_ID IS NULL

In Predicates

An in predicate is true if the specified value, v, is a member of a set S. S can be defined by a static enumeration or by a subquery.

in-predicate ::= <scalar-exp> [NOT] IN <value-set>
value-set    ::= <subquery> | <enumeration>
enumeration  ::= ( <literal> [,<literal>]* )

Example: Testing Whether a Value Is a Member of a Set

MAKE_OF_CAR IN ('CHEVROLET', 'FORD', 'PLYMOUTH')
ACCOUNT NOT IN (SELECT ACCT_NUMBER FROM CUSTOMER WHERE STATUS = 'DEADBEAT')

Quantified Predicates

Quantified predicates, which resemble the comparison predicates, produce a true result when all (any) of the values generated by a given subquery satisfy the stipulated comparison. Although three quantifiers-ALL, ANY, and SOME-are permitted, ANY and SOME are synonymous.

all-or-any-pred ::= <scalar-exp> <quant-op> <subquery>
quant-op        ::= <relop> <quantifier>
quantifier      ::= ALL | ANY | SOME

Example: Generating Values That Satisfy a Specified Comparison

SOFTWARE > ALL (SELECT SOFTWARE FROM PRODUCTS WHERE PROD-NAME = 'FOCUS')
RATING > ANY (SELECT RATING FROM PRODUCTS WHERE COST > 5.00)

The first predicate searches for FOCUS, alone or in a longer string, such as FOCUS Six for Windows. The second is true for any RATING exceeding that of at least one $5.00 product on file.

Existence Predicates

An existence predicate produces a true result if the given subquery has a significant (for example, non-null) answer set.

existence-predicate ::= [NOT] EXISTS <subquery>

Example: Testing Whether a Subquery Has a Significant Answer Set

EXISTS (SELECT * FROM CANDIDATES WHERE EYE_COLOR = 'Blue')

Search Conditions

Predicates linked together by logical operators-AND, OR, and NOT-form search conditions.

search-condition ::= <boolean-term> |
                     <search-condition> OR <boolean-term>
boolean-term     ::= <boolean-factor> |
                     <boolean-term> AND <boolean-factor>
boolean-factor   ::= [NOT] <boolean-primary>
boolean-primary  ::= <predicate> | ( <search-condition> )

Search conditions are evaluated by systematically applying logical operators to the results generated by their constituent predicates. If no logical operators are present, a search condition is nothing more than a predicate and is evaluated accordingly. When logical operators are present, they are applied in a specific order: NOT before AND, AND before OR. Parenthesized search conditions are evaluated before logical external operators are taken into consideration.

Example: Specifying a Search Condition

HEIGHT > 6 AND WEIGHT < 150 OR WEIGHT > 200
HEIGHT > 6 AND (WEIGHT < 150 OR WEIGHT > 200)

The first search condition produces a true result either for six footers weighing less than 150 pounds or for candidates weighing more than 200 pounds regardless of height. The second search condition would be true for any six footer weighing less than 150 pounds or more than 200 pounds. The parentheses in the second example reverse the implicit order in which the logical operators are applied.