Why Does MySQL's `NOT IN` Fail with Empty Subqueries?

MySQL "NOT IN" Query on Three Tables

In your query, you have included the condition Grade.ID_Courses NOT IN (SELECT ID_Courses FROM Evaluation WHERE NAME='JOHN' and Year=1) to filter out rows from the Grade table that do not exist in the Evaluation table with the same NAME and Year values. However, when the name "JOHN" is not present in the Evaluation table, the query produces no output.

Why NOT IN Can Be Troublesome

One limitation of using NOT IN with subqueries is its susceptibility to inconsistencies when one of the subqueries contains NULL values. This is because SQL employs three-valued logic, where NULL represents an unknown or missing value. When NOT IN is applied to a subquery that may contain NULL values, the results can become unpredictable or incorrect.

Alternatives to NOT IN

To mitigate these issues, it is recommended to avoid using NOT IN with subqueries that may contain NULL values. Instead, more reliable alternatives such as NOT EXISTS or explicit joins can be used.

For example, you could rewrite your query using the NOT EXISTS operator as follows:

SELECT Grade.ID_Courses,
       Course.ID_Courses,
       Grade.NAME,
       Course.NAME,
       Grade.ID_Courses,
       Evaluation.NAME,
       Evaluation.Year,
       Grade.Year
FROM Grade
JOIN Course ON Grade.ID_Courses = Course.ID_Courses
LEFT JOIN Evaluation ON Grade.NAME = Evaluation.NAME and Grade.Year = Evaluation.Year
WHERE Grade.NAME = 'JOHN'
  AND Grade.Year = 1
  AND Evaluation.NAME IS NULL;

Using explicit joins instead of the WHERE clause, as suggested in the provided solution, can also improve the performance and readability of your query.

The above is the detailed content of Why Does MySQL's `NOT IN` Fail with Empty Subqueries?. For more information, please follow other related articles on the PHP Chinese website!