SQL/MP Query Guide

Retrieving Data: How to Write Queries
HP NonStop SQL/MP Query Guide—524488-003
1-30
Defining Predicates
WHERE SALARY > (SELECT AVG(SALARY)
FROM EMPLOYEE EMP2) ;
Defining Predicates
A predicate is a condition that a row must satisfy to be returned to the application. For
example, “ITEM_NO > 10” is the predicate in this query:
SELECT ITEM_NAME, RETAIL_PRICE
FROM INVNTRY
WHERE ITEM_NO > 10 ;
You can use these predicates to specify a search condition:
•
Comparison predicates (also known as relational predicates), which allow you to
compare values:
<> < <= = >= >
•
BETWEEN predicates, used to perform range comparisons
•
LIKE predicates, used to match patterns in character strings
•
IN predicates, used to compare one value with a list of values
•
EXISTS predicates, used to check whether at least one value satisfies a given
condition
•
SOME, ANY, and ALL predicates, known as quantified predicates, which enable
you to quantify the number of values to be compared
•
IS NULL predicates, used to check whether a given value is null
You can combine different kinds of predicates with the operators AND and OR, or use
the NOT operator to reverse the truth value of a predicate. In addition, you can use
aggregate functions such as MAX and MIN as part of a predicate.
The examples under Comparison Predicate
refer to a database that consists of two
tables, EMPLOYEE and DEPT, as shown in Example 1-8
.