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)
Code language: SQL (Structured Query Language) (sql)

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)Code language: SQL (Structured Query Language) (sql)

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: