0

I have a list of IDs

'ACE', 'ACD', 'IDs', 'IN','CD'

I also have a table similar to following structure

ID value
ACE  2
CED  3
ACD  4
IN   4
IN   4

I want a SQL query that returns a list of IDs that exists in the database and a list of IDs that does not in the database.

The return should be:

1.ACE, ACD, IN (exist)
2.IDs,CD (not exist)

my code is like this

select 
    ID,
    value
from db
where ID is in ( 'ACE', 'ACD', 'IDs', 'IN','CD')

however, the return is 1) super slow with all kinds of IDs 2) return multiple rows with the same ID. Is there anyway using postgresql to return 1) unique ID 2) make the running faster?

codelearner0504
  • 71
  • 6
  • 1
    That's a _table_, not a _database_. A database typically consists of several tables. – jarlh Jan 25 '21 at 16:33
  • Please declare your Postgres version and the actual table definition (`CREATE TABLE` script showing data types and constraints). Can there be duplicates in the list or in the table column? If so, how to deal with those? – Erwin Brandstetter Jan 25 '21 at 19:27

2 Answers2

1

Assuming no duplicates in table nor input, this query should do it:

SELECT t.id IS NOT NULL AS id_exists
     , array_agg(ids.id)
FROM   unnest(ARRAY['ACE','ACD','IDs','IN','CD']) ids(id)
LEFT   JOIN tbl t USING (id)
GROUP  BY 1;

Else, please define how to deal with duplicates on either side.

If the LEFT JOIN finds a matching row, the expression t.id IS NOT NULL is true. Else it's false. GROUP BY 1 groups by this expression (1st in the SELECT list), array_agg() forms arrays for each of the two groups. Related:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
0

Hmmm . . . Is this sufficient:

select ids.id,
       (exists (select 1 from table t where t.id = ids.id)
from unnest(array['ACE', 'ACD', 'IDs', 'IN','CD']) ids(id);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786