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)?
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