Using AND Condition in Oracle

This article is written about how to use the Oracle AND circumstance with syntax and examples.

Description

The Oracle AND situation (also known as the AND Operator) is used to check for two or extra prerequisites 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 prerequisites that must be met for the data to be selected.

Note

The Oracle AND situation allows you to check two or extra conditions. The Oracle AND circumstance requires that all of the stipulations (ie: condition1, condition2, condition_n) should be met for the record to be protected in the result set.

Example – With SELECT Statement

The first Oracle AND circumstance query entails a SELECT assertion with two conditions.

For example:

SELECT *
FROM customers
WHERE state = 'Florida'
AND customer_id > 5000;

This Oracle AND instance would return all clients that dwell in the country of Florida and have a customer_id > 5000. Because the * is used in the SELECT statement, all fields from the clients table would appear in the result set.

Example – JOINING Tables

Our next Oracle AND example indicates how the AND circumstance can be used to be a part of a couple of 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 extra traditionally write this SQL as follows using a perfect 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 instance would return all rows where the supplier_name is Microsoft. And the suppliers and orders tables are joined on supplier_id. You will word that all of the fields are prefixed with the table names (ie: orders.order_id). This is required to dispose of any ambiguity as to which discipline is being referenced; as the equal field title can exist in both the suppliers and the orders tables.

In this case, the end result set would solely show the order_id and supplier_name fields (as listed in the first phase of the SELECT statement.).

Example – With INSERT Statement

This subsequent 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 situation example would insert into the suppliers table, all customer_id and customer_name data from the clients desk whose customer_name is Microsoft and have a customer_id much less than or equal to 1000

Example – With UPDATE Statement

This Oracle AND situation example indicates how the AND situation 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 circumstance example would replace all supplier_name values in the suppliers table to Apple where the supplier_name was once RIM and had 8 offices.

Example – With DELETE Statement

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

For example:

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

This Oracle AND circumstance instance would delete all data from the suppliers table whose supplier_name was Apple and product was iPod.

Learn more about joining tables in Oracle.