TurboSQL Guide

WHERE Clause

Previous  Top  Next


Specifies filtering conditions for a SELECT or UPDATE statement.

WHERE predicates

Description

Use a WHERE clause to limit the effect of a SELECT or UPDATE statement to a subset of rows in the table. Use of a WHERE clause is optional.

The value for a WHERE clause is one or more logical expressions, or predicates, that evaluate to TRUE or FALSE for each row in the table. Only those rows where the predicates evaluate to TRUE are retrieved by a SELECT statement or modified by an UPDATE statement. For example, the SELECT statement below retrieves all rows where the STATE column contains a value of 'CA'.

SELECT company, state
FROM customer
WHERE state = 'CA'

Multiple predicates must be separated by one of the logical operators OR or AND. Each predicate can be negated with the NOT operator. Parentheses can be used to isolate logical comparisons and groups of comparisons to produce different row evaluation criteria. For example, the SELECT statement below retrieves all rows where the STATE column contains a value of "CA" and those with a value of "HI".

SELECT company, state
FROM customer
WHERE (state = 'CA') OR (state = 'HI')

The SELECT statement below retrieves all rows where the SHAPE column is round or square, but only if the COLOR column also contains red. It would not retrieve rows where, for example, the SHAPE is round and the COLOR blue.

SELECT shape, color, cost
FROM objects
WHERE ((shape = 'round') OR (shape = 'square')) AND (color = 'red')

But without the parentheses to override the order of precedence of the logical operators, as in the statement that follows, the results are very different. This statement retrieves the rows where the SHAPE is round, regardless of the value in the COLOR column. It also retrieves rows where the SHAPE column is square, but only when the COLOR column contains red. Unlike the preceding variation of this statement, this one would retrieve rows where the SHAPE is round and the COLOR blue.

SELECT shape, color, cost
FROM objects
WHERE shape = 'round' OR shape = 'square' AND color = 'red'

Note

A WHERE clause filters data prior to the aggregation of a GROUP BY clause. For filtering based on aggregated values, use a HAVING clause.

Applicability

SELECT, UPDATE, DELETE