SQL EXISTS condition

SQL EXISTS condition is used in combination with a subquery and is considered satisfied if the subquery returns at least one line. It can be used in the SELECT, INSERT, UPDATE or DELETE operator.

Syntax of EXISTS condition in SQL

WHERE EXISTS ( subquery_id );

where:

• subquery_id – the subquery is a SELECT operator. If a subquery returns at least one entry in its result set, the EXISTS offer is evaluated as true and the EXISTS condition is met. If the subquery does not return any record, the EXISTS sentence is evaluated as false and the EXISTS condition is not met.

Note:
SQL statements using the EXISTS condition are very inefficient because the subquery is re-run for EVERY line in the external query table. There are more efficient ways to write most queries that do not use the EXISTS condition.

Using the EXISTS Condition with the SELECT Operator

Let us start with an example that shows how to use the EXISTS condition with a SELECT operator.

In this example we have a table with the following data:

And the table with the following data:

Now let’s find all the records from the customers table, where there is at least one record in the orders table with the same custom_id. Perform the following SELECT query:

SELECT *
FROM customs
WHERE EXISTS
(SELECT *
FROM ords
WHERE customs.custom_id = ords.custom_id);

Four entries will be selected. Here are the results that you should get:

In this example, there are 4 entries in the customers table that have the value custom_id in the orders table.

EXISTS condition with UPDATE operator

Let’s look at an example where the EXISTS condition in the UPDATE operator is used.

In this example we have a product table with the following data:

And a table named sum_data with the following data:

Now let’s update the sum_data table with values from the products table. Enter the following SQL statement:

UPDATE sum_data
SET current_cat = (SELECT cat_id)
FROM prods
WHERE prods.prod_id = sum_data.prod_id)
WHERE EXISTS (SELECT cat_id)
FROM prods
WHERE prods.prod_id = sum_data.prod_id);

5 records will be updated. Select data from sum_data table again:

SELECT *
FROM sum_data;

Here are the results you’ll get:

In this example, the current_cat field in the sum_data table will be updated with cat_id data from the products table where prod_id values match. The first 5 records in the sum_data table were updated.

Tip: If we did not enable EXISTS condition, UPDATE query would update current_category field to NULL in the 6th row of summary_data table (because products table has no record where product_id = 8).

EXISTS Condition with DELETE Operator

Let’s look at an example that uses the EXISTS condition in the DELETE operator.

In this example, we have a table with the following data:

And the table with the following data:

Enter the following DELETE operator:

DELETE FROM ords
WHERE EXISTS
(SELECT *
FROM customs
WHERE customs.custom_id = ords.custom_id
AND customs.l_name = ‘Bieber’);

1 record will be deleted. Select the data from the order table again:

SELECT * FROM ords;

here are the results you should get:

This example removes all records from the order table, where the customer table has a record with l_name “Bieber” and the same custom_id value in both tables. In this example the record for ord_id = 4 has been deleted.

NOT with EXISTS condition

Finally, the NOT condition can be combined with the EXISTS condition to create the NOT EXISTS condition. Let’s look at an example which shows how to use the NOT EXISTS condition in SQL.

In this example we have a table with the following data:

And the table with the following data:

Enter the following SQL statement:

SELECT *
FROM customs
WHERE DOES NOT EXIST
(SELECT *
FROM ords
WHERE customs.custom_id = ords.custom_id);

Two entries will be selected. Here are the results that you will get:

This example will return all entries from the customs table where there are no entries in the ords table for this custom_id.

The SQL EXISTS clause