Filtering with exists in BigQuery-CodePudding

I'm running the following query in bigQuery and I don't have the expected output. Isn't that only the row with Alpha should be returned?

SELECT * FROM UNNEST([
  STRUCT(NULL AS a, '' AS b),
  (1, 'Alpha'),
  (2, 'Bravo'),
  (3, 'Charlie'),
  (4, 'Delta')
])
WHERE EXISTS (SELECT * FROM UNNEST([
  STRUCT(NULL AS a, '' AS b),
  (1, 'Alpha')
]))

CodePudding user response：

Use below instead

SELECT * FROM UNNEST([
  STRUCT(NULL AS a, '' AS b),
  (1, 'Alpha'),
  (2, 'Bravo'),
  (3, 'Charlie'),
  (4, 'Delta')
])
WHERE (a,b) in UNNEST([
  STRUCT(NULL AS a, '' AS b),
  (1, 'Alpha')
])

with output

CodePudding user response：

You forgot the WHERE clause in your EXISTS subquery, that's why it always return rows and is evaluated to true.

Try this:

SELECT * FROM UNNEST([
  STRUCT(NULL AS a, '' AS b),
  (1, 'Alpha'),
  (2, 'Bravo'),
  (3, 'Charlie'),
  (4, 'Delta')
]) t1
WHERE EXISTS (
    SELECT 1 FROM UNNEST([
        STRUCT(NULL AS a, '' AS b),
              (1, 'Alpha')
       ]) t2
    WHERE t1.a = t2.a -- add this condition
)