Structure of a Search Condition

Go Up to Restricting Row Retrieval with WHERE

A typical simple search condition compares a value in one column against a constant or a value in another column. For example, the following WHERE clause tests a row to see if a field equals a hard-coded constant:

WHERE DEPARTMENT = 'Publications'

This search condition has three elements: a column name, a comparison operator (the equal sign), and a constant. Most search conditions are more complex than this. They involve additional elements and combinations of simple search conditions. The following table describes expression elements that can be used in search conditions:

Elements of WHERE clause SEARCH conditions

Element	Description
Column names	Columns from tables listed in the FROM clause, against which to search or compare values.
Host-language variables	Program variables containing changeable values. When used in a SELECT, host-language variables must be preceded by a colon (:).
Constants	Hard-coded numbers or quoted strings, like 507 or “Tokyo”.
Concatenation operators	||, used to combine character strings.
Arithmetic operators	+, –, *, and /, used to calculate and evaluate search condition values.
Logical operators	Keywords, NOT, AND, and OR, used within simple search conditions, or to combine simple search conditions to make complex searches. A logical operation evaluates to true or false.
Comparison operators	<, >, <=, >=, =, and <>, used to compare a value on the left side of the operator to another on the right side. A comparative operation evaluates to True or False. Other, more specialized comparison operators include ALL, ANY, BETWEEN, CONTAINING, EXISTS, IN, IS, LIKE, NULL, SINGULAR, SOME, and STARTING WITH. These operators can evaluate to True, False, or Unknown.
COLLATE clause	Comparisons of CHAR and VARCHAR values can sometimes take advantage of a COLLATE clause to force the way text values are compared.
Stored procedures	Reusable SQL statement blocks that can receive and return parameters, and that are stored as part of a database metadata. For more information about stored procedures in queries, see Working with Stored Procedures.
Subqueries	A SELECT statement nested within the WHERE clause to return or calculate values against which rows searched by the main SELECT statement are compared. For more information about subqueries, see Using Subqueries.
Parentheses	Group related parts of search conditions which should be processed separately to produce a single value which is then used to evaluate the search condition. Parenthetical expressions can be nested.

Complex search conditions can be constructed by combining simple search conditions in different ways. For example, the following WHERE clause uses a column name, three constants, three comparison operators, and a set of grouping parentheses to retrieve only those rows for employees with salaries between $60,000 and $120,000:

WHERE DEPARTMENT = 'Publications' AND
(SALARY > 60000 AND SALARY < 120000)

Search conditions in WHERE clauses often contain nested SELECT statements, or subqueries. For example, in the following query, the WHERE clause contains a subquery that uses the aggregate function, AVG(), to retrieve a list of all departments with bigger-than-average salaries:

EXEC SQL
DECLARE WELL_PAID CURSOR FOR
SELECT DEPT_NO
INTO :wellpaid
FROM DEPARTMENT
WHERE SALARY > (SELECT AVG(SALARY) FROM DEPARTMENT);

For a general discussion of building search conditions from SQL expressions, see Understanding SQL Expressions. For more information about using subqueries to specify search conditions, see Using Subqueries. For more information about aggregate functions, see Retrieving Aggregate Column Information.