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 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 value.

We often 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 in a statement, 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 will use the orders table in the sample database for the demonstration.

Using Oracle OR operator to combine two Boolean expressions example

The following example finds orders whose status is 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)

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)

The following picture illustrates the result:

B) Using Oracle OR operator to combine more than two Boolean expressions example

We often use the OR operators to combine more than two Boolean expressions. For example, the following statement retrieves 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)

Here is the result:

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)

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

C) Using Oracle OR operator to combine with AND operator example

You can combine the OR operator with other logical operators such as AND and NOT to form a 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)

In this tutorial, you have learned how to use the Oracle OR operator to combine two or more Boolean expressions.