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 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:
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:
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:
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:
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.