SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
S-6
Considerations—Search Conditions
Considerations—Search Conditions

SQL evaluates search conditions in this order: first to last; predicates within
parentheses; NOT, AND, and OR.

Within a search condition, a reference to a column refers to the value of that
column in the row evaluated by the search condition.

If a search condition contains a predicate of the form
expression comparison-operator subquery
and the subquery returns no values, the predicate evaluates to null.
If you include a subquery in a search condition, SQL applies the subquery to each
row of the table that is the result of the previous clauses, the uses that result to
evaluate the search condition in relation to a specific row.

A statement that contains a search condition operates on a row only if that row
satisfies the search condition. For example, in a DELETE statement, any row that
satisfies the search condition specified in the WHERE clause is deleted. In a
SELECT statement, from each row or group of rows that satisfies the search
condition, the columns specified in the select list are returned.
A search condition connected by the OR operator might execute successfully
although it includes a predicate that can evaluate to false or null. If any predicate is
true, the values of the remaining predicates are irrelevant.

If a search condition contains a predicate of the form
expression comparison-operator subquery
and the subquery returns no values, the predicate evaluates to null.
For example, this predicate evaluates to null because the subquery returns no
value (there is no part number with more than 1500 units in stock):
PARTNUM = (SELECT PARTNUM
FROM ODETAIL
WHERE QTY_ORDERED > 1500)
Examples—Search Conditions

This example searches for values in rows where the quantity is less than 9, the
delivery date is before November 2, 1991 and the order number in the ORDERS
table equals the order number in the ODETAIL table:
QTY_ORDERED < 9 AND DELIV_DATE <= 911101
AND ORDERS.ORDERNUM = ODETAIL.ORDERNUM