Filtering Rows Using the WHERE Clause | Oracle Database 10g SQL (Osborne ORACLE Press Series)

You can use the WHERE clause in a SELECT statement to filter the rows returned from the database. This is very important as Oracle has the capacity to store large numbers of rows in a table and you may only be interested in a very small subset of those rows. You place the WHERE clause after the FROM clause:

 SELECT  FROM WHERE ;

In the following example, the WHERE clause is used to retrieve the row from the customers table that has the value 2 stored in the customer_id column:

  SELECT *   FROM customers   WHERE customer_id = 2;  CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE ----------- ---------- ---------- --------- ------------  2 Cynthia Green 05-FEB-68 800-555-1212

Notice this example uses the equality operator ( = ) in the WHERE clause. The equality operator is just one of the comparison operators, which you ll learn about next .

Using Comparison Operators

There are many other comparison operators that you can use in a WHERE clause besides the equality operator. The following table lists the comparison operators.

Operator	Description
=	Equal
<> or !=	Not equal
<	Less than
>	Greater than
<=	Less than or equal
>=	Greater than or equal
ANY	Compares one value with any value in a list
ALL	Compares one value with all values in a list

The following SELECT statement uses the not equal (<>) operator in the WHERE clause to retrieve the rows from the customers table whose customer_id is not equal to 2:

  SELECT *   FROM customers   WHERE customer_id  <>  2;  CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE ----------- ---------- ---------- --------- ------------  1 John Brown 01-JAN-65 800-555-1211  3 Steve White 16-MAR-71 800-555-1213  4 Gail Black 800-555-1214  5 Doreen Blue 20-MAY-70

The next example retrieves the customer_id and name columns from the product s table where the customer_id column is greater than 8 using the > operator:

  SELECT customer_id, name   FROM products   WHERE customer_id  >  8;  CUSTOMER_ID NAME ----------- ----------------  9 Classical Music  10 Pop 3  11 Creative Yell  12 My Front Line

You use the ANY operator in a WHERE clause to compare a value with any of the values in a list. You must place an = , <>, <, >, < = , or > = operator before ANY . The following SELEC T statement uses the ANY operator to retrieve rows from the customers table where the value in the customer_id column is greater than any of the values 2, 3, or 4:

  SELECT *   FROM customers   WHERE customer_id  >  ANY (2, 3, 4);  CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE ----------- ---------- ---------- --------- ------------  5 Doreen Blue 20-MAY-70  4 Gail Black 800-555-1214  3 Steve White 16-MAR-71 800-555-1213

Notice that customer #3 is included in the results because 3 is greater than 2.

You use the ALL operator in a WHERE clause to compare a value with all of the values in a list. You must place an = , <>, <, >, < = , or > = operator before ALL . The following SELEC T statement uses the ALL operator to retrieve rows from the customers table where the value in the customer_id column is greater than all of the values 2, 3, and 4:

  SELECT *   FROM customers   WHERE customer_id  >  ALL (2, 3, 4);  CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE ----------- ---------- ---------- --------- ------------  5 Doreen Blue 20-MAY-70

Notice that only customer #5 is returned because 5 is greater than 2, 3, and 4. There are no rows in the customers table with a customer_id greater than 5 and so only one row is returned.

Using the SQL Operators

The SQL operators allow you to limit rows based on pattern matching of strings, lists of values, ranges of values, and null values. The SQL operators are listed in the following table:

Operator	Description
LIKE	Matches patterns in strings
IN	Matches lists of values
BETWEEN	Matches a range of values
IS NULL	Matches null values
IS NAN	New for Oracle10 g . Matches the NaN special value, which means not a number
IS INFINITE	New for Oracle10 g . Matches infinite BINARY_FLOAT and BINARY_DOUBLE values

You can also use the NOT operator to reverse the meaning of LIKE, IN, BETWEEN, and IS NULL:

NOT LIKE
NOT IN
NOT BETWEEN
IS NOT NULL
IS NOT NAN
IS NOT INFINITE

The following sections cover the LIKE , IN , and BETWEEN operators.

Using the LIKE Operator

You use the LIKE operator in a WHERE clause to see if any of the character strings in a text column match a pattern that you specify. You specify patterns using a combination of normal characters and the following two wildcard characters :

Underscore character ( _ ) Matches one character in a specified position
Percent character ( % ) Matches any number of characters beginning at the specified position

The following SELECT statement uses the LIKE operator with the pattern ˜_o% applied to the first_name column of the customers table The underscore character (_) before the o matches any one character in the first position of the column value, and the percent character ( % ) matches any characters following the o :

  SELECT *   FROM customers   WHERE first_name LIKE '_o%';  CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE ----------- ---------- ---------- --------- ------------  1 John Brown 01-JAN-65 800-555-1211  5 Doreen Blue 20-MAY-70

As you can see, two records are retrieved, because the strings John and Doreen both have o as the second character.

