In this section: |
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.
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 ::= = | <> | < | > | <= | >=
SALARY > THRESHOLD
DEALER_COST < (SELECT AVG(DEALER_COST) FROM CAR)
DEPARTMENT = 100
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>
SPEED NOT BETWEEN LEGAL_LIMIT AND 70
PRICE BETWEEN GOING_RATE AND 1.1 * GOING_RATE
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.
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.
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
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
LAST_NAME IS NOT NULL
ACCOUNT_ID IS NULL
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>]* )
MAKE_OF_CAR IN ('CHEVROLET', 'FORD', 'PLYMOUTH')
ACCOUNT NOT IN (SELECT ACCT_NUMBER FROM CUSTOMER WHERE STATUS = 'DEADBEAT')
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
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.
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>
EXISTS (SELECT * FROM CANDIDATES WHERE EYE_COLOR = 'Blue')
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.
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.
iWay Software |