WHERE col1,col2 IN (…) [SQL subquery using composi

Question

Given a table foo with a composite primary key (a,b), is there a legal syntax for writing a query such as:

SELECT ... FROM foo WHERE a,b IN (SELECT ...many tuples of a/b values...);
UPDATE foo SET ... WHERE a,b IN (SELECT ...many tuples of a/b values...);

If this is not possible, and you could not modify the schema, how could you perform the equivalent of the above?

I'm also going to put the terms "compound primary key", "subselect", "sub-select", and "sub-query" here for search hits on these aliases.

Edit: I'm interested in answers for standard SQL as well as those that would work with PostgreSQL and SQLite 3.

Answer 1

Firebird uses this concatenation formula:

SELECT a,b FROM foo WHERE a||b IN (SELECT a||b FROM bar WHERE condition);

Answer 2

With concatenation, this works with PostgreSQL:

SELECT a,b FROM foo WHERE a||b IN (SELECT a||b FROM bar WHERE condition);

UPDATE foo SET x=y WHERE a||b IN (SELECT a||b FROM bar WHERE condition);

Answer 3

The IN syntax you suggested is not valid SQL. A solution using EXISTS should work across all reasonably compliant SQL RDBMSes:

 UPDATE foo SET x = y WHERE EXISTS
    (SELECT * FROM bar WHERE bar.c1 = foo.c1 AND bar.c2 = foo.c2)

Be aware that this is often not especially performant.

Answer 4

sqlite> create table foo (a,b,c);
sqlite> create table bar (x,y);
sqlite> select * from foo where exists (select 1 from bar where foo.a = bar.x and foo.b = bar.y);

Replace the select 1 from bar with your select ... many tuples of a/b values ....

Or create a temporary table of your select ... many tuples of a/b values ... and use it in place of bar..

Answer 5

You've done one very little mistake. You have to put a,b in parentheses.

SELECT ... FROM foo WHERE (a,b) IN (SELECT f,d FROM ...);

That works!

Answer 6

If you need a solution that doesn't require the tuples of values already existing in a table, you can concatenate the relevant table values and items in your list and then use the 'IN' command.

In postgres this would look like this:

SELECT * FROM foo WHERE a || '_' || b in ('Hi_there', 'Me_here', 'Test_test');

While in SQL I'd imagine it might look something like this:

SELECT * FROM foo WHERE CONCAT(a, "_", b) in ('Hi_there', 'Me_here', 'Test_test');