exists
It is used to test for the existence of any record in a subquery.
Syntax
$ where [not] exists(subquery)
subquery:is a select/exec statement. The filter condition can contain fields in the outer query.
Details
A correlated subquery is a subquery that refers to a column of a table that is not in its from clause. The
exists
operator tests for existence of rows in the results set of the subquery.If a subquery row value is found, the condition is flagged true and the search does not continue in the inner query;
If it is not found, then the condition is flagged false and the search continues in the inner query.
For uncorrelated subqueries,
If the result of subquery is not empty, return all results of the outer query;
If the result of subquery is empty, return NULL for the outer query.
The keyword not exists
works the opposite of exists
.
Note: Currently the SQL keywords exists/not exists
do not support distributed queries.
Examples
$ t1 = table(`a`b`c`a`e`f as sym, 3.1 2.9 3.0 2.8 3.2 2.9 as val)
$ t2 = table(`a`b`c as sym, 0 1 -1 as flag)
Query 1: correlated subquery
$ select * from t1 where exists(select * from t2 where t1.sym in t2.sym)
sym | val |
---|---|
a | 3.1 |
b | 2.9 |
c | 3 |
a | 2.8 |
$ select * from t1 where not exists(select * from t2 where t1.sym in t2.sym)
sym | val |
---|---|
e | 3.2 |
f | 2.9 |
Query 2: uncorrelated subquery
$ select * from t1 where exists(select * from t2 where flag >= 0)
sym | val |
---|---|
a | 3.1 |
b | 2.9 |
c | 3 |
a | 2.8 |
e | 3.2 |
f | 2.9 |
$ select * from t1 where not exists(select * from t2 where flag >= 0)
//the query returns NULL
Query 3: subquery returns NULL
$ select * from t1 where exists(select * from t2 where sym=`e)
//the query returns NULL
$ select * from t1 where not exists(select * from t2 where sym=`e)
sym | val |
---|---|
a | 3.1 |
b | 2.9 |
c | 3 |
a | 2.8 |
e | 3.2 |
f | 2.9 |