EXISTS

EXISTS [TEMPORARY] [TABLE|DICTIONARY|DATABASE] [db.]name [INTO OUTFILE filename] [FORMAT format]

Returns a single UInt8-type column, which contains the single value 0 if the table or database does not exist, or 1 if the table exists in the specified database.

Subquery form

The EXISTS operator checks whether a subquery returns any rows. It returns 0 if the subquery result is empty; otherwise, it returns 1. You can use EXISTS in a WHERE clause. The subquery cannot reference tables or columns from the outer query. Syntax

EXISTS(subquery)

Examples Check whether a subquery returns rows:

Query

SELECT
    EXISTS(SELECT * FROM numbers(10) WHERE number > 8),
    EXISTS(SELECT * FROM numbers(10) WHERE number > 11)

Response

┌─in(1, _subquery1)─┬─in(1, _subquery2)─┐
│                 1 │                 0 │
└───────────────────┴───────────────────┘

Use EXISTS in a WHERE clause with a subquery that returns several rows:

Query

SELECT count()
FROM numbers(10)
WHERE EXISTS(SELECT number FROM numbers(10) WHERE number > 8)

Response

┌─count()─┐
│      10 │
└─────────┘

When the subquery result is empty, EXISTS returns 0:

Query

SELECT count()
FROM numbers(10)
WHERE EXISTS(SELECT number FROM numbers(10) WHERE number > 11)

Response

┌─count()─┐
│       0 │
└─────────┘

SQL Reference

Data Types

Engines

Functions

Formats

Settings

System Tables

Data Lakes

Subquery form

​Subquery form

Subquery form