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?

Aundreaaunson

Answer 1

EDIT: this is a dated answer, although it was the accepted answer in 2011, other answers with more upvotes reflect more recent approaches.

Why not construct the OR statements?

SELECT *
FROM mytable 
WHERE (group_id = '1234-567' and group_type = 2)
    OR (group_id = '4321-765' and group_type = 3)
    OR (group_id = '1111-222' and group_type = 5)

Granted, it doesn't look as nice and neat as your concept example but it will do the job (and if you IN with tuples did exist, it would implement it exactly the same way under the covers most likely.

Forbade

Good point! The only problem is in case you have a long list of tuples. – Aundreaaunson

Answer 2

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

As of SQL Server 2022 Microsoft have not added it to SQL Server and consider it an 'unplanned' feature*.

For what it's worth, PostgreSQL and SQLite are examples of SQL products that support this syntax.

^{*The request was migrated to the new Azure Feedback site, but is not in a readable condition.}

Ewell

Feedback for this feature seems to have been moved to UserVoice: feedback.azure.com/forums/908035-sql-server/suggestions/… – Marchpast

So which databases support this? Oracle? Postgres? MariaDB? – Wiedmann

@static_rtti: I know that PostgreSQL and Sqlite support the syntax and that Oracle and mySQL do not. P.S. picky point: these are examples of SQL products (or 'SQL DBMSs' or similar). Databases are variables that users create using SQL products. For example there's a database called Airplanes (logical) that is persisted on disk on my laptop and I can manipulate this variable which itself contains a table (logical) named mytable. The database Airplanes does not support the aforementioned syntax because it is limited by the SQL product (MSSQL) that I use to create and modify it. – Ewell

DB2 also supports this. – Haughty

Answer 3

In SQL Server 2008 you can do like this:

select *
from mytable as T
where exists (select *
              from (values ('1234-567', 2), 
                           ('4321-765', 3), 
                           ('1111-222', 5)) as V(group_id, group_type)
              where T.group_id = V.group_id and
                    T.group_type = V.group_type               
             )

Kortneykoruna

What in this is specific to SQL Server 2008? Is it the "from values" part? – Aundreaaunson

@Promather, Yes it is the values. The alternative is to use select ... union all like in the answer by B Tyler. – Kortneykoruna

Great! is this possible in SQL Server 2008? This shows what is row-value-expression meaning clearly. – Kyrakyriako

@MikaelEriksson this would also work: where not exists (select T.group_id, T.group_type EXCEPT select * from (values ('1234-567', 2), ...) as V(group_id,group_type) ) ; – Rowdyism

Or this: where exists (select T.group_id, T.group_type INTERSECT select * from (values ('1234-567', 2), ...) as V(group_id,group_type) ). – Gehenna

I've been looking for the functionality values provides for months now, and our DBA has repeatedly claimed it doesnt exist. Bravo sir. – Doody

Answer 4

You can use a common table expression to pretend that these tuples are in another table:

;WITH Tuples as (
     select '1234-567' as group_id, 2 as group_type union all
     select '4321-765', 3 union all
     select '1111-222', 5
)
SELECT * /* TODO - Pick appropriate columns */
from mytable m where exists (
   select * from Tuples t
   where m.group_id = t.group_id and m.group_type = t.group_type)

Xerarch

Thanks, but why is there a semicolon before the WITH clause? – Aundreaaunson

@Promather - it's there because the WITH keyword can have other meanings, and to introduce a CTE, it must be the start of a statement. The semicolon ensures that the previous statement is definitely terminated. – Xerarch

Answer 5

Using that solution, this should work:

SELECT *
FROM mytable m
WHERE EXISTS (
   SELECT * FROM (
   SELECT "1234-567" group_id, 2 group_type UNION ALL
   SELECT "4321-765", 3 UNION ALL
   SELECT "1111-222", 5) [t]
   WHERE m.group_id = t.group_id AND m.group_type = t.group_type) 

BTW, you should probably use a CTE to create that inner table.

Sisyphus

Answer 6

I haven't seen this yet, but something like this should work

SELECT * FROM  AgeGroup ag JOIN
(VALUES
('18-24', 18, 24),
('25-34 ', 25, 39),
('35-44 ', 35, 49),
('45-54 ', 45, 59),
('55-64 ', 55, 69),
('65+   ', 65, 299)
) AS x (agegroup, minage, maxage)
ON ag.age_group = x.agegroup 
    AND ag.min_age=x.minage 
    AND ag.max_age=x.maxage

Hardiness

Answer 7

Here is another tuple solution using a join:

SELECT 
  *
FROM mytable m
JOIN
(
   SELECT "1234-567" group_id, 2 group_type 
   UNION ALL SELECT "4321-765", 3 
   UNION ALL SELECT "1111-222", 5
) [t]
ON m.group_id = t.group_id 
AND m.group_type = t.group_type

Anthropography

Answer 8

I had a similar problem but my tuple collection was dynamic - it was sent over to the SQL Server in a query parameter. I came up with the following solution:

1. Pass a tuple as an XML:

   DECLARE @tuplesXml xml = '<tuples><tuple group-id="1234-567" group-type="2"/><tuple group-id="4321-765" group-type="3"/></tuples>';
   

2. Inner join the table that you want to filter with the XML nodes:

   SELECT t.* FROM mytable t
   INNER JOIN @tuplesXml.nodes('/tuples/tuple') AS tuple(col)
   ON tuple.col.value('./@group-id', 'varchar(255)') = t.group_id
   AND tuple.col.value('./@group-type', 'integer') = t.group_type
   

It seems to work fine in my situation which is a bit more complex than the one described in the question.

Keep in mind that it is necessary to use t.* instead of * and the table returned from nodes method needs to be aliased (it's tuple(col) in this case).

Gyrose

Answer 9

select * from table_name where 1=1 and (column_a, column_b) in ((28,1),(25,1))

Bautram

This solution returns the error: An expression of non-boolean type specified in a context where a condition is expected, near ',' – Madaih