SQL EXISTS Operator

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 EXISTSoperator checks if a subquery returns any rows. The following illustrates the syntax of the EXISTSoperator:

WHERE EXISTS (subquery)Code language: SQL (Structured Query Language) (sql)

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

You can use the NOToperator with the EXISTSoperator to inverse the meaning of the EXISTSoperator.

WHERE NOT EXISTS (subquery);

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

SQL EXISTS Operator examples

You can use the EXISTSoperator to find a customer who has ordered products. For each customer in the customerstable, you check if there is at least one order exists in the orderstable.

SELECT 
    customerid, companyName
FROM
    customers
WHERE
    EXISTS( 
	SELECT 
            orderid
        FROM
            orders
        WHERE
            orders.customerid = customers.customerid);Code language: SQL (Structured Query Language) (sql)

SQL EXISTS operator with a 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: SQL (Structured Query Language) (sql)

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 orderstable:

SELECT 
    customerid, companyName
FROM
    customers
WHERE
    NOT EXISTS( 
        SELECT 
            orderid
        FROM
            orders
        WHERE
            orders.customerid = customers.customerid);Code language: SQL (Structured Query Language) (sql)

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.