Using Tuples in SQL "In" Clause

Using tuples in SQL IN clause

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.

Using tuples in SQL in clause

your code works if you do it in PostgreSQL or Oracle. on MS SQL, it is not supported

use this:

SELECT a,b FROM aTable
WHERE 
-- (aTable.a,aTable.b) IN -- leave this commented, it makes the intent more clear
EXISTS
(
    SELECT anotherTable.a,anotherTable.b -- do not remove this too, perfectly fine for self-documenting code, i.e.. tuple presence testing
    FROM anotherTable
    WHERE anotherTable.a = aTable.a AND anotherTable.b = aTable.b
);

[EDIT]

sans the stating of intent:

SELECT a,b FROM aTable
WHERE     
EXISTS
(
    SELECT *
    FROM anotherTable
    WHERE anotherTable.a = aTable.a AND anotherTable.b = aTable.b
);

it's somewhat lame, for more than a decade, MS SQL still don't have first-class support for tuples. IN tuple construct is way more readable than its analogous EXISTS construct. btw, JOIN also works (tster's code), but if you need something more flexible and future-proof, use EXISTS.

[EDIT]

speaking of SQLite, i'm dabbling with it recently. yeah, IN tuples doesn't work

Using tuples in ORACLE IN clause and a condition for one element in the tuple

Are you looking for something like this?

select *
from MY_TABLE
where (id, name) in ((1,'new'), (2, 'old')) and
      date between effectiveDate and termDate

This looks for the pairs in a list and then checks for the dates between a range of dates.

EDIT:

I think you want to break this into multiple clauses, one for each set of values:

where (id = 1 and name = 'new' and date between eff1 and term1) or
      (id = 2 and name = 'old' and date between eff2 and term2) or
      . . .

How to use the IN clause forcing more columns into a tuple and then looking up that tuple among the result of a query returning only one column

distinct is not a function and always applies to all columns of the SELECT list. Enclosing one or more columns in parentheses won't change the result of the DISTINCT.

Your problem stems from the fact that (a,b,c,d) create a single column with an anonymous record as its type in Postgres. It does not return four columns, but just a single one. And thus the comparison with four columns from the IN condition fails.

Additionally: in a sub-query for an IN condition the distinct is actually useless because the IN operator only checks the first match. It might even make things slower.

select a, b, c, d, e 
from table_j 
where (a, b, c, d) in (select distinct a, b, c, d 
                       from table_r 
                       where date > '2022-05-01' 
                         and code='123456' )
order by a, b, c, d;

Tuple not in clause to run in mysql and mssql issue

NOT EXISTS can be used instead:

SELECT
    a.col1,
    b.col2
FROM
    tableA a
    JOIN tableB b ON b.col_x = a.col_x
WHERE a.col5 IN ('VAL1', 'VAL2', 'VAL3')
  AND NOT EXISTS    
  (
    SELECT *
    from tableC c
    WHERE c.col2 = a.col2 AND c.col4 = b.col4
  )

SQL where tuple in list

SQL Server doesn't support tuples with in. The closest reasonable syntax is to switch to a correlated subquery:

select c.* 
from courses c
where c.duration = (select max(c2.duration)
                    from courses c2
                    where c2.cyear = c.cyear
                   );

Python psycog2 | Use a tuple or array in where clause

Use SQL string composition to pass identifiers or literals to a query text.

import psycopg2
import psycopg2.sql as sql
# ...

vilist = (1,2,3,4)
query = sql.SQL("select * from temp.table1 where ids in {}").format(sql.Literal(vilist))
cur.execute(query)

Can't get python tuple as input in WHERE IN SQL clause?

string_literal(...) appears to be preparing the object for insertion into a char/text field, not stringifying it in a way that could be appended to a query.

You can pass the tuple contents in as additional parameters for your query by dynamically building parameter placeholders. This has the additional advantage of using parameterization to avoid SQL injection and related problems.

screen_name = "example"
seat_numbers = [1, 2, 3, 4]

args = tuple([screen_name] + seat_numbers)

query = """
UPDATE 
    seats 
SET 
    isReserved = 1 
WHERE 
    screen_name=%s AND 
    seat_number IN ({placeholders})
""".format(
    placeholders=",".join(["%s"] * len(seat_numbers)),
)
print(query)
print(args)

cursor.execute(query, args)

---

Related Topics