SQL EXISTS Condition

SQL EXISTS Condition

The SQL EXISTS condition is considered "to be met" if the subquery returns at least one row. The SQL EXISTS condition can be used in any valid SQL statement - SQL SELECT statement, SQL INSERT statement, SQL UPDATE statement, or SQL DELETE statement.

The syntax for the SQL EXISTS condition is:

WHERE EXISTS ( subquery );

Note

SQL Statements that use the SQL EXIST 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.

SQL EXISTS Condition - SELECT Statement example

Let's take a look at a simple example.

The following is an 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.

SQL EXISTS Condition - SELECT Statement using NOT EXISTS example

The SQL EXISTS condition can also be combined with the SQL NOT operator.

For example,

SELECT *

FROM suppliers

WHERE NOT 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 are no records in the orders table for the given supplier_id.

SQL EXISTS Condition - INSERT Statement example

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

INSERT INTO suppliers

(supplier_id, supplier_name)

SELECT account_no, name

FROM suppliers

WHERE EXISTS (select * from orders Where suppliers.supplier_id = orders.supplier_id);

SQL EXISTS Condition - UPDATE Statement example

The following is an example of an 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);

SQL EXISTS Condition - DELETE Statement example

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

DELETE FROM suppliers

WHERE EXISTS (select *

              from orders

              where suppliers.supplier_id = orders.supplier_id);