SQL EXISTS Operator

06 Feb 2014

EXISTS operator returns value 0 or 1, depends on the right-hand side operand of it.

Theoretically, EXISTS should return as soon as there is one row found in the right-hand operand, which should be the correct behavior and avoiding unnecessary table scan as compared to using COUNT().

However, the real behavior of EXISTS depends on the actual implementation of various database engine. For SQLite, it is not clear whether it deviates from standard behavior or not, according to an excerpt from SQLite’s website:

The EXISTS operator

The EXISTS operator always evaluates to one of the integer values 0 and 1. If executing the SELECT statement specified as the right-hand operand of the EXISTS operator would return one or more rows, then the EXISTS operator evaluates to 1. If executing the SELECT would return no rows at all, then the EXISTS operator evaluates to 0.

The number of columns in each row returned by the SELECT statement (if any) and the specific values returned have no effect on the results of the EXISTS operator. In particular, rows containing NULL values are not handled any differently from rows without NULL values.

However, if the right-hand side operand is a SELECT scalar subquery, then SQLite’s EXISTS operator will definitely return as soon as there is already one row found in the right-hand side operand. This can be explained by another excerpt from SQLite’s website:

Scalar Subqueries

A SELECT statement enclosed in parentheses may appear as a scalar quantity. A SELECT used as a scalar quantity must return a result set with a single column. The result of the expression is the value of the only column in the first row returned by the SELECT statement. If the SELECT yields more than one result row, all rows after the first are ignored. If the SELECT yields no rows, then the value of the expression is NULL. The LIMIT of a scalar subquery is always 1. Any other LIMIT value given in the SQL text is ignored.

All types of SELECT statement, including aggregate and compound SELECT queries (queries with keywords like UNION or EXCEPT) are allowed as scalar subqueries.

In conclusion, in SQLite, a scalar subquery returns only one row, if there is any; and EXISTS operator will only see one row at most if the right-hand side operand is a scalar subquery!