How to get the Not Exists Record List from the Given List in Presto with ANSI SQL

Question

How to get the list of Not Exists Record List from the Given Input Condition?

If we are using Not IN Operator, it will result all the not matching records from the table. But I want to get the not matching record from the Given Input Condition.

I know this answer is there for oracle database but this is exclusively needed for Presto. Can someone help on this?

select i.column_value as country_code     
from table(SYS.DBMS_DEBUG_VC2COLL('AU', 'IN', 'ZA', 'DK', 'CH', 'NL')) i   
where not exists 
(select null                                
from country c                              
where c.country_code = i.column_value)

Answer 1

The Oracle SYS.DBMS_DEBUG_VC2COLL function is used to transform a comma-separated list into a list of rows, just like a select from a table. An "unnested" array in Presto should substitute for this, e.g.:

SELECT t.country_code
FROM UNNEST(ARRAY['AU', 'IN', 'ZA', 'DK', 'CH', 'NL']) AS t(country_code)
WHERE NOT EXISTS (
    SELECT 1
    FROM country c
    WHERE c.country_code = t.country_code
)