Test For Existence of Rows From a Subquery Using SQL EXISTS

Summary: in this tutorial, you will learn how to use SQL EXISTS operator to test if a subquery returns any rows.

This tutorial requires a good knowledge of the subquery concept. If you do not know anything about subquery, you can follow the SQL subquery tutorial before going forward with this tutorial.

Introduction to SQL EXISTS operator

The EXISTS operator checks if a subquery returns any rows. The following illustrates the syntax of the EXISTS operator:

WHERE EXISTS (subquery)

The expression  EXISTS (subquery) returns TRUE if the subquery returns at least one row, otherwise it returns FALSE. Notice that you put the subquery inside the parentheses followed by the EXISTS operator.

You can use the NOT operator with the EXISTS operator to inverse the meaning of the EXISTS operator.

WHERE NOT EXISTS (subquery);

The expression  NOT EXISTS (subquery) returns TRUE if the subquery returns no row, otherwise it returns FALSE. You can use the EXISTS operator in any SQL statement that accepts a WHERE clause e.g., SELECTUPDATE or DELETE statement.

SQL EXISTS Operator examples

You can use the EXISTS operator to find a customer who has ordered products. For each customer in the customers table, you check if there is at least one order exists in the orders table.

SELECT customerid, companyName FROM customers WHERE EXISTS( SELECT orderid FROM orders WHERE orders.customerid = customers.customerid);

SQL EXISTS customer with orders

SQL EXISTS operator with subquery that returns NULL

If a subquery returns NULL, the expression  EXIST NULL returns TRUE. Let’s take a look at the following example:

SELECT customerId, companyName FROM customers WHERE EXISTS( SELECT NULL);
Code language: PHP (php)

SQL EXISTS NULL

SQL NOT EXISTS example

You can use the  NOT EXIST to find the customer who has never purchased anything by checking the number of orders of the customer in the orders table:

SELECT customerid, companyName FROM customers WHERE NOT EXISTS( SELECT orderid FROM orders WHERE orders.customerid = customers.customerid);
SQL NOT EXISTS example

In this tutorial, we have shown you how to use the SQL EXISTS operator to test for the existence of rows returned from a subquery.