[SOLVED] Multi keys in SQL WHERE IN clause

Multi keys in SQL WHERE IN clause

Say you have Accounts table where ID column is PK and TaxID+AccountNumber is unique constraint:

select * from Accounts where ID in (100, 101)

now you want to make similar query using the natural key:

select * from Accounts 
where {TaxID, AccountNumber} in 
  ({"0123456", "2000897"}, {"0125556", "2000866"})

So this involves tuples and looks pretty legitimate. Is it possible to express somehow with ANSI SQL? Maybe in some specific SQL extension? If not, why (will appreciate any speculations)?

Solution

Both of these are valid ISO/ANSI Full SQL-92 syntax:

SELECT a.* 
FROM Accounts a
  INNER JOIN
    ( VALUES('0123456', '2000897'), ('0125556', '2000866')
    ) AS v(TaxID, AccountNumber) 
  ON (a.TaxID, a.AccountNumber) = (v.TaxID, v.AccountNumber)

SELECT * 
FROM Accounts a
WHERE (a.TaxID, a.AccountNumber) IN 
    ( VALUES ('0123456', '2000897'), ('0125556', '2000866') )

But I don't think either of them works in any current DBMS.

This is also valid Full SQL-92 syntax (it doesn't work in SQL-Server 2008 because of the NATURAL JOIN):

SELECT a.* 
FROM Accounts a
  NATURAL JOIN
    ( VALUES('0123456', '2000897'), ('0125556', '2000866')
    ) AS v(TaxID, AccountNumber)

This is also valid SQL (not sure if it is in the 92 specification or later) - and is what you have (but using parenthesis, not curly brackets).
It is supported by MySQL, Postgres, DB2 (but not SQL Server):

SELECT a.* 
FROM Accounts a
WHERE (TaxID, AccountNumber) IN
    ( ('0123456', '2000897'), ('0125556', '2000866') )
  ;

There has been a similar question in DBA.SE, with various other ways to formulate this:
selecting where two columns are in a set