How to Efficiently Query Database Records Matching Tuples from a List?

Tuples in SQL "IN" Clause

Q: How can we efficiently query a database table for records matching tuples from a hard-coded list?

A: Standard SQL-92 syntax provides a solution by utilizing the VALUES keyword.

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

Implementation Details:

• Enclose the tuples in single quotes, not double quotes.
• Add the VALUES keyword before the list of tuples.

Advantages of this Syntax:

• Eliminates the need for expensive string concatenation and conversion.
• Supports tuples with multiple values.

Database Compatibility:

• Standard SQL-92 compliant databases support this syntax.
• As of SQL Server 2022, Microsoft has not implemented this feature.
• PostgreSQL and SQLite are examples of databases that support this syntax.

The above is the detailed content of How to Efficiently Query Database Records Matching Tuples from a List?. For more information, please follow other related articles on the PHP Chinese website!