WHERE clause in Oracle

Posted by AbundantCode

This article will show you how to use the Oracle WHERE clause to filter rows returned by a query by specifying a criteria.

The WHERE clause specifies a search criterion for the SELECT statement’s rows returned. The syntax of the WHERE clause is demonstrated as follows:

SELECT
    select_list
FROM
    table_name
WHERE
    search_condition
ORDER BY
   sort_expression;

After the FROM clause, but before the ORDER BY clause, comes the WHERE clause. The search condition keyword comes after the WHERE keyword, and it specifies a condition that returned rows must meet.

You can utilise the WHERE clause in the DELETE or UPDATE statement to define which rows to update or delete in addition to the SELECT statement.

WHERE examples in Oracle

Using a basic equality operator to select rows

Only products with the name ‘Kingston’ are returned in the following example:

SELECT
    product_name,
    description,
    list_price,
    category_id
FROM
    products
WHERE
    product_name = 'Kingston';

The clauses are evaluated in the following sequence by Oracle in this example: SELECT and FROM WHERE

  • The FROM clause, for starters, specifies the table from which data will be retrieved.
  • The WHERE clause, on the other hand, filtered entries depending on the criteria (e.g., product name =’Kingston’).
  • The SELECT clause determined which columns should be returned in the third step.

using comparison operator

You use the following statement to get goods with list prices more than 500:

SELECT
    product_name,
    list_price
FROM
    products
WHERE
    list_price > 500;

Choose rows that fulfil certain criteria

The logical operators AND, OR, and NOT can be used to combine circumstances.

For example, you could use the following statement to get all motherboards in category id 1 with list prices greater than 500

SELECT
    product_name,
    list_price
FROM
    products
WHERE
    list_price > 500
    AND category_id = 4;

Rows with a value between two values are selected.

The BETWEEN operator in the WHERE clause is used to discover rows with a value that is between two values.

For example, you could use the following expression to fetch goods with list prices between 650 and 680:

SELECT
    product_name,
    list_price
FROM
    products
WHERE
    list_price BETWEEN 650 AND 680
ORDER BY
    list_price;

Rows in a list of values are selected.

The IN operator is used to query rows in a list of values as follows:

SELECT
    product_name,
    category_id
FROM
    products
WHERE
    category_id IN(1, 4)
ORDER BY
    product_name;
Tags: