SQLite EXISTS

Summary: in this tutorial, you will learn how to use the SQLite EXISTS operator to test for the existence of rows returned by a subquery.

Introduction to SQLite EXISTS operator

The EXISTS operator is a logical operator that checks whether a subquery returns any row.

Here is the basic syntax of the EXISTS operator:

EXISTS(subquery)

In this syntax, the subquery is a SELECT statement that returns zero or more rows.

If the subquery returns one or more row, the EXISTS operator return true. Otherwise, the EXISTS operator returns false or NULL.

Note that if the subquery returns one row with NULL, the result of the EXISTS operator is still true because the result set contains one row with NULL.

To negate the EXISTS operator, you use the NOT EXISTS operator as follows:

NOT EXISTS (subquery)

The NOT EXISTS operator returns true if the subquery returns no row.

SQLite EXISTS operator example

See the following Customers and Invoices tables from the sample database:

The following statement finds customers who have invoices:

SELECT
    CustomerId,
    FirstName,
    LastName,
    Company
FROM
    Customers c
WHERE
    EXISTS (
        SELECT 
            1 
        FROM 
            Invoices
        WHERE 
            CustomerId = c.CustomerId
    )
ORDER BY
    FirstName,
    LastName; 

The following picture shows the partial result set:

In this example, for each customer, the EXISTS operator checks if the customer id exists in the invoices table.

• If yes, the subquery returns one row with value 1 that causes the EXISTS operator evaluate to true. Therefore, the query includes the curstomer in the result set.
• In case the customer id does not exist in the Invoices table, the subquery returns no rows which causes the EXISTS operator to evaluate to false, hence the query does not include the customer in the result set.

Notice that you can use the IN operator instead of EXISTS operator in this case to achieve the same result:

SELECT
   CustomerId, 
   FirstName, 
   LastName, 
   Company
FROM
   Customers c
WHERE
   CustomerId IN (
      SELECT
         CustomerId
      FROM
         Invoices
   )
ORDER BY
   FirstName, 
   LastName;

Once the subquery returns the first row, the EXISTS operator stops searching because it can determine the result. On the other hand, the IN operator must scan all rows returned by the subquery to determine the result.

Generally speaking, the EXISTS operator is faster than IN operator if the result set returned by the subquery is large. By contrast, the IN operator is faster than the EXISTS operator if the result set returned by the subquery is small.

SQLite NOT EXISTS operator example

See the following Artists and Albums table from the sample database:

This query find all artists who do not have any album in the Albums table:

SELECT
   *
FROM
   Artists a
WHERE
   NOT EXISTS(
      SELECT
         1
      FROM
         Albums
      WHERE
         ArtistId = a.ArtistId
   )
ORDER BY Name;

Here is the partial output:

In this tutorial, you have learned how to use the SQLite EXISTS operator to test for the existence of rows returned by a subquery.