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.

Here’s the syntax of the AND operator:

expression_1 AND expression_2

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

Typically, you use AND operator in the WHERE clause of the SELECT to filter data based on multiple expressions.

Additionally, you can use the AND operator in the condition of the JOIN clause to form the join condition.

When you use multiple logical operators (such as AND, OR, and NOT) 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 #

We’ll use the following orders table in the sample database:

Combining two Boolean expressions example #

The following example uses an AND operator in the WHERE clause to find orders of the customer id 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)

Try it

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:

Combining 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)

Try it

Output:

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

Using AND operator with the 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)

Try it

Summary #

• Use the Oracle AND operator to combine two or more Boolean expressions to form a flexible condition.

Quiz #