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 |