MySQL "NOT IN" condition query detailed explanation

MySQL database supports the "NOT IN" operator, which retrieves rows from one table where the value of a specified column does not exist in another table. But it should be noted that the syntax of "NOT IN" query in MySQL is different from that of other database systems.

Syntax error analysis

The syntax you are trying to use:

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

produces a syntax error because you are trying to compare two columns directly without using a subquery.

"NOT IN" query Correct syntax

The correct syntax for "NOT IN" query in MySQL is as follows:

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

In this syntax, we use a subquery to select values ​​from the "principal" column of Table2 and compare these values ​​with the "principal" column in Table1. The result will be a list of rows in Table1 whose "principal" value is not in Table2.