Predicates let you construct conditions so that only those rows that meet these conditions are processed. Basic predicates are discussed in Selecting Rows. IN, BETWEEN, LIKE, EXISTS and quantified predicates are discussed in this section.
Use the IN predicate to compare a value with several other values. For example:
SELECT NAME
FROM STAFF
WHERE DEPT IN (20, 15)
This example is equivalent to:
SELECT NAME
FROM STAFF
WHERE DEPT = 20 OR DEPT = 15
You can use the IN and NOT IN operators when a subquery returns a set of values. For example, the following query lists the surnames of employees responsible for projects MA2100 and OP2012:
SELECT LASTNAME
FROM EMPLOYEE
WHERE EMPNO IN
(SELECT RESPEMP
FROM PROJECT
WHERE PROJNO = 'MA2100'
OR PROJNO = 'OP2012')
The subquery is evaluated once, and the resulting list is substituted directly into the outer-level query. For example, the subquery above selects employee numbers 10 and 330, the outer-level query is evaluated as if its WHERE clause were:
WHERE EMPNO IN (10, 330)
The list of values returned by the subquery can contain zero, one, or more values.
Use the BETWEEN predicate to compare a value with a range of values. The range is inclusive and it considers the two expressions in the BETWEEN predicate for the comparisons.
The following example finds the names of employees who earn between $10, 000 and $20, 000:
SELECT LASTNAME
FROM EMPLOYEE
WHERE SALARY BETWEEN 10000 AND 20000
This is equivalent to:
SELECT LASTNAME
FROM EMPLOYEE
WHERE SALARY >= 10000 AND SALARY <= 20000
The next example finds the names of employees who earn less than $10, 000 or more than $20, 000:
SELECT LASTNAME
FROM EMPLOYEE
WHERE SALARY NOT BETWEEN 10000 AND 20000
Use the LIKE predicate to search for strings that have certain patterns. The pattern is specified through percentage signs and underscores.
The following example selects employee names that are seven letters long starting with the letter 'S':
SELECT NAME
FROM STAFF
WHERE NAME LIKE 'S_ _ _ _ _ _'
The next example selects names of employees that do not start with the letter 'S':
SELECT NAME
FROM STAFF
WHERE NAME NOT LIKE 'S%'
You can use a subquery to test for the existence of a row that satisfies some condition. In this case, the subquery is linked to the outer-level query by the predicate EXISTS or NOT EXISTS.
When you link a subquery to an outer query by an EXISTS predicate, the subquery does not return a value. Rather, the EXISTS predicate is true if the answer set of the subquery contains one or more rows, and false if it contains no rows.
The EXISTS predicate is often used with correlated subqueries. The example below lists the departments that currently have no entries in the PROJECT table:
SELECT DEPTNO, DEPTNAME
FROM DEPARTMENT X
WHERE NOT EXISTS
(SELECT *
FROM PROJECT
WHERE DEPTNO = X.DEPTNO)
ORDER BY DEPTNO
You may connect the EXISTS and NOT EXISTS predicates to other predicates by using AND and OR in the WHERE clause of the outer-level query.
A quantified predicate compares a value with a collection of values. If a fullselect returns more than one value, you must modify the comparison operators in your predicate by attaching the suffix ALL, ANY, or SOME. These suffixes determine how the set of values returned is to be treated in the outer-level predicate. The > comparison operator is used as an example (the remarks below apply to the other operators as well):
The following example uses a subquery and a > ALL comparison to find the name and profession of all employees who earn more than all managers:
SELECT LASTNAME, JOB
FROM EMPLOYEE
WHERE SALARY > ALL
(SELECT SALARY
FROM EMPLOYEE
WHERE JOB='MANAGER')
For more information on predicates and operators, refer to the SQL Reference.