How to use SQL EXISTS to check subqueries 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 conditions.

What is SQL EXISTS?

In Structured Query Language, you can use ‘subqueries’ for more complex queries. To check whether a specific value exists within one of these subqueries, 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 combination with SELECT. The corresponding 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 corresponding 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 illustrate the meaning and functionality 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 corresponding entries are removed from the list. Our table now looks like this:

Query multiple conditions

You can also query several conditions further specifying 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:

Combination with NOT

SQL EXISTS also provides the option to check for conditions 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:

Alternatives for SQL EXISTS

There are several alternatives to SQL EXISTS that you can also use to check subqueries for specific entries. The most practical options are the IN and JOIN operators, both of which you can also specify according to your needs.