exists

where [not] exists(subquery)

subquery: is a select/exec statement. The filter condition can contain fields in the outer query.

• 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.

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)

select * from t1 where not exists(select * from t2 where t1.sym in t2.sym)

Query 2: uncorrelated subquery

select * from t1 where exists(select * from t2 where flag >= 0)

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)