Multiple columns on left side of IN condition

Question

This is a valid statement:

SELECT foo FROM table1 WHERE
  column1 IN (SELECT bar FROM table2) AND
  column2 IN (SELECT bar FROM table2)

But is there any way we can avoid repeating the SELECT bar FROM table2 statement to make it simpler and more economical?

I mean something like this:

SELECT foo FROM table1 WHERE
  «column1 and also column2» IN (SELECT bar FROM table2)

I don't think there is a *simpler* way of expressing this logic. — Gordon Linoff, Feb 20 '19 at 12:47

score 4 · Accepted Answer · 2019-02-20T12:51:38.770

4

You could use a co-related sub-query with an EXISTS operator

select *
from table1 t1
where exists (select *
              from table2 t2
              where t2.bar in (t1.column1, t1.column2));

If both columns should match the value in table2.bar the following can be used:

select *
from table1 t1
where exists (select *
              from table2 t2
              where t2.bar = t1.column1
                and t2.bar = t1.column2);

edited Feb 20 '19 at 12:51

answered Feb 20 '19 at 07:57

This is not the same. This would be the same if the original query had `or`, but it has `and`. I understand the answer, but not that it was accepted. – Gordon Linoff Feb 20 '19 at 12:42
@GordonLinoff: yes you are right. Salman's answer treats that as an AND condition. – Feb 20 '19 at 12:46

Salman A · Answer 2 · 2019-02-20T09:32:42.443

3

You could use aggregation:

select *
from table1
where exists (
    select 1
    from table2
    where table2.bar in (table1.column1, table1.column2)
    having count(distinct bar) = case when table1.column1 = table1.column2 then 1 else 2 end
);

So if table1 contains the pair (foo, bar) and table2 contain values (foo), (baz) it won't match.

edited Feb 20 '19 at 09:32

answered Feb 20 '19 at 08:09

Salman A

262,204
82
430
521

Multiple columns on left side of IN condition

2 Answers2