Using tuples in SQL "IN" clause

Question

I have a table containing the fields group_id and group_type and I want to query the table for all the records having any tuple (group id, group type) from a list of tuples. For example, I want to be able to do something like:

SELECT * FROM mytable WHERE (group_id, group_type) IN (("1234-567", 2), ("4321-765", 3), ("1111-222", 5)) 

A very similar question is already asked at: using tuples in sql in clause , but the solution proposed there presumes the tuple list is to be fetched from another table. This doesn't work in my case is the tuple values are hard coded.

One solution is to use string concatenation:

SELECT * FROM mytable WHERE group_id + STR(group_type, 1) IN ("1234-5672", "4321-7653", "1111-2225") 

But the problem is that the table is quite big and doing a string concatenation and conversion for each record would be very expensive.

Any suggestion?

Answer 1

Given a very minor tweak (replace double quotes with single and add the VALUES keyword), your proposed syntax is valid Standard SQL-92 syntax i.e.

SELECT *   FROM mytable  WHERE (group_id, group_type) IN (                                   VALUES ('1234-567', 2),                                           ('4321-765', 3),                                           ('1111-222', 5)                                  ); 

Sadly, MSFT have not added it to SQL Server and consider it an 'unplanned' feature.

FWIW PostgreSQL and Sqlite are examples of SQL products that support this syntax.