CODEWITHSUNDEEP
sqlpostgresql
Ráfagan
Ráfagan

Reputation: 2255

How can I find array value occurrences and missing ones in a single query?

Considering an array of numeric strings, e.g. arr = ['202', '303', '1', ...] and the following table:

MyTable
code VARCHAR(255)

Is possible to find in a single query:

  1. All values in arr which doesn't exists in MyTable.code?
  2. All values of MyTable.code which doesn't exists in arr?

There's some way to achieve that?

I'm using PostgreSQL.

Upvotes: 1

Views: 269

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269883

You can unnest the array and use joins or something similar

For the first condition:

select el
from unnest(ar) el 
where not exists (select 1 from t where t.code = el);

Similar logic can be used for the second, but you might want select distinct:

select t.code
from t 
where not exists (select 1 from unnest(ar) el  where t.code = el);

If you want both in a single query, you can use union all or full join:

select el, t.code
from unnest(ar) el full join
     t 
     on t.code = el
where t.code is null or el is null;

Upvotes: 2

Related Questions