Oracle AND Operator

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

Introduction to Oracle AND operator

The AND operator is a logical operator that combines Boolean expressions and returns true if both expressions are true. If one of the expressions is false, the AND operator returns false.

The syntax of the AND operator is as follows:

expression_1 AND expression_2

The following table illustrates the result when you combine the true, false, and a NULL value using the AND operator

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

When you use more than one logical operator in a statement, Oracle always evaluates the AND operators first. However, you can use parentheses to change the order of evaluation.

Oracle AND operator examples

See the following orders table in the sample database:

A) Oracle AND to combine two Boolean expressions example

The following example finds orders of the customer 2 with the pending status:

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

In this example, the query returned all orders that satisfy both expressions:

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

and

customer_id = 2Code language: SQL (Structured Query Language) (sql)

Here is the result:

B) Oracle AND to combine more than two Boolean expressions example

You can use multiple AND operators to combine Boolean expressions.

For example, the following statement retrieves the orders that meet all the following conditions:

• placed in 2017
• is in charge of the salesman id 60
• has the shipped status.

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;Code language: SQL (Structured Query Language) (sql)

In this example, we used the EXTRACT() function to get the YEAR field from the order date and compare it with 2017.

C) Oracle AND to combine with OR operator example

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

For example, the following query finds an order placed by customer id 44 and has the status canceled 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;Code language: SQL (Structured Query Language) (sql)

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