Basic about EXISTS Condition – using and example

This SQL tutorial explains how to use the SQL EXISTS circumstance with syntax and examples.

Description

The SQL EXISTS condition is used in combination with a subquery and is viewed 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 consider to authentic and the EXISTS situation will be met. If the subquery does now not return any records, the EXISTS clause will consider to false and the EXISTS condition will no longer be met.

Note

SQL statements that use the EXISTS condition are very inefficient considering the sub-query is rerun for EVERY row in the outer query’s table. There are extra efficient ways to write most queries, that do not use the EXISTS condition.

DDL/DML for Examples

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

Get DDL/DML

Example – Using EXISTS Condition with the SELECT Statement

Let’s start by using searching at an instance that indicates how to use the EXISTS situation with a SELECT statement.

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

customer_id last_name first_name favorite_website 4000 Jackson Joe techonthenet.com 5000 Smith Jane digminecraft.com 6000 Ferguson Samantha bigactivities.com 7000 Reynolds Allen checkyourmath.com 8000 Anderson Paige NULL 9000 Johnson Derek techonthenet.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 locate all of the records from the clients table the place there is at least one file in the orders table with the same customer_id. Enter the following SELECT statement:

Try It

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

There will be 4 information selected. These are the outcomes that you have to see:

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

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

Example – Using EXISTS Condition with the UPDATE Statement

Let’s appear at an instance that uses the EXISTS circumstance in an UPDATE statement.

In this example, we have a desk 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 desk 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 documents update. Select the records 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 instance would update the current_category area in the summary_data desk with the category_id from the products table where the product_id values match. The first 5 documents in the summary_data desk have been updated.

TIP: If we hadn’t covered the EXISTS condition, the UPDATE query would have updated the current_category field to NULL in the sixth row of the summary_data table (because the merchandise table does not have a report the place product_id=8).

Example – Using EXISTS Condition with the DELETE Statement

Let’s appear at an example that uses the EXISTS condition in a DELETE statement.

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

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

And a desk referred to as 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 document deleted. Select the data from the orders desk 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 instance would delete all records from the orders desk the place there is a file in the clients desk with the last_name of ‘Jackson’ and a matching customer_id cost in both tables. In this example, the document for order_id=4 used to be deleted.

If you favor to decide the quantity of rows that will be deleted, you can run the following SELECT announcement earlier than 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 variety of data that will be deleted when you execute the DELETE statement.

COUNT(*)

1

Example – Using NOT with the EXISTS Condition

Finally, the NOT situation can be blended with the EXISTS condition to create a NOT EXISTS condition. Let’s seem at an example that indicates how to use the NOT EXISTS situation in SQL.

In this example, we have a desk called clients with the following data:

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

And a desk referred to as 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:

Try It

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

There will be 2 data selected. These are the consequences that you see:

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

This instance would return all documents from the clients table where there are no files in the orders desk for the given customer_id.