EXISTS operator returns value 0 or 1, depends on the right-hand side operand of it.
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
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:
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