Alternative SQL ways of looking up multiple items of known IDs?

Question

Is there a better solution to the problem of looking up multiple known IDs in a table:

SELECT * FROM some_table WHERE id='1001' OR id='2002' OR id='3003' OR ...

I can have several hundreds of known items. Ideas?

Answer 1

See Arrays and Lists in SQL Server 2005

Answer 2

SELECT * FROM some_table WHERE ID IN ('1001', '1002', '1003')

and if your known IDs are coming from another table

SELECT * FROM some_table WHERE ID IN (
    SELECT KnownID FROM some_other_table WHERE someCondition 
)

Answer 3

For a fixed set of IDs you can do:

SELECT * FROM some_table WHERE id IN (1001, 2002, 3003);

For a set that changes each time, you might want to create a table to hold them and then query:

SELECT * FROM some_table WHERE id IN 
  (SELECT id FROM selected_ids WHERE key=123);

Another approach is to use collections - the syntax for this will depend on your DBMS.

Finally, there is always this "kludgy" approach:

SELECT * FROM some_table WHERE '|1001|2002|3003|' LIKE '%|' || id || '|%';

Answer 4

The first (naive) option:

SELECT * FROM some_table WHERE id IN ('1001', '2002', '3003' ... )

However, we should be able to do better. IN is very bad when you have a lot of items, and you mentioned hundreds of these ids. What creates them? Where do they come from? Can you write a query that returns this list? If so:

SELECT *
FROM some_table 
INNER JOIN ( your query here) filter ON some_table.id=filter.id

Answer 5

ORs are notoriously slow in SQL.

Your question is short on specifics, but depending on your requirements and constraints I would build a look-up table with your IDs and use the EXISTS predicate:

select t.id from some_table t 
where EXISTS (select * from lookup_table l where t.id = l.id)

Answer 6

In Oracle, I always put the id's into a TEMPORARY TABLE to perform massive SELECT's and DML operations:

CREATE GLOBAL TEMPORARY TABLE t_temp (id INT)


SELECT  *
FROM    mytable
WHERE   mytable.id IN
        (
        SELECT  id
        FROM    t_temp
        )

You can fill the temporary table in a single client-server roundtrip using Oracle collection types.

Answer 7

We have a similar issue in an application written for MS SQL Server 7. Although I dislike the solution used, we're not aware of anything better...

'Better' solutions exist in 2008 as far as I know, but we have Zero clients using that :)

We created a table valued user defined function that takes a comma delimited string of IDs, and returns a table of IDs. The SQL then reads reasonably well, and none of it is dynamic, but there is still the annoying double overhead:
1. Client concatenates the IDs into the string
2. SQL Server parses the string to create a table of IDs

There are lots of ways of turning '1,2,3,4,5' into a table of IDs, but the Stored Procedure which uses the function ends up looking like...

CREATE PROCEDURE my_road_to_hell @IDs AS VARCHAR(8000)
AS
BEGIN

   SELECT
      *
   FROM
      myTable
   INNER JOIN
      dbo.fn_split_list(@IDs) AS [IDs]
         ON [IDs].id = myTable.id

END

Answer 8

The fastest is to put the ids in another table and JOIN

SELECT some_table.* 
FROM some_table INNER JOIN some_other_table ON some_table.id = some_other_table.id

where some_other_table would have just one field (ids) and all values would be unique