SQL EXISTS condition

Preamble

If a subquery returns at least one line, the SQL EXISTS condition is used with it and is said to be satisfied. The SELECT, INSERT, UPDATE, and DELETE operators all support its use.

Syntax of EXISTS condition in SQL

WHERE EXISTS ( subquery_id );

where:

• If a subquery returns at least one record in its result set, the EXISTS condition is satisfied and the EXISTS offer is evaluated as true. If the subquery returns no records, the EXISTS sentence is evaluated as false and the EXISTS condition is not satisfied.

Note:
SQL statements using the EXISTS condition are very inefficient because the subquery is run again for EVERY line in the external query table. The majority of queries that don’t use the EXISTS condition can be written more effectively.

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 take a look at an instance of the UPDATE operator that makes use of the EXISTS condition.

In this illustration, the product table contains the information below:

And a table named sum_data with the following data:

Let’s now add values from the products table to the sum_data table. Type the following SQL query:

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 case, where prod_id values match, the current_cat field in the sum_data table will be updated with cat_id information from the products table. The sum_data table’s first five records were updated.

If the EXISTS condition wasn’t turned on, the current_category field in the sixth row of the summary_data table would be changed to NULL because there is no record in the products table with the product_id = 8 value.

EXISTS Condition with DELETE Operator

Let’s take a look at a DELETE operator example that makes use of the EXISTS condition.

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’);

The deletion of 1 record. Re-select the information from the order table:

SELECT * FROM ords;

here are the results you should get:

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

NOT with EXISTS condition

Finally, the NOT EXISTS condition can be produced by combining the NOT condition with the EXISTS condition. Let’s take a look at a SQL example that demonstrates how to use the NOT EXISTS condition.

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

And the table with the following data:

Type the following SQL query:

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

There will be two winners. These are the outcomes you’ll get:

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

The SQL EXISTS clause

About Enteros

​​​​Enteros offers a patented database performance management SaaS platform. It finds the root causes of complex database scalability and performance problems that affect business across a growing number of cloud, RDBMS, NoSQL, and machine learning database platforms.