MySQL "NOT IN" query

sql mysql
Kshitij Saxena -KJ- picture Kshitij Saxena -KJ- · Oct 5, 2009 · Viewed 513.5k times · Source

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

Julien Lebosquain picture Julien Lebosquain · Oct 5, 2009

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

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

Related questions

How do I import an SQL file using the command line in MySQL?

I have a .sql file with an export from phpMyAdmin. I want to import it into a different server using the command line. I have a Windows Server 2008 R2 installation. I placed the .sql file on the C drive, and …

Read More
How can I prevent SQL injection in PHP?

If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example: $unsafe_variable = $_POST['user_input']; mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')"); That's …

Read More
What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?

What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN in MySQL?

Read More