The following example uses NOT LIKE to reverse the rows retrieved by the previous query:

  SELECT *   FROM customers   WHERE first_name NOT LIKE '_o%';  CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE ----------- ---------- ---------- --------- ------------  2 Cynthia Green 05-FEB-68 800-555-1212  3 Steve White 16-MAR-71 800-555-1213  4 Gail Black 800-555-1214

As expected, all rows other than the rows in the previous example are retrieved.

If you need to perform a text match on the actual underscore or percent characters in a string, you can use the ESCAPE option. The following example retrieves the products whose name contains the string a_product :

 SELECT first_name FROM customers WHERE first_name LIKE '%a\_product%' ESCAPE '\';

The ESCAPE option specifies that the backslash character precedes any wildcard characters used with the LIKE operator ”in this example, the underscore ( _ ) is the wildcard used. The underscore is then used in the text match, rather than being treated as a wildcard character as would otherwise be the case.

Using the IN Operator

You can use the IN operator in a WHERE clause to select only those rows whose column value is in a list that you specify. The following SELECT statement uses the IN operator to retrieve rows from the customers table where the value in the customer_id column is 2, 3, or 5:

  SELECT *   FROM customers   WHERE customer_id IN (2, 3, 5);  CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE ----------- ---------- ---------- --------- ------------  2 Cynthia Green 05-FEB-68 800-555-1212  3 Steve White 16-MAR-71 800-555-1213  5 Doreen Blue 20-MAY-70

NOT IN reverses the rows selected by IN ”if IN were replaced by NOT IN for the previous example, all rows except those shown above would be retrieved.

Note	NOT IN returns false if a value in the list is null.

The following example shows no rows are returned when NOT IN processes a null in a list of values:

  SELECT *   FROM customers   WHERE customer_id NOT IN (2, 3, 5, NULL);  no rows selected

Using the BETWEEN Operator

You use the BETWEEN operator in a WHERE clause to select rows whose column value is inclusive within a specified range. The following example uses the BETWEEN operator to retrieve rows from the customers table where the customer_id column is between 1 and 3:

  SELECT *   FROM customers   WHERE customer_id BETWEEN 1 AND 3;  CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE ----------- ---------- ---------- --------- ------------  1 John Brown 01-JAN-65 800-555-1211  2 Cynthia Green 05-FEB-68 800-555-1212  3 Steve White 16-MAR-71 800-555-1213

The range specified with the BETWEEN operator is inclusive so the rows where the custome r_id column is equal to 1, 2, or 3 are retrieved. As you d expect, NOT BETWEEN reverses the rows retrieved.

Using the Logical Operators

There are three logical operators that may be used in a WHERE clause. The logical operators allow you to limit rows based on logical conditions. The logical operators are listed in the following table:

Operator	Description
x AND y	Returns true when both x and y are true
x OR y	Returns true when either x or y is true
NOT x	Returns true if x is false, and returns false if x is true

The following example illustrates the use of the AND operator to retrieve rows from the customers table where both of the following conditions are met:

The dob column is greater than January 1, 1970.

The customer_id column is greater than 3.

  SELECT *   FROM customers   WHERE dob  >  '01-JAN-1970'   AND customer_id  >  3;  CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE ----------- ---------- ---------- --------- ------------  5 Doreen Blue 20-MAY-70

The following example illustrates the use of the OR operator to retrieve rows from the customer s table where either of the following conditions is met:

The dob column is greater than January 1, 1970.

The customer_id column is greater than 3.

  SELECT *   FROM customers   WHERE dob  >  '01-JAN-1970'   OR customer_id  >  3;  CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE ----------- ---------- ---------- --------- ------------  3 Steve White 16-MAR-71 800-555-1213  4 Gail Black 800-555-1214  5 Doreen Blue 20-MAY-70

You can use the logical operators AND and OR to combine expressions in a WHERE clause.

Understanding Operator Precedence

If you combine AND and OR in the same expression, the AND operator takes precedence over the OR operator (which means it s executed first). The comparison operators take precedence over AND . Of course, you can override these using parentheses.

The following example retrieves rows from the customers table where either of the following two conditions is met:

The dob column is greater than January 1, 1970.

The customer_id column is less than 2 and the phone column has 1211 at the end.

  SELECT *   FROM customers   WHERE dob  >  '01-JAN-1970'   OR customer_id  <  2   AND phone LIKE '%1211';  CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE ----------- ---------- ---------- --------- ------------  1 John Brown 01-JAN-65 800-555-1211  3 Steve White 16-MAR-71 800-555-1213  5 Doreen Blue 20-MAY-70

As I mentioned, the AND operator takes precedence over OR , so you can think of the previous query s WHERE clause as follows :

 dob > '01-JAN-1970' OR (customer_id < 2 AND phone LIKE '%1211')

Therefore, customers #1, #3, and #5 are displayed.