Home » Oracle SQL » Oracle AND Operator

Oracle AND Operator

AND is a logical condition in Oracle and its usage is the same as in English language. This logical condition is used to combine two expressions.

The syntax for this Logical condition is as follows:

expression1 AND expression2

It returns TRUE if both the expressions are TRUE, it returns FALSE if either of them is FALSE. Otherwise it returns UNKNOWN.

To see the outcomes when we combine two expressions having TRUE, FALSE and NULL values, we can refer this table:

AND Logical Conditions

AND Logical operator is used with WHERE clause and can be used in SELECT, DELETE and UPDATE statements. It is also used while writing the join condition when we combine the tables.

AND, OR and NOT are the three logical operators used in Oracle.

Oracle AND Logical Condition Examples

We will be using Employees Table from HR schema for these examples:

Employee Table from HR Schema

1. Oracle AND to combine two expressions

Lets check this query to retrieve all the employees from department 80 who have salary over 8000.

SELECT first_name, department_id, salary 
FROM employees 
WHERE department_id = 80 
AND salary > 8000;

Result:

AND to combine 2 expressions

Using this query, we get all the employees who meet the condition.

2. Oracle AND to combine multiple expressions

Now we will use AND logical operator to combine more than two expressions:

SELECT first_name, department_id, salary 
FROM employees
WHERE department_id = 80
AND salary >8000
AND first_name = 'Peter';

Result:

AND to combine multiple expressions

The query returned two employees with the name Peter who belong to department 80 and have salary more than 8000.

3. Oracle AND in combination with OR operator

Now we will combine both AND and OR logical operators in query to see how it works:

Suppose we want to find employees who either work in department=80 or 60 and have salary more than 5000 then we will use this query:

SELECT first_name, department_id, salary 
FROM employees
WHERE (department_id = 80
OR department_id=60)
AND salary >5000;

Result:

AND with OR Logical Operator in Oracle

This query gives us the desired result after both the logical operator AND and OR.

In this article, we learned how to use AND logical operator to combine expressions.