Oracle WHERE Clause

This SQL tutorial provides an introduction to the WHERE clause in Oracle with explanations, examples and exercises. For this lesson’s exercises, use this link.

This tutorial is a part of several posts describing how to use the WHERE clause in Oracle. To read additional posts regarding this subject, please use the following links:

• Introduction – Oracle WHERE clause
• Basic comparison operators
• IN operator
• BETWEEN operator
• LIKE operator
• IS NULL operator
• AND & OR operators

Introduction – Oracle WHERE Clause

The Oracle WHERE Clause is used to restrict the rows returned from a query. While the previous chapter (Basic SELECT Statements) explained how to extract the names of all customers from Customers table, using the Oracle WHERE clause, you are able to restrict the query to rows that meet a certain condition. For example: extract the customers whose age is greater than 27, or the customers who live in London.

• The Oracle WHERE clause must be written after the Oracle FROM clause (which in turn must be written after the Oracle SELECT clause), this order cannot be changed.
• Unlike the Oracle SELECT and FROM statements, which are necessary for creating a valid SQL query, the Oracle WHERE clause is optional. A SQL query can function properly with or without the Oracle WHERE clause.

Oracle WHERE Clause Syntax

The Oracle WHERE clause has the following structure:

WHERE  column_name operator value

SELECT *
FROM products
WHERE unit_price < 30

• Column_name – the name of the column. The query above retrieves all products whose price is less than 30, and therefore the unit_price column was selected. If you would like to display all customers whose city of residence equals Paris, you need to specify the City column in the WHERE clause.

SELECT *
FROM customers
WHERE city = 'Paris'

• Operator – there are two types of operators:
  • Basic comparison operators– represented by operators such as = , < , > , => , =<, <>
  • More advanced comparison operators– represented by operators such as IN, BETWEEN, LIKE, AND and OR.

• Comparison value – 
• Number – for example, a price is greater than a certain number.

SELECT product_id, product_name, unit_price
FROM products
WHERE unit_price > 6000

• Date – for example, the birthdate is greater than a certain date.

SELECT customer_id, last_name, birthdate 
FROM customers
WHERE birthdate > '13-JAN-1982'

• String – for example, a product’s name equals a certain value.

SELECT product_id, product_name, unit_price
FROM products
WHERE product_name = 'Tea'

General Guidelines

• The value data type must match the column date type  (it makes no sense to try to display the products whose name is greater than 4503).

SELECT product_id , product_name, unit_price
FROM products
WHERE product_name > 4503
-- (error)

•  If the requested comparison does not match the table values (for example, the highest price in the table is 50 and you would like to display the products whose price is greater than 200,000), no result will be retrieved but no error will be generated either (the table could not retrieve values that, based on its settings, do not exist).

SELECT product_id, product_name, unit_price
FROM products
WHERE unit_price > 200000
-- (valid Oracle SELECT statement)
-- As described no error will be generated and no rows will be retrieved either