Oracle Comparison Operators

This SQL tutorial focuses on the basic Comparison Operators in Oracle, and provides explanations, examples and exercises. For this lesson’s exercises, use this link.

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

Oracle Comparison Operators

Comparison conditions in Oracle are used to compare one expression to another value or expression. This section explains how to use the basic Comparison Conditions (as mentioned earlier, = , < , > , => , =<, <>) .

Comparing Numbers

SELECT product_id, product_name, unit_price
FROM products
WHERE  unit_price  =  50

In this Oracle example, the explicit value of 50 is compared to the price value in the unit_price column of the Products table.

SELECT product_id, product_name, unit_price, supplier_id
FROM products
WHERE  supplier_id  =  60

In this Oracle example, the explicit value of 60 is compared to the supplier number in the supplier_id column of the Products table.

SELECT employee_id, last_name, first_name, age
FROM employees
WHERE  age  =  27

In this Oracle example, the explicit value of 27 is compared to the age in the Age column.

It is not possible to write the numeric value together with special characters. also, numeric values should not be enclosed within single quotes.

SELECT product_id, product_name, unit_price
FROM products
WHERE  unit_price =    $50
-- (error)

SELECT product_id, product_name, unit_price, discount_percent
FROM products 
WHERE discount_percent = %20 
-- (error) 
-- * Usually these type of numbers are expressed as a number with a decimal point
-- * For example 0.2

SELECT product_id, product_name, unit_price
FROM products
WHERE  unit_price  =   50,000
-- (error)

SELECT product_id, product_name, unit_price
FROM products
WHERE unit_price   =   50
-- (valid comparison)

If the requested comparison does not match table values (for example, the highest price in the table is 120 and you would like to display the products whose price is greater than 20,000), no results will be retrieved and no error will be generated either.

Comparing Strings

In Oracle the string value must be enclosed within single quotes (‘string value’).

SELECT product_id, product_name
FROM products
WHERE  product_name = Tea
-- (error)

SELECT product_id, product_name  
FROM products
WHERE  product_name = 'Tea'
-- (valid comparison)

SELECT customer_id, first_name, last_name
FROM customers
WHERE first_name = 'John'
-- (valid comparison)

In Oracle the string values are case sensitive. The following Oracle queries will produce different results:

SELECT product_name
FROM products
WHERE product_name = 'TEA' 

SELECT product_name
FROM products
WHERE product_name = 'Tea'

SELECT product_name
FROM products
WHERE product_name = 'tea'

Comparing Dates

In Oracle, comparing with dates requires specifying the date value within single quotes (‘date value’). It also requires using the appropriate Oracle date format, as date values are format sensitive.

SELECT customer_id, customer_name, birthdate
FROM customers
WHERE  birthdate  = 17-MAY-1980
-- (error - no single quotes)

SELECT customer_id, customer_name, birthdate
FROM customers
WHERE birthdate = '05-17-1980'
-- (error - not a valid date format)    

SELECT customer_id, customer_name, birthdate
FROM customers
WHERE birthdate = '17-SEP-1980'
-- (valid comparison)