AND Operator in Oracle

You will learn how to use the Oracle AND operator to combine two or more Boolean expressions in this article.

The AND operator is a logical operator that joins two Boolean expressions together and returns true if both are true. The AND operator returns false if one of the expressions is false.

The AND operator has the following syntax:

expression_1 AND expression_2

To establish a condition for matching data, we often utilise AND in the WHERE clause of the SELECT, DELETE, and UPDATE statements. In addition, the AND operator is used to form the join condition in the predicate of the JOIN clause.

When a statement contains more than one logical operator, Oracle examines the AND operators first. You can, however, use parentheses to modify the evaluation order.

Examples of Oracle AND operator

Combine two Boolean expressions

The following example locates customer 2’s orders that are in the pending status:

SELECT
	order_id,
	customer_id,
	status,
	order_date
FROM
	orders
WHERE
	status = 'Pending'
	AND customer_id = 2
ORDER BY
	order_date;

Combine More than two boolean expressions

Combining Boolean statements can be done using several AND operators.

The following statement, for example, obtains orders that fulfil all of the following criteria:

The salesman id 60 has the shipping status and was assigned to him in 2017.

SELECT
    order_id,
    customer_id,
    status,
    order_date
FROM
    orders
WHERE
    status = 'Shipped'
    AND salesman_id = 60
    AND EXTRACT(YEAR FROM order_date) = 2017
ORDER BY
    order_date;

Combine AND with OR operator

To create a condition, combine the AND operator with other logical operators like OR and NOT.

The following query, for example, finds orders placed by customer id 44 that are cancelled or pending.

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;