IN clause with multiple columns

Question

Is there a way to achieve the following union query using only one query using IN clause with multiple columns?

Instead of using

select * 
from table_a 
where field_a in (select field_1 from table_b) 
union
select * 
from table_a 
where field_a in (select field_2 from table_b);

I want to create something that will look like the following:

select * 
from table_a 
where field_a in (select field_1,field_2 from table_b) ;

score 5 · Accepted Answer · answered Jul 27 '14 at 20:56

The most you can get is to put the union inside the sub query:

select * 
from table_a 
where field_a in (select field_1 from table_b union select field_2 from table_b)

Or:

select * 
from table_a 
where field_a in (select field_1 from table_b)
or field_a in ( select field_2 from table_b)

score 2 · Answer 2 · edited May 23 '17 at 12:11

The equivalent is this:

select *
  from table_a
 where field_a in (select field_1 from table_b)
    or field_a in (select field_2 from table_b)

NOT THIS:

select *
  from table_a
 where field_a in (select field_1, field_2 from table_b)

Because in the latter case, field1 and field2 would have to be appear on the same row of table_b.

In the UNION query that you want to mimic, that is not the case. You need 2 separate INs to mimic what that UNION query is doing.

I answered a similar question on the difference between the above not long ago here: Difference in two SQL query, but same result

daghan · Answer 3 · 2014-07-27T21:04:15.057

2

select 
    * 
from 
    table_a a
where 
    exists(
        select 
            1 
        from 
            table_b b 
        where 
            a.field_a = b.field_1 OR 
            a.field_a = b.field_2
    )

edited Jul 27 '14 at 21:04

answered Jul 27 '14 at 20:58

daghan

948
10
18

canon · Answer 4 · 2014-07-30T20:55:11.773

2

Why not use a join? Then you can list all the columns you'd like for in()...

select distinct
 a.* 
from table_a as a
    join table_b as b
    on a.field_a in (b.field_1, b.field_2)

Alternatively, you could leverage the exists() function:

select distinct
 a.* 
from table_a as a
where exists (
    select
    *
    from table_b as b
    where a.field_a in (b.field_1, b.field_2)
)

edited Jul 30 '14 at 20:55

answered Jul 27 '14 at 21:06

canon

40,609
10
73
97

this would result in rows being repeated however, if in table_b the field_a value is listed more than once due to additional columns not discussed in the question – Brian DeMilia Jul 27 '14 at 21:09
@BrianDeMilia Well, I suppose we can apply a distinct (it should have been there anyway considering his original union implementation) and then we can take it to the performance test. I don't deal with mysql in particular very often... so, I'd be curious to see how yours performs versus mine. – canon Jul 27 '14 at 21:21

IN clause with multiple columns

4 Answers4