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 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 WITH SELECT STATEMENT

Let's look 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         www.techonthenet.com
       5000  Smith      Jane        www.digminecraft.com
       6000  Ferguson   Samantha    www.bigactivities.com
       7000  Reynolds   Allen       www.checkyourmath.com
       8000  Anderson   Paige
       9000  Johnson    Derek       www.techonthenet.com

And a table called orders with the following data:

ORDER_ID  CUSTOMER_ID
--------  -----------
       1         5000
       2         6000
       3         7000
       4         9000

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 samecustomer_id. Enter the following SELECT statement:

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

These are the results that you should see:

CUSTOMER_ID  LAST_NAME  FIRST_NAME  FAVORITE_WEBSITE
-----------  ---------  ----------  ---------------------
       5000  Smith      Jane        www.digminecraft.com
       6000  Ferguson   Samantha    www.bigactivities.com
       7000  Reynolds   Allen       www.checkyourmath.com
       9000  Johnson    Derek       www.techonthenet.com

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

EXAMPLE - USING NOT EXISTS WITH SELECT STATEMENT

The EXISTS condition can also be combined with the NOT operator to create a NOT EXISTS condition.

Let's use the NOT EXISTS condition to find all of the customers where there is not a record in the orders table with the samecustomer_id. Using the same customers and orders data as the previous example, enter the following SELECT statement:

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

These are the results that you should see:

CUSTOMER_ID  LAST_NAME  FIRST_NAME  FAVORITE_WEBSITE
-----------  ---------  ----------  ---------------------
       4000  Jackson    Joe         www.techonthenet.com
       8000  Anderson   Paige

In this example, there are 2 records in the customers table where the customer_id value does not appear in the orders table.

EXAMPLE - USING EXISTS WITH INSERT STATEMENT

Now, let's look at an example that shows how to use the EXISTS condition with an INSERT statement.

In this example, we have a contacts table that is empty and we want to populate:

CONTACT_ID  LAST_NAME  FIRST_NAME
----------  ---------  ----------

Let's insert into the contacts table all of the customer_id, last_name, and first_name values from the customers table where there is at least one record in the orders table with a matching customer_id.

INSERT INTO contacts
(contact_id, last_name, first_name)
SELECT customer_id, last_name, first_name
FROM customers
WHERE EXISTS (SELECT *
              FROM orders
              WHERE customers.customer_id = orders.customer_id);

Then select the data from the contacts table:

SELECT * FROM contacts;

These are the results that you should see:

CONTACT_ID  LAST_NAME  FIRST_NAME
----------  ---------  ----------
      5000  Smith      Jane
      6000  Ferguson   Samantha
      7000  Reynolds   Allen
      9000  Johnson    Derek

In this example, 4 records from the customers table will be inserted into the contacts table.

EXAMPLE - USING EXISTS WITH 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  QUANTITY
----------  ------------  --------
         1  Pear                25
         2  Banana               0
         3  Orange              18
         4  Apple               45

And a table called summary_data with the following data:

PRODUCT_ID  CURRENT_LEVELS
----------  --------------
         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_levels = (SELECT quantity
                 FROM products
                 WHERE products.product_id = summary_data.product_id)
WHERE EXISTS (SELECT quantity
                 FROM products
                 WHERE products.product_id = summary_data.product_id);

Then select the data from the summary_data table again:

SELECT * FROM summary_data;

These are the results that you should see:

PRODUCT_ID  CURRENT_LEVELS
----------  --------------
         1              25
         2               0
         3              18
         4              45
         5              10

In this example, 4 records would be updated in the summary_data table.

TIP: Notice that our UPDATE statement included an EXISTS condition in the WHERE clause to make sure that there was a matching product_id in both the products and summary_data table before updating the record.

If we hadn't included the EXISTS condition, the UPDATE query would have updated the current_levels field to NULL in the last row of the summary_data table (because the products table does not have a record where product_id is 5).

EXAMPLE - USING EXISTS WITH 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 products with the following data:

PRODUCT_ID  PRODUCT_NAME  QUANTITY
----------  ------------  --------
         1  Pear                25
         2  Banana               0
         3  Orange              18
         4  Apple               45

And a table called archival with the following data:

PRODUCT_ID
----------
         1
         2
         3
         4
         5

Now let's delete records from the archival table where there is at least one record in the products table with a matchingproduct_id. Enter the following SQL statement:

DELETE FROM archival
WHERE EXISTS (SELECT *
              FROM products
              WHERE products.product_id = archival.product_id);

Then select the data from the archival table again:

SELECT * FROM archival;

These are the results that you should see:

PRODUCT_ID
----------
         5

In this example, 4 records from the archival table will be deleted, leaving just one record remaining.