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 (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, and value is a value or a range of values compared against the column. The next table describes the kinds of values you can specify.
Contents
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. |
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. |
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 have not paid. Notice that there are three search conditions in the WHERE clause, which are joined together with the AND operator.
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'