AND Condition

This Oracle tutorial explains how to use the Oracle AND condition with syntax and examples.

Description

The Oracle AND condition (also called the AND Operator) is used to test for two or more conditions in a SELECT, INSERT, UPDATE, or DELETE statement.

Syntax

The syntax for the AND Condition in Oracle/PLSQL is:

WHERE condition1

AND condition2

...

AND condition_n;

Parameters or Arguments

condition1, condition2, ... condition_n

All of the conditions that must be met for the records to be selected.

Note

• The Oracle AND condition allows you to test 2 or more conditions.
• The Oracle AND condition requires that all of the conditions (ie: condition1, condition2, condition_n) be must be met for the record to be included in the result set.

Example - With SELECT Statement

The first Oracle AND condition query involves a SELECT statement with 2 conditions.

For example:

SELECT *

FROM customers

WHERE state = 'Florida'

AND customer_id > 5000;

This Oracle AND example would return all customers that reside in the state of Florida and have a customer_id > 5000. Because the * is used in the SELECT statement, all fields from the customers table would appear in the result set.

Example - JOINING Tables

Our next Oracle AND example shows how the AND condition can be used to join multiple tables in a SELECT statement.

For example:

SELECT orders.order_id, suppliers.supplier_name

FROM suppliers, orders

WHERE suppliers.supplier_id = orders.supplier_id

AND suppliers.supplier_name = 'Microsoft';

Though the above SQL works just fine, you would more traditionally write this SQL as follows using a proper INNER JOIN.

For example:

SELECT orders.order_id, suppliers.supplier_name

FROM suppliers

INNER JOIN orders

ON suppliers.supplier_id = orders.supplier_id

WHERE suppliers.supplier_name = 'Microsoft';

This Oracle AND condition example would return all rows where the supplier_name is Microsoft. And the suppliers and orders tables are joined on supplier_id. You will notice that all of the fields are prefixed with the table names (ie: orders.order_id). This is required to eliminate any ambiguity as to which field is being referenced; as the same field name can exist in both the suppliers and the orders tables.

In this case, the result set would only display the order_id and supplier_name fields (as listed in the first part of the SELECT statement.).

Example - With INSERT Statement

This next Oracle AND example demonstrates how the AND condition can be used in the INSERT statement.

For example:

INSERT INTO suppliers

(supplier_id, supplier_name)

SELECT customer_id, customer_name

FROM customers

WHERE customer_name = 'Microsoft'

AND customer_id <= 1000;

This Oracle AND condition example would insert into the suppliers table, all customer_id and customer_name records from the customers table whose customer_name is Microsoft and have a customer_id less than or equal to 1000.

Example - With UPDATE Statement

This Oracle AND condition example shows how the AND condition can be used in the UPDATE statement.

For example:

UPDATE suppliers

SET supplier_name = 'Apple'

WHERE supplier_name = 'RIM'

AND offices = 8;

This Oracle AND condition example would update all supplier_name values in the suppliers table to Apple where the supplier_name was RIM and had 8 offices.

Example - With DELETE Statement

Finally, this last Oracle AND example demonstrates how the AND condition can be used in the DELETE statement.

For example:

DELETE FROM suppliers

WHERE supplier_name = 'Apple'

AND product = 'iPod';

This Oracle AND condition example would delete all records from the suppliers table whose supplier_name was Apple and product was iPod.