How to validate that each value of 'IN' statement are present in the SQL query result?

Question

I have a set of tableA

Name	City
Paulo	Rome
Rudy	Singapore
Ming	Singapore
Takeshi	Tokyo
Judy	Jakarta
Yuki	Tokyo
Steve	Singapore

I want to make sure that person from Berlin, Singapore, and Tokyo, all 3 of those cities are present in the table

I have this SQL query to check that each city is present

select a.*
from (
select *,
  row_number() over (partition by city ) as rn
from tableA
where city in (Berlin, Tokyo, Singapore)
) a
where rn = 1

With this query I can find out that at least 1 person from those countries is present, while actually there's none from Berlin. What I want is the query can validate that all of cities that in IN condition are present. It will return True if all of them is present and False if one of them is missing. Is it possible to do so? Suggestion for simpler query also appreciated.

Answer 1

You can compare count of cities in IN(...) and your query output rows count

select case when count(*)=3 then 'true' else 'false' end res
from (
  select *,
    row_number() over (partition by city order by name) as rn
  from tableA
  where city in ('Berlin', 'Tokyo', 'Singapore')
) a
where rn = 1

res='false'

OR simpler

select case when count(*)=3 then 'true' else 'false' end res
from(
  select distinct city
  from tableA
  where city in ('Berlin', 'Tokyo', 'Singapore')
) a

Example

With @jarlh suggestion

select case when count(distinct city)=3 then 'true' else 'false' end res
from tableA
where city in ('Berlin', 'Tokyo', 'Singapore')

I can't check it on amazon-athena, but it's a beautiful solution!