38

I have a table called players as follows:

First_Id    Second_Id     Name
1           1             Durant
2           1             Kobe
1           2             Lebron
2           2             Dwight
1           3             Dirk

I wish to write a select statement on this table to retrieve all rows whose first ids and second ids match a bunch of specified first and second ids.

So for example, I wish to select all rows whose first and second ids are as follows: (1,1), (1,2) and (1,3). This would retreive the following 3 rows:

First_Id    Second_Id    Name
1           1            Durant
1           2            Lebron
1           3            Dirk

Is it possible to write a select query in a manner such as:

SELECT * 
FROM PLAYERS
WHERE (First_Id, Second_Id) IN ((1,1), (1,2) and (1,3))?

If there is a way to write the SQL similar to the above I would like to know. Is there a way to specify values for an IN clause that represents multiple rows as illustrated.

I'm using DB2.

Ankit Bajpai
  • 13,128
  • 4
  • 25
  • 40
user1412952
  • 381
  • 1
  • 3
  • 3

7 Answers7

41

This works on my DB2 (version 9.7 on Linux/Unix/Windows) by using this syntax:

SELECT *
FROM PLAYERS
WHERE (First_Id, Second_Id) IN (VALUES (1,1), (1,2), (1,3))

This syntax won't work on DB2 on the Mainframe (at least in version 9.1) because you can't substitute a sub-select with a VALUES expression. This syntax will work:

SELECT *
FROM PLAYERS
WHERE (First_Id, Second_Id) IN (SELECT 1, 1 FROM SYSIBM.SYSDUMMY1 UNION ALL
                                SELECT 1, 2 FROM SYSIBM.SYSDUMMY1 UNION ALL
                                SELECT 1, 3 FROM SYSIBM.SYSDUMMY1)
bhamby
  • 15,112
  • 1
  • 45
  • 66
  • This is not standard SQL but it provides a database-specific solution. – Gordon Linoff May 23 '12 at 18:46
  • 3
    @GordonLinoff I agree, but he did say he was using DB2. :) – bhamby May 23 '12 at 18:55
  • 3
    The first syntax (`WHERE (f, s) IN (VALUES (1,1), (1,2))`) also works in PostgreSQL, and in docs it said that it is kind of standard SQL: https://www.postgresql.org/docs/current/static/sql-values.html – Envek Apr 22 '17 at 11:12
5

Here's a very similar solution in postgresql:

SELECT tmp_table.val1, tmp_table.val2
FROM tmp_table
WHERE (tmp_table.val1, tmp_table.val2) not in (select tmp_table2.val1, tmp_table2.val2 from tmp_table2);
Botz3000
  • 39,020
  • 8
  • 103
  • 127
Dom T.
  • 51
  • 1
  • NOTE: This isn't exactly what was asked for, I used the previous comments to solve my specific problem of inserting two values, val1, val2 from a temp table into an actual table, while this actual table had a unique key on (val1, val2). I had to find a way to remove those key pair in my tmp_table prior to inserting. This is the select part of the clause on my insert. – Dom T. Jun 05 '12 at 16:09
2

With compound primary keys, I would concatenate the two ids and match compound strings.

select id1 + id2 as FullKey, *
from players
where FullKey in ('11','12','13')

(If ids are not strings, simply cast them as such.)

SQLCurious
  • 514
  • 3
  • 10
  • Although concatenating the key columns is a useful approach, it's safer to include a delimiter character between the columns. Although the ORDER BY clause can reference a column alias such as FullKey in this example, DB2 only allows table aliases but not column aliases in the WHERE clause. – Fred Sobotka May 23 '12 at 19:53
  • 1
    I see. Did not realise DB2 had such a constraint on column aliases. Still, using the compound id1 + id2 (or id1 + delim + id2, which, you're right, is a better idea) in the WHERE should do the trick. – SQLCurious May 23 '12 at 20:27
  • 1
    You need to add a delimiter which you have to make sure not being in the result. There will always be the possibility of duplicates like `('ab', 'cd')` and `('abc', 'd')` both concatenating to `abcd` – inetphantom Nov 14 '17 at 13:06
2

This syntax works in MySQL:

SELECT *
FROM PLAYERS
WHERE (First_Id, Second_Id) IN ((1,1), (1,2), (1,3))
MichaelG
  • 652
  • 1
  • 10
  • 17
1
SELECT * FROM <your table> where (<field1>, <field2>, ...) in (SELECT <field1>, <field2>, ... FROM <your table> where <your condition>)

This worked wonder for me.

Merkurial
  • 242
  • 3
  • 17
  • Thank you! It worked for me in TimescaleDB 2.0 and EXPLAIN ANALYZE shows some performance improvement. – franchb Dec 10 '20 at 13:32
0

This type of query works in DB2.

SELECT * FROM A
WHERE (C1, C2) IN (SELECT B1, B2 FROM B WHERE B3=1);
Ankit Bajpai
  • 13,128
  • 4
  • 25
  • 40
0

If data is needed in below order:

First_Id Second_Id Name
1 1 Durant
1 2 Lebron
1 3 Dirk
2 1 Kobe
2 2 Dwight

then simple and easy way would be:

select * from players order by First_id, second_id;

"where" clause can be used if any condition is needed on any of the column.

Noor
  • 1
  • 2