MySQL WHERE <multiple-column> IN <subquery>

mysql where-clause multiple-tables in-subquery

Max13 · Jul 9, 2012 · Viewed 37.6k times · Source

Is there a way (without JOIN) to use the WHERE clause on 2 columns (OR) IN a subquery? Currently, I'm doing

WHERE 'col1' IN
(
    SELECT id FROM table
) OR 'col2' IN
(
    SELECT id FROM table
)

And I'm sure I can do better :) . i've also tried WHERE ('col1', 'col2') IN <subquery> but MySQL says: Operand should contain 2 column(s)

Thanks for your help.

Edit: By "No join", I mean I'm alreeady making many joins: http://pastebin.com/bRfD21W9, and as you can see, the subqueries are on another table.

Answer

SELECT *
FROM table

WHERE 
(col_1, col_2) NOT IN
 (SELECT col_1, col_2 FROM table)