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