Follow us:
sql tutorial

SQL: EXISTS Condition

This SQL tutorial explains how to use the SQL EXISTS condition with syntax and examples.

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
The subquery is a SELECT statement. If the subquery returns at least one record in its result set, the EXISTS clause will evaluate to true and the EXISTS condition will be met. If the subquery does not return any records, the EXISTS clause will evaluate to false and the EXISTS condition will not be met.

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 query's table. There are more efficient ways to write most queries, that do not use the SQL EXISTS Condition.

Example - With SELECT Statement

Let's 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);
Share: