Why Use NOT EXISTS or Explicit Joins Instead of NOT IN in SQL Queries?

The Perils of "NOT IN" in SQL Queries

When attempting a query that checks if data from one table does not exist in another, the "NOT IN" operator can lead to unexpected results, especially when handling null values.

The Problem with "NOT IN"

In the provided query, the "NOT IN" clause compares the results of a subquery to the column "ID_Courses" in the "Grade" table:

Grade.ID_Courses NOT IN (SELECT ID_Courses FROM Evaluation WHERE NAME='JOHN' and Year=1)

If the subquery returns any null values, the "NOT IN" clause will evaluate to false, even if the specified "JOHN" record with "Year" 1 exists in the "Grade" table. This is because three-valued logic in SQL interprets null as "unknown," leading to an incorrect evaluation.

Alternatives to "NOT IN"

To avoid this problem, it is recommended to use alternative methods such as NOT EXISTS or explicit joins:

NOT EXISTS

SELECT Grade.ID_Courses, Course.ID_Courses, Grade.NAME, 
    Course.NAME, Grade.ID_Courses,
    Evaluation.NAME,
    Evaluation.Year,    
    Grade.Year
    from Grade
    LEFT 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 
    Evaluation.NAME IS NULL
    GROUP BY Grade.ID_Courses

Explicit Joins

SELECT Grade.ID_Courses, Course.ID_Courses, Grade.NAME, 
    Course.NAME, Grade.ID_Courses,
    Evaluation.NAME,
    Evaluation.Year,    
    Grade.Year
    from Grade
    LEFT 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 
    NOT (Evaluation.NAME IS NOT NULL)
    GROUP BY Grade.ID_Courses

By using these alternatives, you can ensure that the query accurately identifies records where the specified data does not exist in the other table, regardless of the presence of null values.

The above is the detailed content of Why Use NOT EXISTS or Explicit Joins Instead of NOT IN in SQL Queries?. For more information, please follow other related articles on the PHP Chinese website!