SQL In Clause Multiple Columns

Question

SELECT *
FROM Tabl tabb
WHERE (tabb.col1, tabb.col2) IN ( (1,2), (3,4))

The above works in Oracle but I am trying to run in a proprietary SQL engine which doesn't support the above query formation with multiple columns in IN.

I am trying to find combinations of 1,2 and 3,4 in the DB.

Please help me with any alternatives for achieving the above.

I am looking for ways to pass a list of values for col1 and col2 from Java at one shot, so '=' might not be an option as it might require two SQL statements for achieving the above.

it would be easier with sample data, you want the result where col1 = 1 and col2 = 2 or col1 = 3 and col2 = 4 ? — Justin
– Justin, Commented Nov 27, 2012 at 21:12
Yes I want the result where col1 = 1 and col2 = 2 or col1 = 3 and col2 = 4. — user1857647
– user1857647, Commented Nov 27, 2012 at 21:19

hall.stephenk · Accepted Answer · 2012-11-27 19:39:19Z

5

How about this?

SELECT
    *
FROM
    Tabl tabb
WHERE
    (tabb.col1 = 1 AND tabb.col2 = 2) OR
    (tabb.col1 = 3 AND tabb.col2 = 4)

answered Nov 27, 2012 at 19:39

hall.stephenk

1,1657 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1857647 Over a year ago

Apologies for not being clearer earlier, please see the edited question above.

JohnLBevan · Accepted Answer · 2012-11-27 19:41:07Z

0

You could do something like this:

SELECT *
FROM Tabl tabb
inner join (
    select 1 a, 2 b 
    union select 3, 4
) x
on tabb.col1 = x.a
and tabb.col2 = x.b

answered Nov 27, 2012 at 19:41

JohnLBevan

24.8k16 gold badges107 silver badges201 bronze badges

1 Comment

user1857647 Over a year ago

Like I mentioned in the question, the values 1,2,3,4 are static values and not a result of select.

elxala · Accepted Answer · 2016-06-03 21:57:29Z

0

In case of numeric columns and supposing the values are bounded to a specific range more values can be packed into one number using some formula

for instance supposing the values of col1 and col2 are from 1 to 1000 the formula

  N = 10000 * col1 + col2

can be used and in the example the select would be

  SELECT * FROM Tabl tabb WHERE (10000 * tabb.col1 + tabb.col2) IN ( 10002, 30004)

In general string concatenation can be used to combine several column values, for example

  SELECT * FROM Tabl tabb WHERE (tabb.col1||"&"||tabb.col2) IN ( "1&2", "3&4")

answered Jun 3, 2016 at 21:57

elxala

3013 silver badges6 bronze badges

Collectives™ on Stack Overflow

SQL In Clause Multiple Columns

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related