This article is written about how to use the Oracle EXISTS situation with syntax and examples.
The Oracle EXISTS situation is used in mixture with a subquery and is considered “to be met” if the subquery returns at least one row. It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.
Syntax
The syntax for the EXISTS circumstance in Oracle/PLSQL is:
WHERE EXISTS ( subquery );Parameters or Arguments
subquery The subquery is a SELECT statement. If the subquery returns at least one document in its result set, the EXISTS clause will evaluate to real and the EXISTS circumstance will be met. If the subquery does now not return any records, the EXISTS clause will consider to false and the EXISTS situation will not be met.
Note
Oracle SQL statements that use the Oracle EXISTS situation are very inefficient considering that the sub-query is RE-RUN for EVERY row in the outer query’s table. There are more efficient ways to write most queries, that do no longer use the EXISTS condition.
Example – With SELECT Statement
Let’s look at a simple example.
The following is a SELECT announcement that uses the EXISTS condition:
SELECT *
FROM customers
WHERE EXISTS (SELECT *
FROM order_details
WHERE customers.customer_id = order_details.customer_id);This Oracle EXISTS condition instance will return all information from the customers desk the place there is at least one file in the order_details desk with the matching customer_id.
Example – With SELECT Statement using NOT EXISTS
The Oracle EXISTS circumstance can additionally be combined with the NOT operator.
For example,
SELECT *
FROM customers
WHERE NOT EXISTS (SELECT *
FROM order_details
WHERE customers.customer_id = order_details.customer_id);This Oracle EXISTS instance will return all files from the customers table the place there are no information in the order_details table for the given customer_id.
Example – With INSERT Statement
The following is an instance of an INSERT assertion that makes use of the EXISTS condition:
INSERT INTO contacts
(contact_id, contact_name)
SELECT supplier_id, supplier_name
FROM suppliers
WHERE EXISTS (SELECT *
FROM order_details
WHERE suppliers.supplier_id = order_details.supplier_id);Example – With UPDATE Statement
The following is an instance of an UPDATE statement that uses the EXISTS condition:
UPDATE suppliers
SET supplier_name = (SELECT customers.name
FROM customers
WHERE customers.customer_id = suppliers.supplier_id)
WHERE EXISTS (SELECT customers.name
FROM customers
WHERE customers.customer_id = suppliers.supplier_id);Example – With DELETE Statement
The following is an example of a DELETE announcement that makes use of the EXISTS condition:
DELETE FROM suppliers
WHERE EXISTS (SELECT *
FROM order_details
WHERE suppliers.supplier_id = order_details.supplier_id);