How to Correctly Use `NOT IN` to Retrieve Distinct Rows in MySQL?

MySQL's NOT IN Operator: Avoiding Syntax Errors When Selecting Distinct Rows

Many users encounter syntax errors when using MySQL's NOT IN operator to retrieve rows where a column value isn't found in another table. While MySQL does support NOT IN, the syntax is crucial.

The query:

<code class="language-sql">SELECT * FROM Table1 WHERE Table1.principal NOT IN Table2.principal</code>

is incorrect and will produce a syntax error. The correct syntax requires a subquery:

<code class="language-sql">SELECT * FROM Table1 WHERE Table1.principal NOT IN (SELECT principal FROM Table2)</code>

By using parentheses to enclose (SELECT principal FROM Table2), you create a subquery. MySQL then correctly interprets this as a set of values to compare against Table1.principal. This revised query efficiently returns all rows from Table1 where the principal value is absent from the results of the subquery. This resolves the syntax error and delivers the expected results.

The above is the detailed content of How to Correctly Use `NOT IN` to Retrieve Distinct Rows in MySQL?. For more information, please follow other related articles on the PHP Chinese website!