Multi keys in SQL WHERE IN clause

Question

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

Gelb

Based on the answers you've got. I would recommend you to keep doing your's queries as you did in the past... – Euphemiah

If you are a SQL Server user, the feature you allude to (comparisons involving rows of more than one column) is not yet in SQL Server but you can vote for it here. – Raynor

Answer 1

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

Eggplant

Tried SQL Server 2008. None of these work. It does not support natural joins and the other queries produce "An expression of non-boolean type specified in a context where a condition is expected, near ','." error. – Gelb

AFAIK MySQL supports row comparison e.g. this is valid and useful syntax ...WHERE (a.TaxID, a.AccountNumber) IN (SELECT b.TaxID, b.AccountNumber FROM b);. However, AFAIK mySQL does not support row value constructors (the VALUES (...) part) in the given context. – Raynor

The last query is SQL-92 syntax but it will not work on SQL Server because no version of SQL Server supports NATURAL JOIN. If the last query works on PostgeSQL then I would expect the first two to also work on postgreSQL. – Raynor

@onedaywhen: My mistake. I tested in SO Dataexchange and thought it worked but I was wrong. – Teethe

@Eggplant Thanks for this solution. I didn't know that you need the values keyword for SQL-92. Unfortunately mysql works only without this keyword. – Futch

@Futch I don't think that VALUES is required. Not sure why my answer appears to imply that. I will update. – Teethe

@Eggplant What I found out so far is that sqlite does not support the syntax (%columns%) IN ((%values%),(%values%)) the VALUES keyword is required in sqlite but mysql does not support (%columns%) IN (VALUES (%values%),(%values%)) - it's a mess :) – Futch

@Futch yes, the various DBMS have chosen to implement different parts of SQL. This is not the worst part, UPDATE is horribly different across implementations. – Teethe

Answer 2

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.

Lend

Answer 3

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

Elinaelinor

Answer 4

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

Lanyard

Good point, your JOIN-solution is the best so far but the query cannot be parameterised, can it? – Gelb

@Gelb - in TSQL, as of SQL 2008, there is support for table valued parameters. So you can pass a table in as a parameter and join onto that instead. Or, less desirably, the "older" ways of doing it were to pass in CSV/XML in and then extract the values out into a temp table to then join on. I've blogged about those approaches here: adathedev.co.uk/2010/02/… – Lanyard

Answer 5

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.

Plebeian