WHERE col1, col2 IN (...) [SQL subquery using composite primary key]

Question

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

 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 cannot change the circuit, how could you fulfill the equivalent above?

I am also going to use the terms "composite primary key", "subheading", "subselect" and "subquery" here to search for these aliases.

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

Answer 1

 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 select 1 from bar with select ... many tuples of a/b values ...

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

Answer 2

Your syntax is very close to the SQL standard!

The following are valid: FULL SQL-92 (as confirmed by the Mimer SQL-92 Validator )

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

Of course, not every SQL product supports full SQL-92 (shame!) If someone wants to see this syntax supported by Microsoft SQL Server, they can vote for it.

Another SQL-92 construct that is more widely supported (e.g. Microsoft SQL Server and Oracle) is INTERSECT for example.

 SELECT a, b FROM Foo INTERSECT SELECT a, b FROM Bar; 

Note that these constructs handle NULL correctly, unlike some other sentences here, for example. those using EXISTS (<equality predicates>) , concatenated values, etc.

Answer 3

You made a very small mistake. You must put a, b in parentheses.

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

It works!

Answer 4

Suggested IN syntax is not valid SQL. A solution using EXISTS should work in all reasonably compatible SQL RDBMSes:

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

Remember that this is often not particularly noticeable.

Answer 5

  SELECT ... FROM foo INNER JOIN (SELECT ...many tuples of a/b values...) AS results ON results.a = foo.a AND results.b = foo.b 

What are you looking for?

Answer 6

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 7

JOINS and INTERSECTS work as a replacement for IN , but they are not as obvious as a replacement for NOT IN , for example: inserting rows from TableA into TableB , where they don't already exist in TableB , where PK on both tables is composite.

I am currently using the concatenation method above in SQL Server, but this is not a very elegant solution.

Answer 8

If you need a solution that does not require tuples of values ​​that already exist in the table, you can combine the corresponding table values ​​and elements in your list, and then use the "IN" command.

In postgres, it will look like this:

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

In SQL, I would suggest that it might look something like this:

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

Answer 9

Firebird uses this concatenation formula:

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