InterBase Quick Start: Part IV - Search Conditions

Go Up to InterBase Quick Start: Part IV - Using the WHERE Clause

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, an operator, and a value. The WHERE clause has the following general form:

WHERE column_name operator value

• column_name is the column name in the table that you query from.
• operator is a comparison operator.
• value is a value or a range of values to compare against the column.

Comparison Operators

Search conditions use the following operators:

Operator	Description	Examples
Comparison operators	Compare data in a column to a value in the search condition.	<, >, {{{1}}}, {{{1}}}, {{{1}}}, {{{1}}}, <>, BETWEEN, CONTAINING, IN, IS NULL, LIKE, and STARTING WITH.
Arithmetic operators	Calculate and evaluate search condition values.	+, -, *, and /.
Logical operators	Combine search conditions or to negate a condition.	NOT, AND, and OR.

Search Condition Values

You can use literal or calcualted (derived) values in a search condition. Additionally, the value can be the return value of a subquery. A subquery is a nested SELECT statement. You must specify values that are text literals in single quotes.

Type of Values	Description	Examples
Literal values	Numbers and text strings whose value you want to test literally.	The number 1138 or the string 'Smith'. Note: String comparisons are case sensitive.
Derived values	Functions and arithmetic expressions.	salary * 2 or last_name.
Subqueries	A nested SELECT statement that returns one or more values.	The result represents the search condition.

When you compare a row to a search condition, the result is one of three values:

• 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.

Using a Complex WHERE Clause

Enter the following statement to query the Sales table for all the customers who made an order before January 1, 1994, received the order, and have not paid yet. There are three search conditions in the WHERE clause, joined together with the AND operator.

SELECT *
FROM   Sales
WHERE  order_date < '1-JAN-1994'
       AND order_status = 'shipped'
       AND paid = 'n'

The result returns two rows:

Using Negation

You can negate any expression with negation operators: !, ^, and ~. These operators are all synonyms for NOT. for example, if you want to find customers that are not from the United States you can do that using the following SELECT statement:

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

The result shows a list of 10 customers:

There are other ways to achieve this exact result. To confirm that, enter each of the queries below and compare the results:

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

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

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

Advance To:

• Pattern Matching