Search Condition
Function
A search condition is a multi-predicate boolean expression which defines whether a candidate row or group is to be included in the resultant table of the query, depending upon whether the condition equates to true.
Invocation
A search condition may appear as the body of a WHERE clause in either a query specification or a searched DELETE or UPDATE statement and as the body of a HAVING clause.
Syntax
[NOT] [PREDICATE | (SEARCH CONDITION)] [AND | OR]
where
predicate | The basis of a search condition and constitutes one of the possible search terms. All predicates equate to true, false or unknown. |
search_condition | A recursive construction enabling complex search conditions. Complex recursive constructions must be enclosed in brackets. As they are built upon predicates, search conditions also equate to true, false or unknown and constitute the other possible search term. |
NOT | An operator which negates the result of either the predicate or the included search condition. |
AND/OR | Boolean operators which combine predicates and parenthesized search conditions to form a final search condition. |
Description
If a search condition in a WHERE clause equates to true, the evaluated candidate row is considered to be a member of the resultant table. Otherwise it is rejected.
If the search condition is in a HAVING clause, the candidate group is included if the search condition equates to true.
Individual search terms of the search condition can be combined using the boolean operators AND or OR. The order of precedence of the operators is NOT followed by AND followed by OR. Operators of the same precedence are evaluated from left to right. Search terms which are search conditions are evaluated first.
Because predicates can result in the state UNKNOWN, the operators are able to evaluate `tri-state logic'. The truth tables are as follows:
NOT | TRUE | FALSE | UNKNOWN |
FALSE | TRUE | UNKNOWN |
| | | |
AND | TRUE | FALSE | UNKNOWN |
TRUE | TRUE | FALSE | UNKNOWN |
FALSE | FALSE | FALSE | FALSE |
UNKNOWN | UNKNOWN | FALSE | UNKNOWN |
| | | |
OR | TRUE | FALSE | UNKNOWN |
TRUE | TRUE | TRUE | TRUE |
FALSE | TRUE | FALSE | UNKNOWN |
UNKNOWN | TRUE | UNKNOWN | UNKNOWN |
Examples
The following example selects the IDs of all people who have a surname which starts with the letter W, and are not from the city of DERBY:
SELECT person_id
FROM person
WHERE surname LIKE 'W%' AND NOT city = 'DERBY';
The following example deletes all contract data for those persons who made a reservation on the 4th of September 1991, where the cruise does not cost more than 2000 or the amount deposited is not more than 700:
DELETE FROM contract
WHERE date_reservation = 19910904
AND ( NOT price > 2000 OR NOT amount_deposit > 700 );
The following example determines the average price of all cruises that go to MARMARIS, start from RHODOS or FETHIYE, and have a starting time of 16.00 or 17.00:
SELECT start_harbor, destination_harbor, start_time,
AVG(cruise_price)
FROM cruise
WHERE destination_harbor = 'MARMARIS'
GROUP BY start_time,start_harbor
HAVING (start_harbor = 'RHODOS' OR start_harbor = 'FETHIYE')
AND (start_time = 16 OR start_time = 17);