InterBase Quick Start: Part IV - 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, an operator, and a value. The
WHERE clause has the following general form:
WHERE column_name operator value
column_nameis the column name in the table that you query from.
operatoris a comparison operator.
valueis a value or a range of values to compare against the column.
Search conditions use the following operators:
Compare data in a column to a value in the search condition.
Calculate and evaluate search condition values.
Combine search conditions or to negate a condition.
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
You must specify values that are text literals in single quotes.
|Type of Values
Numbers and text strings whose value you want to test literally.
Functions and arithmetic expressions.
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
False: A row does not meet the conditions specified in the
Unknown: A field in the
WHEREclause contains an
NULLstate that could not be evaluated.
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
WHERE order_date < '1-JAN-1994'
AND order_status = 'shipped'
AND paid = 'n'
The result returns two rows:
You can negate any expression with negation operators:
~. These operators are all synonyms for
for example, if you want to find customers that are not from the United States you can do that using the following
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:
WHERE country != 'USA'
WHERE country ~= 'USA'
WHERE country ^= 'USA'