How to use SQL EXISTS to check sub­quer­ies for certain values

You can use the SQL EXISTS operator to check a subquery for a specific entry. The result is a Boolean value. It’s also possible to query several con­di­tions.

What is SQL EXISTS?

In Struc­tured Query Language, you can use ‘sub­quer­ies’ for more complex queries. To check whether a specific value exists within one of these sub­quer­ies, you can use the SQL EXISTS operator. This operator applies a defined condition to the subquery and returns TRUE if the condition is met. Only then will the main query be executed. SQL EXISTS can be used with the SQL commands DELETE, INSERT, SELECT and UPDATE.

Syntax and function

In the following, we’ll show you how SQL EXISTS works in com­bin­a­tion with SELECT. The cor­res­pond­ing syntax is as follows:

SELECT name_of_column(s) 
FROM name_of_table 
WHERE EXISTS 
(SELECT name_of_column FROM name_of_table WHERE condition);

name_of_column(s) denotes the column or columns to be filtered by the SELECT statement. Then, specify the name of the table where the command is to be executed. The subquery is checked using WHERE EXISTS. At this point, enter the cor­res­pond­ing subquery in brackets.

When the code is executed, the higher-level query is initiated first. The system then runs the subquery. If the subquery returns a result (i.e. TRUE), the result of the main query is taken into account. However, if the result of the sub-query is NULL, the result of the main query is also skipped.

Example of using the operator

The easiest way to il­lus­trate the meaning and func­tion­al­ity of SQL EXISTS is via an example. To do this, we create two different tables. The first table is called ‘Customer list’. It lists various customers of a company with their customer number, name and location. This table looks like this:

Our second table is called ‘Orders’. It contains an article number, the customer number and the order date for each entry. This is what it looks like:

Now we can filter which customers placed at least one order in the period from January 17 to February 3. We use the following code for this:

SELECT customer number, name, location 
FROM customer_list 
WHERE EXISTS 
(SELECT * FROM orders WHERE customerlist.customernumber = orders.customernumber);

The system now checks whether customer numbers from the customer list also appear in the orders. If this is the case (i.e. if the value is TRUE), the cor­res­pond­ing entries are removed from the list. Our table now looks like this:

Query multiple con­di­tions

You can also query several con­di­tions further spe­cify­ing your selection. In the following example, we want to check whether certain customer numbers are included and whether the location is Winchester. The code looks like this:

SELECT customer number, name, location 
FROM customer_list 
WHERE EXISTS 
(SELECT * FROM orders WHERE customerlist.customernumber = orders.customernumber AND location = 'Winchester');

Here too, the result is TRUE and the output is this:

Com­bin­a­tion with NOT

SQL EXISTS also provides the option to check for con­di­tions in the reverse way. In the following example, we query all customers who have not placed an order in the specified time frame. To do this, we use the addition NOT.

SELECT customer number, name, location 
FROM customer_list 
WHERE NOT EXISTS 
(SELECT * FROM orders WHERE customerlist.customernumber = orders.customernumber);

The output is:

Al­tern­at­ives for SQL EXISTS

There are several al­tern­at­ives to SQL EXISTS that you can also use to check sub­quer­ies for specific entries. The most practical options are the IN and JOIN operators, both of which you can also specify according to your needs.