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