Search Conditions

The text following the WHERE keyword is called a search condition, because a SELECT statement searches for rows that meet the condition. Search conditions consist of a column name (such as “last_name”), an operator (such as “=”), and a value (such as “Green”). Thus, WHERE clauses have the following general form:

WHERE column_name operator value

In general, column_name is the column name in the table being queried, operator is a comparison operator (Table 2), and value is a value or a range of values compared against the column. Table 3 describes the kinds of values you can specify.

Comparison Operators

Search conditions use the following operators. Note that for two-character operators, there is no space between the operators.

Operator	Description
Comparison operators	Used to compare data in a column to a value in the search condition. Examples include <, >, <=, >=, =, !=, and <>. Other operators include BETWEEN, CONTAINING, IN, IS NULL, LIKE, and STARTING WITH.
Arithmetic operators	Used to calculate and evaluate search condition values. The operators are +, -, *, and /.
Logical operators	Used to combine search conditions or to negate a condition. The keywords are NOT, AND, and OR.

Table 2: Search Condition Operators Operator Description

Search Condition Values

The values in a search condition can be literal, or they can be calculated (derived). In addition, the value can be the return value of a subquery. A subquery is a nested SELECT statement. Values that are text literals must be placed in quotes. The approaching standard will require single quotes. Numeric literals must not be quoted.

Important: String comparisons are case sensitive.

Type of Values	Description
Literal values	Numbers and text strings whose value you want to test literally; for example, the number 1138 or the string 'Smith'
Derived values	Functions and arithmetic expressions; for example salary * 2 or last_name || first_name
Subqueries	A nested SELECT statement that returns one or more values. The returned values are used in testing the search condition

Table 3: Types of values used in search conditions

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 does not meet the conditions specified in the WHERE clause.
• Unknown: A field in the WHERE clause contains an NULL state that could not be evaluated.

Find the Deadbeats

Execute the following SELECT statement into the SQL Statement Area of IBConsole to query the Sales table for all the customers who ordered before January 1, 1994, received their shipment, and still haven’t paid. Notice that there are three search conditions in the WHERE clause, which are joined together with the AND operator.

:<span style="color:blue"><code>SELECT * from Sales
WHERE order_date < '1-JAN-1994' AND order_status = 'shipped' AND paid = 'n'

You should get two rows, one for PO number V93F3088 and one for PO number V93N5822.

Negation

You can negate any expression with the negation operators !, ^, and ~. These operators are all synonyms for NOT. Suppose you just want to find what customers are not in the United States. Execute the following SELECT statement:

SELECT customer, country FROM Customer
WHERE NOT country = 'USA'

You should get a list of ten customers.

There are other ways to achieve exactly this result. To prove to yourself that these all produce the same results as the previous query, execute each of the following forms of it:

SELECT customer, country FROM Customer WHERE country != 'USA' SELECT customer, country FROM Customer WHERE country ~= 'USA' SELECT customer, country FROM Customer WHERE country ^= 'USA'</source>

Advance To:

Pattern Matching