Oracle OR Operator

Summary: in this tutorial, you will learn how to the Oracle OR operator to combine two or more Boolean expressions.

Introduction to Oracle OR operator #

The OR operator is a logical operator that combines two Boolean expressions and returns true if one of the expressions is true.

The following illustrates the syntax of the OR operator:

expression_1 OR expression_2Code language: SQL (Structured Query Language) (sql)

The following table shows the results the OR operator between true, false, and a NULL:

X	Y	X OR Y
TRUE	TRUE	TRUE
TRUE	FALSE	TRUE
TRUE	NULL	TRUE
FALSE	FALSE	FALSE
FALSE	NULL	NULL
NULL	NULL	NULL

In practice, you use the OR operator in the WHERE clause of the SELECT, DELETE, and UPDATE statements to form a condition for filtering data.

If you use multiple logical operators (AND, OR, and NOT) within an expression, Oracle evaluates the OR operators after the NOT and AND operators. However, you can change the order of evaluation by using parentheses.

Oracle OR operator examples #

We’ll use the orders table in the sample database for the demonstration.

Combining two Boolean expressions example #

The following statement uses the OR operator in an expression in a WHERE clause to find orders with the status pending or canceled:

SELECT
  order_id,
  customer_id,
  status,
  order_date
FROM
  orders
WHERE
  status = 'Pending'
  OR status = 'Canceled'
ORDER BY
  order_date DESC;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

In this example, the statement returned all orders that satisfy one of the following expressions:

status = 'Pending'
status = 'Canceled'Code language: SQL (Structured Query Language) (sql)

Combining more than two Boolean expressions #

You can multiple the OR operators to combine more than two Boolean expressions.

For example, the following statement uses the OR operator to retrieve the orders that are in charge of one of the following the salesman’s id 60, 61 or 62:

SELECT
  order_id,
  customer_id,
  status,
  salesman_id,
  order_date
FROM
  orders
WHERE
  salesman_id = 60
  OR salesman_id = 61
  OR salesman_id = 62
ORDER BY
  order_date DESC;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

Instead of using multiple OR operators, you can use the IN operator as shown in the following example:

SELECT
  order_id,
  customer_id,
  status,
  salesman_id,
  order_date
FROM
  orders
WHERE
  salesman_id IN (60, 61, 62)
ORDER BY
  order_date DESC;Code language: SQL (Structured Query Language) (sql)

Try it

This query returns the same result as the one that uses the OR operator above.

Using OR operator with AND operator #

You can combine the OR operator with other logical operators such as AND and NOT to form a more flexible condition.

For example, the following query returns the orders that belong to customer id 44 and have canceled or pending status.

SELECT
  order_id,
  customer_id,
  status,
  salesman_id,
  order_date
FROM
  orders
WHERE
  (
    status = 'Canceled'
    OR status = 'Pending'
  )
  AND customer_id = 44
ORDER BY
  order_date;Code language: SQL (Structured Query Language) (sql)

Try it

Summary #

• Use the Oracle OR operator to combine two Boolean expressions and return TRUE if one of the expression is TRUE.

Quiz #