Testing for a Single Condition
Retrieving Data with Queries
Querying and Manipulating Data 2-9
The
WHERE
clause can test a single condition at a time, or combine multiple tests using
the
AND
clause.
shows how to use the
WHERE
clause to return the column values that are
restricted to a single department, which has
90
for its
department_id
.
Example 25 Testing for a Single Condition
SELECT first_name "First", last_name "Last"
FROM employees
WHERE department_id=90;
The results of the query appear.
First Last
-------------------- -------------------------
Steven King
Neena Kochhar
Lex De Haan
3 rows selected
shows how to use the
WHERE ... AND
clause to return the rows that are
restricted to two separate condition, to match a salary that is greater or equal to
11,000
, and an assigned (not null) commission rate.
Example 26 Testing Multiple Conditions
SELECT first_name "First", last_name "Last",
SALARY "Salary", COMMISSION_PCT "%"
FROM employees
WHERE salary >=11000 AND commission_pct IS NOT NULL;
The results of the query appear.
First Last Salary %
-------------------- --------------------- -------------------- -----
John Russell 14000 0.4
Karen Partners 13500 0.3
Alberto Errazuriz 12000 0.3
...
6 rows selected
<
Tests for less than
<=
Tests for less than or equal
BETWEEN
a
AND
b
Tests for a fit in the range between two values, inclusive
LIKE
Tests for a match in a string, using the wildcard symbols (
%
)
for zero or multiple characters, or underscore (
_
) for a single
character
IN()
Tests for a match in a specified list of values
NOT IN()
Tests that there is no match in a specified list of values
IS NULL
Tests that the value is null
IS NOT NULL
Tests that the value is not null
Comparison Operator
Definition
