Oracle WHERE Clause

Summary: In this tutorial, you will learn how to use the Oracle WHERE clause to specify a condition for filtering rows returned by a query.

Introduction to Oracle WHERE clause #

WHERE is a clause of an SELECT statement. A WHERE clause allows you to specify a condition for filtering rows returned by the SELECT clause.

Here’s the syntax of the SELECT statement with a WHERE clause:

SELECT
    select_list
FROM
    table_name
WHERE
    search_condition
ORDER BY
   sort_expression;Code language: SQL (Structured Query Language) (sql)

The WHERE clause appears after the FROM clause, but before the ORDER BY clause.

In the WHERE clause, you specify a condition (search_condition) that filter rows. If a row meets the condition, the WHERE clause include it in the final result set.

When evaluating the SELECT statement, Oracle evaluate the clauses in the following sequence:

1. FROM
2. WHERE
3. SELECT
4. ORDER BY

Since Oracle evaluates the WHERE clause before the SELECT clause, you cannot use column aliases in the WHERE clause. The reason is that that column aliases are not available at the time Oracle evaluates the WHERE clause.

Besides the SELECT statement, you can use the WHERE clause in the DELETE or UPDATE statement to specify which rows to update or delete.

Oracle WHERE clause examples #

See the following products table in the sample database:

Selecting rows by using a simple equality operator #

The following example uses a SELECT statement with a WHERE clause to return products whose names are 'Kingston':

SELECT
  product_name,
  description,
  list_price,
  category_id
FROM
  products
WHERE
  product_name = 'Kingston';Code language: SQL (Structured Query Language) (sql)

Try it

Output:

In this example, Oracle evaluates the clauses in the order: FROM, WHERE and SELECT.

• First, the FROM clause specified the table for querying data.
• Second, the WHERE clause filtered rows based on the condition e.g., product_name = 'Kingston').
• Third, the SELECT clause chose the columns that should be returned.

Selecting rows using the comparison operator #

Besides the equality operator (=), Oracle provides many other comparison operators for filtering rows. Here are tthe common ones:

Operator	Description
=	Equality
!=,<>	Inequality
>	Greater than
<	Less than
>=	Greater than or equal to
<=	Less than or equal to
IN	Equal to any value in a list of values
ANY/ SOME / ALL	Compare a value to a list or subquery. It must be preceded by another operator such as =, >, <.
NOT IN	Not equal to any value in a list of values
[NOT] BETWEEN n and m	Equivalent to [Not] >= n and <= y.
[NOT] EXISTS	Return true if the subquery returns at least one row
IS [NOT] NULL	NULL test

The following example uses a WHERE clause to retrieve the products whose list prices are greater than 500:

SELECT
  product_name,
  list_price
FROM
  products
WHERE
  list_price > 500;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

Filtering rows by multiple conditions #

To combine two conditions, you can use the AND, OR and NOT logical operators.

For example, to get all motherboards that belong to the category id 1 and have list prices greater than 500, you use the following statement:

SELECT
  product_name,
  list_price
FROM
  products
WHERE
  list_price > 500
  AND category_id = 1;Code language: SQL (Structured Query Language) (sql)

Try it

The result set includes only motherboards whose list prices are greater than 500.

Selecting rows that have values between two values #

To find rows that have a value between two values, you use the BETWEEN operator in the WHERE clause:

BETWEEN low_value AND high_value

For example, to get the products whose list prices are between 650 and 680, you use the following statement:

SELECT
    product_name,
    list_price
FROM
    products
WHERE
    list_price BETWEEN 650 AND 680
ORDER BY
    list_price;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

Note that the following expressions are equivalent:

list_price BETWEEN 650 AND 680
list_price >= 650 AND list_price <= 680

Selecting rows that are in a list of values #

To retrieve rows with values in a list of values, you use the IN operator as follows:

SELECT
    product_name,
    category_id
FROM
    products
WHERE
    category_id IN(1, 4)
ORDER BY
    product_name;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

The expression:

category_id IN (1, 4)Code language: SQL (Structured Query Language) (sql)

is the same as:

category_id = 1 OR category_id = 4Code language: SQL (Structured Query Language) (sql)

Selecting rows that contain values as part of a string #

The following statement retrieves a product whose name starts with Asus:

SELECT
  product_name,
  list_price
FROM
  products
WHERE
  product_name LIKE 'Asus%'
ORDER BY
  list_price;Code language: SQL (Structured Query Language) (sql)

Try it

In this example, we use the LIKE operator to match rows based on the specified pattern.

Summary #

• Use the Oracle WHERE clause to filter rows based on a condition.

Quiz #