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
EXISTSoperator evaluate to true. Therefore, the query includes the curstomer in the result set. - In case the customer id does not exist in the
Invoicestable, the subquery returns no rows which causes theEXISTSoperator 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.