EXISTS

Syntax

Parameters

• subquery

  A correlated subquery

Returns

A Boolean that is true if and only if the the correlated subquery contains any row.

Examples

Existence of an element within an array

This supports queries with existential predicates that could be used, for example, to check whether a certain repeated field matches certain criteria.

Let’s assume the following table:

CREATE TYPE AS STRUCT restaurant_review (reviewer STRING, rating INT64);
CREATE TABLE restaurant (
    rest_no INT64,
    name STRING,
    reviews restaurant_review ARRAY,
    PRIMARY KEY(rest_no));

and the following records:

{"REST_NO": 42, "name": "Restaurant1", "reviews":
    [{"reviewer": "Reviewer11", "rating": 2},
    {"reviewer": "Reviewer22", "rating": 5},
    {"reviewer": "Reviewer21", "rating": 1}]}
{"REST_NO": 43, "name": "Restaurant2", "reviews":
    [{"reviewer": "Reviewer19", "rating": 1}]}
{"REST_NO": 44, "name": "Restaurant3", "reviews":
    [{"reviewer": "Reviewer41", "rating": 3},
    {"reviewer": "Reviewer55", "rating": 5}]}
{"REST_NO": 45, "name": "Restaurant4", "reviews":
    [{"reviewer": "Reviewer14", "rating": 3},
    {"reviewer": "Reviewer55", "rating": 2}]}

For example, suppose we want to return a list of all restaurants with at least one review with a (good) rating, that is, larger or equal to 5. Since reviews is a repeated field in RestaurantRecord, it is not possible to query it via a simple predicate. However, we can use an existential predicate to iterate over its values using a correlated alias to the parent table and apply, for each review, the predicate on its rating:

select * from restaurant as R where exists (select * from R.reviews where rating >= 5);

For the data above, this query will return:

rest_no	name	reviews
42	"Restaurant1"	[{"reviewer": "Reviewer11","rating": "2"}, {"reviewer": "Reviewer22","rating": "5"}, {"reviewer": "Reviewer21","rating": "1"}]
44	"Restaurant3"	[{"reviewer": "Reviewer41","rating": "3"}, {"reviewer": "Reviewer55","rating": "5"}]