SQL EXISTS condition

SQL EXISTS condition

Description

The SQL EXISTS condition is used in combination 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 SQL EXISTS condition is:

WHERE EXISTS ( subquery );

Parameters or Arguments

subquery is a SELECT statement.

Note

• SQL Statements that use the SQL EXISTS Condition are very inefficient since the sub-query is RE-RUN for EVERY row in the outer querys table. There are more efficient ways to write most queries, that do not use the SQL EXISTS Condition.

Example - With SELECT Statement

Lets look at a simple example.
The following is a SQL SELECT statement that uses the SQL EXISTS condition:

SELECT *
FROM suppliers
WHERE EXISTS (SELECT *
 FROM orders
 WHERE suppliers.supplier_id = orders.supplier_id);

This SQL EXISTS condition example will return all records from the suppliers table where there is at least one record in the orders table with the same supplier_id.

Example - With SELECT Statement using NOT EXISTS

The EXISTS condition can also be combined with the NOT operator.
For example :

SELECT *
FROM suppliers
WHERE NOT EXISTS (SELECT * 
 FROM orders
 WHERE suppliers.supplier_id = orders.supplier_id);

This SQL EXISTS example will return all records from the suppliers table where there are no records in the orders table for the given supplier_id.

Example - With INSERT Statement

The following is an example of a SQL INSERT statement that uses the SQL EXISTS condition :

INSERT INTO contacts
(contact_id, contact_name)
SELECT supplier_id, supplier_name
FROM suppliers
WHERE EXISTS (SELECT *
 FROM orders
 WHERE suppliers.supplier_id = orders.supplier_id);

Example - With UPDATE Statement

The following is an example of a SQL UPDATE statement that uses the SQL 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 SQL DELETE statement that uses the SQL EXISTS condition :

DELETE FROM suppliers
WHERE EXISTS (SELECT *
 FROM orders
 WHERE suppliers.supplier_id = orders.supplier_id);

Source : techonthenet.com 

download file now