MySQL "NOT IN" query

Question

I wanted to run a simple query to throw up all the rows of Table1 where a principal column value is not present in a column in another table (Table2).

I tried using:

SELECT * FROM Table1 WHERE Table1.principal NOT IN Table2.principal

This is instead throwing a syntax error. Google search led me to forums where people were saying that MySQL does not support NOT IN and something extremely complex needs to be used. Is this true? Or am I making a horrendous mistake?

Answer 1

To use IN, you must have a set, use this syntax instead:

SELECT * FROM Table1 WHERE Table1.principal NOT IN (SELECT principal FROM table2)

Answer 2

MySQL supports NOT IN just fine, your snippet is just not valid SQL.

It must be

SELECT * FROM Table1 WHERE Table1.principal NOT IN (SELECT Table2.principal FROM Table2 WHERE ...)

Answer 3

MySQL (at least recent versions) does support NOT IN. Your problem is that NOT IN needs to be followed by a subquery, like this:

SELECT * FROM Table1 WHERE Table1.principal NOT IN (SELECT principal FROM Table2)

Answer 4

SELECT * FROM Table1 WHERE Table1.principal NOT IN (SELECT principal FROM Table2)

works for me, MySQL 5.0

Answer 5

The subquery option has already been answered, but note that in many cases a LEFT JOIN can be a faster way to do this:

SELECT table1.*
FROM table1 LEFT JOIN table2 ON table2.principal=table1.principal
WHERE table2.principal IS NULL

Answer 6

SELECT * FROM Table1
Left Join Table2
On Table1.principal = Table2.principal

WHERE Table2.principal IS NULL

this will work

Answer 7

SELECT * FROM Table1 WHERE table1.Principle NOT IN (SELECT Principle FROM Table2)

Answer 8

SELECT * FROM Table1 WHERE table1.Principle NOT IN (SELECT Principle FROM Table2)