CODEWITHSUNDEEP
sqlrow-value-expression
UserControl
UserControl

Reputation: 15179

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

Upvotes: 5

Views: 5343

Answers (5)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115650

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

Upvotes: 3

user2891819
user2891819

Reputation: 11

In Oracle SQL you can just substitute parenthesis for the curly brackets "{}" in the original post (second example). May not be the ANSII standard, but it's close, and it works fine.

Concatenating the values is not recommended, even with uncommon delimiters there's always some tiny risk of it incorrectly matching freely-entered text values. Best not to get in the habit.

Upvotes: 1

Elian Ebbing
Elian Ebbing

Reputation: 19067

If you are using T-SQL, then an option that looks a bit like your hypothetical query is to use table literals, like this:

select * 
from Accounts a
inner join (values('0123456', '2000897'),('0125556', '2000866')) 
    as v(TaxID, AccountNumber) 
    on a.TaxID = v.TaxID and a.AccountNumber = v.AccountNumber

Here you create a table literal named v that contains the fields TaxID and AccountNumber. Now you can join the table literal on two fields to get the desired result. One caveat is that a table literal can only contain 1000 rows. You can read more about T-SQL support for table literals on this page.

Edit: this page indicates that this construct also works in PostgreSQL.

Upvotes: 3

AdaTheDev
AdaTheDev

Reputation: 147374

A crude way would be to concatenate the 2 values together..

e.g.

SELECT *
FROM Accounts
WHERE CAST(TaxID AS VARCHAR(10)) + '-' + CAST(AccountNumber AS VARCHAR(10)) 
IN ('0123456-2000897', '......', ....)

However, in e.g. SQL Server, this would not be able to use an index.

You could add a computed column that combines both values into 1 and then match on that:

SELECT * FROM Accounts WHERE MyComputedColumn IN ('0123456-2000897', ....)


Or, you could do:

SELECT a.*
FROM Accounts a
    JOIN 
    (
        SELECT '0123456' AS TaxID, '2000897' AS AccountNumber
        UNION ALL
        SELECT '0125556', '2000866'
    ) x ON a.TaxID = x.TaxID AND a.AccountNumber = x.Number

Upvotes: 2

Sparky
Sparky

Reputation: 15115

Be careful how to intrepret this. Shark's answer will work, but will return

TaxID        AccountNumber
1234         8765
1234         7654
2345         8765
2345         7654

Which might not be what you want... For example, if you only want account number 8765 for tax ID 1234 and 7654 for tax ID 2345, you would need a WHERE clause like this:

WHERE (taxId'1234' and accountnumber='8765') OR 
      (taxid='2345' and accountNumber='7654')

Upvotes: 2

Related Questions