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)

Answer 1

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);