MySQL “NOT IN” query

Question

I needed to execute a query to show all the rows of Table1 where a principal column value is not present in a column in the other table which is Table2.

I attempted using:

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

This is rather resulting in a syntax error. I also saw on the Internet that MySQL does not support NOT IN and something extremely complex has to be used. 

Is this true? Or am I making a terrible mistake?

Answer 1

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

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

Want to learn more concepts related to SQL? Join this SQL Course by Intellipaat.

You can check out the below MySQL Tutorial video for better understanding.