# 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](https://www.ionos.ca/digitalguide/server/configuration/sql-introduction-with-examples/), 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](https://www.ionos.ca/digitalguide/server/configuration/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:

```sql
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 parentheses.

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:

<table>
  <thead>
    <tr>
      <th>Customer Number</th>
      <th>Name</th>
      <th>Location</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>1427</td>
      <td>Smith</td>
      <td>New York</td>
    </tr>
    <tr>
      <td>1377</td>
      <td>Johnson</td>
      <td>Los Angeles</td>
    </tr>
    <tr>
      <td>1212</td>
      <td>Brown</td>
      <td>Los Angeles</td>
    </tr>
    <tr>
      <td>1431</td>
      <td>Davis</td>
      <td>Houston</td>
    </tr>
    <tr>
      <td>1118</td>
      <td>Wilson</td>
      <td>Phoenix</td>
    </tr>
  </tbody>
</table>

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:

<table>
  <thead>
    <tr>
      <th>Article number</th>
      <th>Customer number</th>
      <th>Order date</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>00282</td>
      <td>1172</td>
      <td>2024-17-01</td>
    </tr>
    <tr>
      <td>00311</td>
      <td>1361</td>
      <td>2024-19-01</td>
    </tr>
    <tr>
      <td>00106</td>
      <td>1431</td>
      <td>2024-19-01</td>
    </tr>
    <tr>
      <td>00378</td>
      <td>1274</td>
      <td>2024-30-01</td>
    </tr>
    <tr>
      <td>00418</td>
      <td>1118</td>
      <td>2024-03-02</td>
    </tr>
  </tbody>
</table>

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:

```sql
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:

<table>
  <thead>
    <tr>
      <th>Customer Number</th>
      <th>Name</th>
      <th>Location</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>1431</td>
      <td>Davis</td>
      <td>Houston</td>
    </tr>
    <tr>
      <td>1118</td>
      <td>Wilson</td>
      <td>Phoenix</td>
    </tr>
  </tbody>
</table>

## 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 Houston. The code looks like this:

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

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

<table>
  <thead>
    <tr>
      <th>Customer Number</th>
      <th>Name</th>
      <th>Location</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>1431</td>
      <td>Davis</td>
      <td>Houston</td>
    </tr>
  </tbody>
</table>

## 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`.

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

The output is:

<table>
  <thead>
    <tr>
      <th>Customer Number</th>
      <th>Name</th>
      <th>Location</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>1427</td>
      <td>Smith</td>
      <td>New York</td>
    </tr>
    <tr>
      <td>1377</td>
      <td>Johnson</td>
      <td>Los Angeles</td>
    </tr>
    <tr>
      <td>1212</td>
      <td>Brown</td>
      <td>Los Angeles</td>
    </tr>
  </tbody>
</table>

## 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.

Tip Top performance and personal advice! With [SQL Server Hosting](https://www.ionos.com/cloud/sql-server-hosting "SQL Server Hosting from IONOS") from IONOS you can choose between MSSQL, MySQL and MariaDB. Choose the plan that best suits your needs!


This is a markdown version of: [https://www.ionos.ca/digitalguide/server/configuration/sql-exists/](https://www.ionos.ca/digitalguide/server/configuration/sql-exists/) for AI/LLM consumption.