Chris.Hansen: Import ClickHouse Docs: Wed Aug 28 2024 14:52:43 GMT-0400 (Eastern Daylight Time)

2024-08-28T18:52:43Z

Import ClickHouse Docs: Wed Aug 28 2024 14:52:43 GMT-0400 (Eastern Daylight Time)

New page

The <code>EXISTS</code> operator checks how many records are in the result of a subquery. If it is empty, then the operator returns <code>0</code>. Otherwise, it returns <code>1</code>.

<code>EXISTS</code> can also be used in a [[Relational_Data_-_Statements_-_Select_-_Where|WHERE]] clause.

<div class="tip">

References to main query tables and columns are not supported in a subquery.

</div>
'''Syntax'''

<syntaxhighlight lang="sql">EXISTS(subquery)</syntaxhighlight>
'''Example'''

Query checking existence of values in a subquery:

<syntaxhighlight lang="sql">SELECT EXISTS(SELECT * FROM numbers(10) WHERE number > 8), EXISTS(SELECT * FROM numbers(10) WHERE number > 11)</syntaxhighlight>
Result:

<syntaxhighlight lang="text">┌─in(1, _subquery1)─┬─in(1, _subquery2)─┐
│ 1 │ 0 │
└───────────────────┴───────────────────┘</syntaxhighlight>
Query with a subquery returning several rows:

<syntaxhighlight lang="sql">SELECT count() FROM numbers(10) WHERE EXISTS(SELECT number FROM numbers(10) WHERE number > 8);</syntaxhighlight>
Result:

<syntaxhighlight lang="text">┌─count()─┐
│ 10 │
└─────────┘</syntaxhighlight>
Query with a subquery that returns an empty result:

<syntaxhighlight lang="sql">SELECT count() FROM numbers(10) WHERE EXISTS(SELECT number FROM numbers(10) WHERE number > 11);</syntaxhighlight>
Result:

<syntaxhighlight lang="text">┌─count()─┐
│ 0 │
└─────────┘</syntaxhighlight>
[[Category:Relational_Data]]

Relational Data - Operators - Exists - Revision history

Chris.Hansen: Import ClickHouse Docs: Wed Aug 28 2024 14:52:43 GMT-0400 (Eastern Daylight Time)