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:


 

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, then 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, then 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

Limitations:

None.

ANSI Specifics:

None.

CONNX Specifics:

None.

 

Example:

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);