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 EXISTS condition in SQL 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 EXISTS condition are very inefficient since the sub-query is rerun for EVERY row in the outer query's table. There are more efficient ways to write most queries, that do not use the EXISTS condition.

DDL/DML for Examples

If you want to follow along with this tutorial, get the DDL to create the tables and the DML to populate the data. Then try the examples in your own database!

Get DDL/DML

Example - Using EXISTS Condition with the SELECT Statement

Let's start by looking at an example that shows how to use the EXISTS condition with a SELECT statement.

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

customer_id	last_name	first_name	favorite_website
4000	Jackson	Joe	google.com
5000	Smith	Jane	digminecraft.com
6000	Ferguson	Samantha	bigactivities.com
7000	Reynolds	Allen	checkyourmath.com
8000	Anderson	Paige	NULL
9000	Johnson	Derek	google.com

And a table called orders with the following data:

order_id	customer_id	order_date
1	7000	2016/04/18
2	5000	2016/04/18
3	8000	2016/04/19
4	4000	2016/04/20

Now let's find all of the records from the customers table where there is at least one record in the orders table with the same customer_id. Enter the following SELECT statement:

SELECT *
FROM customers
WHERE EXISTS 
  (SELECT *
   FROM orders
   WHERE customers.customer_id = orders.customer_id);

There will be 4 records selected. These are the results that you should see:

customer_id	last_name	first_name	favorite_website
4000	Jackson	Joe	google.com
5000	Smith	Jane	digminecraft.com
7000	Reynolds	Allen	checkyourmath.com
8000	Anderson	Paige	NULL

In this example, there are 4 records in the customers where the customer_id value appears in the orders table.

Example - Using EXISTS Condition with the UPDATE Statement

Let's look at an example that uses the EXISTS condition in an UPDATE statement.

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

product_id	product_name	category_id
1	Pear	50
2	Banana	50
3	Orange	50
4	Apple	50
5	Bread	75
6	Sliced Ham	25
7	Kleenex	NULL

And a table called summary_data with the following data:

product_id	current_category
1	10
2	10
3	10
4	10
5	10

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

UPDATE summary_data
SET current_category = (SELECT category_id
   FROM products
   WHERE products.product_id = summary_data.product_id)
WHERE EXISTS (SELECT category_id
   FROM products
   WHERE products.product_id = summary_data.product_id);

There will be 5 records update. Select the data from the summary_data table again:

SELECT * FROM summary_data;

These are the results that you should see:

product_id	current_category
1	50
2	50
3	50
4	50
5	75
8	10

This example would update the current_category field in the summary_data table with the category_id from the products table where the product_id values match. The first 5 records in the summary_data table have been updated.

TIP: If we hadn't included the EXISTS condition, the UPDATE query would have updated the current_category field to NULL in the 6th row of the summary_data table (because the products table does not have a record where product_id=8).

Example - Using EXISTS Condition with the DELETE Statement

Let's look at an example that uses the EXISTS condition in a DELETE statement.

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

customer_id	last_name	first_name	favorite_website
4000	Jackson	Joe	google.com
5000	Smith	Jane	digminecraft.com
6000	Ferguson	Samantha	bigactivities.com
7000	Reynolds	Allen	checkyourmath.com
8000	Anderson	Paige	NULL
9000	Johnson	Derek	google.com

And a table called orders with the following data:

order_id	customer_id	order_date
1	7000	2016/04/18
2	5000	2016/04/18
3	8000	2016/04/19
4	4000	2016/04/20
5	NULL	2016/05/01

Enter the following DELETE statement:

DELETE FROM orders
WHERE EXISTS
  (SELECT *
   FROM customers
   WHERE customers.customer_id = orders.customer_id
   AND customers.last_name = 'Jackson');

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

SELECT * FROM orders;

These are the results that you should see:

order_id	customer_id	order_date
1	7000	2016/04/18
2	5000	2016/04/18
3	8000	2016/04/19
5	NULL	2016/05/01

This example would delete all records from the orders table where there is a record in the customers table with the last_name of 'Jackson' and a matching customer_id value in both tables. In this example, the record for order_id=4 was deleted.

If you want to determine the number of rows that will be deleted, you can run the following SELECT statement before performing the delete.

SELECT COUNT(*) FROM orders
WHERE EXISTS
  (SELECT *
   FROM customers
   WHERE customers.customer_id = orders.customer_id
   AND customers.last_name = 'Jackson');

This will return number of records that will be deleted when you execute the DELETE statement.

COUNT(*)
1

Example - Using NOT with the EXISTS Condition

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

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

customer_id	last_name	first_name	favorite_website
4000	Jackson	Joe	google.com
5000	Smith	Jane	digminecraft.com
6000	Ferguson	Samantha	bigactivities.com
7000	Reynolds	Allen	checkyourmath.com
8000	Anderson	Paige	NULL
9000	Johnson	Derek	google.com

And a table called orders with the following data:

order_id	customer_id	order_date
1	7000	2016/04/18
2	5000	2016/04/18
3	8000	2016/04/19
4	4000	2016/04/20
5	NULL	2016/05/01

Enter the following SQL statement:

SELECT *
FROM customers
WHERE NOT EXISTS
  (SELECT * 
   FROM orders
   WHERE customers.customer_id = orders.customer_id);

There will be 2 records selected. These are the results that you should see:

customer_id	last_name	first_name	favorite_website
6000	Ferguson	Samantha	bigactivities.com
9000	Johnson	Derek	google.com

This example would return all records from the customers table where there are no records in the orders table for the given customer_id.