# exists {#exists}

It is used to test for the existence of any record in a subquery.

**Parent topic:**[SQL Reference](../../Programming/SQLStatements/SQLStatement.md)

## Syntax {#syntax}

```
where [not] exists(subquery)
```

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

## Details {#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 {#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|

