Restricting Row Retrieval with WHERE

Go Up to Understanding Data Retrieval with SELECT

In a query, the WHERE clause specifies the data a row must (or must not) contain to be retrieved.

In singleton selects, where a query must only return one row, WHERE is mandatory unless a select procedure specified in the FROM clause returns only one row itself.

In SELECT statements within DECLARE CURSOR statements, the WHERE clause is optional. If the WHERE clause is omitted, a query returns all rows in the table. To retrieve a subset of rows in a table, a cursor declaration must include a WHERE clause.

The simple syntax for WHERE is:

WHERE <search_condition>

For example, the following simple WHERE clause tests a row to see if the
DEPARTMENT column is “Publications”:

WHERE DEPARTMENT = 'Publications'

What is a Search Condition?

Because the WHERE clause specifies the type of data a query is searching for it is often called a search condition. A query examines each row in a table to see if it meets the criteria specified in the search condition. If it does, the row qualifies for retrieval.

When a row is compared to a search condition, one of three values is returned:

• True: A row meets the conditions specified in the WHERE clause.
• False: A row fails to meet the conditions specified in the WHERE clause.
• Unknown: A column tested in the WHERE clause contains an unknown value that could not be evaluated because of a NULL comparison.

Most search conditions, no matter how complex, evaluate to True or False. An expression that evaluates to True or False—like the search condition in the WHERE clause—is called a boolean expression.

Structure of a Search Condition

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.

Collation Order in Comparisons

When CHAR or VARCHAR values are compared in a WHERE clause, it can be necessary to specify a collation order for the comparisons if the values being compared use different collation orders.

To specify the collation order to use for a value during a comparison, include a COLLATE clause after the value. For example, in the following WHERE clause fragment from an embedded application, the value to the left of the comparison operator is forced to be compared using a specific collation:

WHERE LNAME COLLATE FR_CA = :lname_search;

For more information about collation order and a list of collations available to InterBase, see the Data Definition Guide.

Advance To:

• Sorting Rows with ORDER BY