Search conditions are used to restrict the number of rows affected by a table operation or SQL statement of a routine. Only the rows that satisfy the specified search condition are taken into account. You may specify search conditions for DELETE, MERGE, UPDATE and SELECT, when joining tables (join expression) and in a conditional expression (CASE expression). You can specify search conditions in table and column constraints in order to formulate integrity constraints. Search conditions also occur in the case of statements in routines.
You define a search condition in a WHERE, HAVING, ON, CHECK or WHEN clause or in a control statement of a routine, and it may be used in the following statements and expressions or query expressions:
WHERE clause
DELETE statement
SELECT statement
SELECT expression for CREATE VIEW, DECLARE, INSERT
UPDATE statement
HAVING clause
SELECT statement
SELECT expression for CREATE VIEW, DECLARE, INSERT
ON clause
MERGE statement
Join expression
CHECK condition in the CREATE TABLE or ALTER TABLE statement
WHEN clause in a CASE-expression with search condition
IF, CASE, REPEAT, or WHILE statement in a routine
A search condition consists of predicates and can include logical operators. The predicates are the operands of the logical operators.
A search condition is evaluated by applying the operators to the results of the operands. The result is one of the truth values true, false or unknown.
The operands are not evaluated in a predefined order. In certain cases, an operand is not calculated if it is not required for calculating the total result.
search_condition ::= {
praedicate |
search_condition { AND | OR } search_condition |
NOT search_condition |
( search_condition )
}
predicate
Predicate
AND
Logical AND
Result for Op1 AND Op2
Op1 true | Op1 false | Op1 unknown | |
Op2 true | true | false | unknown |
Op2 false | false | false | false |
Op2 unknown | unknown | false | unknown |
Table 20: Logical operator AND
OR
Logical OR
Result Op1 OR Op2
Op1 true | Op1 false | Op1 unknown | |
Op2 true | true | true | true |
Op2 false | true | false | unknown |
Op2 unknown | true | unknown | unknown |
Table 21: Logical operator OR
NOT
Negation
Result for NOT Op
NOT Op | |
Op true | false |
Op false | true |
Op unknown | unknown |
Table 22: Logical operator NOT
Precedence
Expressions enclosed in parentheses have highest precedence.
NOT takes precedence over AND and OR.
AND takes precedence over OR.
Operators with the same precedence level are applied from left to right.
Examples
Select all orders with company placed after the specified date in the tables ORDERS and CUSTOMERS.
SELECT o.order_num, c.company, o.order_text, o.order_date FROM orders o, customers c WHERE o.order_date > DATE '<date>' AND o.cust_num = c.cust_num order_ company order_text order_date num 250 The Poodle Parlor Mailmerge intro 20010-03-03 251 The Poodle Parlor Customer administration 2010-05-02 300 Login GmbH Network test/comparison 2010-02-14 305 The Poodle Parlor Staff training 2010-05-02
Delete all the items from the ITEMS table whose price is less than 500.00 and whose item name starts with the letter H:
DELETE FROM items WHERE price < 500.00 AND item_name LIKE 'H%'
Select all the orders from the SERVICE table that were filled in the specified period or for which no training was given or no training documentation or manual created.
SELECT order_num, service_date, service_text FROM service
WHERE service_date BETWEEN DATE '2013-04-01' AND DATE '2013-04-30'
OR service_text NOT IN('Training','Training documentation','Manual')
service_ order_ service_date service_text
num num
1 200 2013-04-19 Training documentation
2 200 2013-04-22 Training
3 200 2013-04-23 Training
4 211 2013-01-20 Systems analysis
5 211 2013-01-28 Database design
6 211 2013-02-15 Copies/transparencies
10 250 2013-02-21 Travel expenses