One select for multiple records by composite key

Question

Such a query as in the title would look like this I guess:

select * from table t where (t.k1='apple' and t.k2='pie') or (t.k1='strawberry' and t.k2='shortcake') 
... --10000 more key pairs here

This looks quite verbose to me. Any better alternatives? (Currently using SQLite, might use MYSQL/Oracle.)

Answer 1

You can use for example this on Oracle, i assume that if you use regular concatenate() instead of Oracle's || on other DB, it would work too (as it is simply just a string comparison with the IN list). Note that such query might have suboptimal execution plan.

SELECT *
FROM
  TABLE t
WHERE
  t.k1||','||t.k2 IN ('apple,pie',
                      'strawberry,shortcake' );

But if you have your value list stored in other table, Oracle supports also the format below.

SELECT *
FROM
  TABLE t
WHERE (t.k1,t.k2) IN ( SELECT x.k1, x.k2 FROM x );

Answer 2

Don't be afraid of verbose syntax. Concatenation tricks can easily mess up the selectivity estimates or even prevent the database from using indexes.

Here is another syntax that may or may not work in your database.

select * 
  from table t 
 where (k1, k2) in(
            ('apple',      'pie')
           ,('strawberry', 'shortcake')
           ,('banana',     'split')
           ,('raspberry',  'vodka')
           ,('melon',      'shot')
       );

A final comment is that if you find yourself wanting to submit 1000 values as filters you should most likely look for a different approach all together :)