How Can I Efficiently Query for Tuple Pairs in SQL's IN Clause?

Using Tuples in SQL IN Clause: Alternative to String Concatenation

In SQL, the IN clause allows you to compare a field with a set of values. However, handling tuples containing multiple values can pose challenges.

The Problem

Consider a table with fields group_id and group_type, and you want to query for records with specific tuple pairs, such as:

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

The proposed solution to use string concatenation, as below, is inefficient for large tables:

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

Standard SQL Solution

A more efficient solution is to use the Standard SQL-92 syntax for tuples in the IN clause with the VALUES keyword:

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

This syntax is supported by several SQL products, including PostgreSQL and SQLite.

Microsoft SQL Server Note

At the time of writing (SQL Server 2022), this syntax is not supported in Microsoft SQL Server.

The above is the detailed content of How Can I Efficiently Query for Tuple Pairs in SQL's IN Clause?. For more information, please follow other related articles on the PHP Chinese website!