Share via

exists function

  • Article

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

Returns true if func is true for any element in expr or query returns at least one row.

Syntax

exists(expr, func)

exists(query)

Arguments

  • expr: An ARRAY expression.
  • func: A lambda function.
  • query: Any Query.

Returns

A BOOLEAN.

The lambda function must result in a boolean and operate on one parameter, which represents an element in the array.

exists(query) can only be used in the WHERE clause and few other specific cases.

Examples

> SELECT exists(array(1, 2, 3), x -> x % 2 == 0);
 true
> SELECT exists(array(1, 2, 3), x -> x % 2 == 10);
 false
> SELECT exists(array(1, NULL, 3), x -> x % 2 == 0);
 NULL
> SELECT exists(array(0, NULL, 2, 3, NULL), x -> x IS NULL);
 true
> SELECT exists(array(1, 2, 3), x -> x IS NULL);
 false

> SELECT count(*) FROM VALUES(1)
   WHERE exists(SELECT * FROM VALUES(1), (2), (3) AS t(c1) WHERE c1 = 2);
  1
> SELECT count(*) FROM VALUES(1)
   WHERE exists(SELECT * FROM VALUES(1), (NULL), (3) AS t(c1) WHERE c1 = 2);
  0
> SELECT count(*) FROM VALUES(1)
     WHERE NOT exists(SELECT * FROM VALUES(1), (NULL), (3) AS t(c1) WHERE c1 = 2);
  1