Oracle AND condition

The Oracle condition AND (also called AND operator) is used to check two or more conditions of SELECT, INSERT, UPDATE or DELETE operators.

Syntax of the AND condition in Oracle/PLSQL

WHERE condition1
AND condition2
…
AND condition_n;

Parameters and arguments of the condition

• condition1, condition2,… condition_n – All conditions that must be met for the selected records.

condition1 AND condition2 logic

Note: Oracle condition AND allows checking 2 or more conditions.

Oracle condition AND requirement that all conditions (condition1, condition2, condition_n) must be met for records that are included in the resulting set.

Example with the SELECT operator

In the SELECT operator, the first condition AND is associated with the second condition.

SELECT *
FROM customers
WHERE state = 'Nevada'.
And customer_id > 12,000;

In this example, the AND operator would return all customers that are in state ‘Nevada’ and for which customer_id > 12000. Since SELECT uses *, all fields from the customer_id table will appear in the result set.

Example JOINING tables

The following example shows how the AND condition can be used to combine multiple tables in a SELECT operator. For example:

SELECT orders.order_id, suppliers.supplier_name
FROM suppliers, orders
WHERE.supplier_id = orders.supplier_id
AND suppliers.supplier_name = 'RosKosmos';

Although this SQL query works just fine, you could rewrite this SQL in the following way using INNER JOIN.

For example:

SELECT orders.order_id, suppliers.supplier_name
FROM suppliers
INNER JOIN orders
ON.supplier_id = orders.supplier_id
WHERE suppliers.supplier_name = 'RosKosmos';

This example of the Oracle AND operator will return all the lines where supplier_name is ‘RosKosmos’. And the tables suppliers and orders are connected via supplier_id.

You will notice that all fields have prefixes beginning with the table name (that is: orders.order_id). This is necessary to exclude any ambiguity as to which table the field refers to. As the same field can exist in both the order and the order table.

This result set will display the order_id and supplier_name fields.

Example with the INSERT operator

The following example shows how the AND condition can be used in the INSERT operator. For example:

INSERT INTO suppliers
(supplier_id, supplier_name)
SELECT customer_id, customer_name
FROM customers
WHERE customer_name = 'Paradise'
AND customer_id <= 3000;

This example shows an insert in the table of suppliers of all the customer_id and customer_name records from the customer_name table where customer_name is ‘Paradise’ and has customer_id less or equal to 3000.

Example with UPDATE operator

This example shows how the AND condition can be used in UPDATE. For example:

UPDATE suppliers
SET supplier_name = 'Nike'
WHERE supplier_name = 'Adidas'
AND offices = 8;

This example will update all values of the supplier_name field in the ‘Nike’ table where supplier_name was ‘Adidas’ and had 8 offices.

Example c DELETE

Finally, the last example as an AND condition can be used in DELETE. For example:

DELETE FROM suppliers
WHERE supplier_name = 'Apple'
AND product = 'iPod';

This example will delete all the entries from the suppliers’ table whose supplier_name was ‘Apple’ and the product was ‘iPod’.

Introduction to PL/SQL Conditional Statements in Oracle Database