OR Operator in Oracle

Posted by AbundantCode

You’ll learn how to combine two or more Boolean statements with the Oracle OR operator in this article.

The OR operator combines Boolean phrases and returns true if one of them is true.

The syntax of the OR operator is illustrated as follows:

expression_1 OR expression_2

To filter data, we frequently utilise the OR operator in the WHERE clause of the SELECT, DELETE, and UPDATE statements.

Oracle analyses the OR operators after the NOT and AND operators if you employ several logical operators in a statement. Parentheses, on the other hand, can be used to modify the evaluation order.

Examples of OR operator in Oracle

Combining two Boolean statements with Oracle’s OR operator

Orders that are pending or cancelled are found using the following example:

SELECT
    order_id,
    customer_id,
    status,
    order_date
FROM
    orders
WHERE
    status = 'Pending'
    OR status = 'Canceled'
ORDER BY
    order_date DESC;

Combining more than two Boolean expressions with Oracle’s OR operator

The OR operators are frequently used to combine more than two Boolean expressions. For instance, the following line retrieves orders that are assigned to one of the salesman ids 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;

You can use the IN operator instead of several OR operators, as seen 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;

Combining OR and AND operator

To create a condition, combine the OR operator with other logical operators like AND and NOT. For example, the following query returns all orders with a cancelled or pending status that belong to the customer id 44.

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;
Tags: