This article is written about how to use the Oracle AND circumstance with syntax and examples.
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.
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.
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.
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.
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.
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.
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.
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.
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.