NOT IN and NOT EXISTS

Scenario:

Imagine you have two tables: students and enrollments. The students table contains all students, and the enrollments table contains the students who are enrolled in courses.

We want to find out which students are not enrolled in any course.

Table: students

student_id	student_name
1	Alice
2	Bob
3	Carol
4	David

Table: enrollments

student_id	course_id
1	101
2	102

---

1. Using NOT IN

The NOT IN operator checks if a value is not present in a list returned by a subquery.

Query with NOT IN:

SELECT student_id, student_name
FROM students
WHERE student_id NOT IN (
    SELECT student_id
    FROM enrollments
);

Explanation:

• The subquery:sqlCopy codeSELECT student_id FROM enrollments; returns a list of student_id values from the enrollments table.
• The main query:sqlCopy codeSELECT student_id, student_name FROM students WHERE student_id NOT IN (...); returns all students whose student_id is not in the list of student_id values from the enrollments table.

Result:

student_id	student_name
3	Carol
4	David

Problem with NOT IN:

NOT IN has a potential issue when the subquery returns NULL values. If any row in the subquery result contains NULL, the entire query may return no results or behave unexpectedly because NULL cannot be compared directly.

Example with NULL:

Let’s say the enrollments table contains a NULL value in the student_id column (perhaps due to a data issue):

student_id	course_id
1	101
2	102
NULL	NULL

Running the same NOT IN query would return no results, because NOT IN with a NULL in the list doesn’t behave as expected.

---

2. Using NOT EXISTS

The NOT EXISTS operator checks if a subquery returns no rows. It’s generally safer than NOT IN because it doesn’t suffer from the NULL comparison issue.

Query with NOT EXISTS:

SELECT student_id, student_name
FROM students s
WHERE NOT EXISTS (
    SELECT 1
    FROM enrollments e
    WHERE e.student_id = s.student_id
);

Explanation:

• The subquery:SELECT 1 FROM enrollments e WHERE e.student_id = s.student_id; checks if there is any record in the enrollments table where the student_id matches the student_id from the students table.
• The main query:sqlCopy codeSELECT student_id, student_name FROM students s WHERE NOT EXISTS (...); returns all students for whom the subquery does not return any rows (i.e., the student is not enrolled in any course).

Result:

student_id	student_name
3	Carol
4	David

Why NOT EXISTS is Safer:

Even if the subquery returns a NULL value, NOT EXISTS will still work as expected because it checks for the absence of rows rather than directly comparing values. So, you won’t run into the issues that NOT IN might cause when dealing with NULL.

---

Key Differences Between NOT IN and NOT EXISTS

Feature	NOT IN	NOT EXISTS
Purpose	Checks if a value is not in a list returned by a subquery.	Checks if the subquery returns no rows.
NULL Handling	Can produce incorrect results if the subquery contains NULL.	Safely handles NULL values.
Performance	May be less efficient with large datasets or when NULL is involved.	Generally more efficient with large datasets and NULL handling.
When to Use	Suitable for small, simple subqueries where NULL isn’t an issue.	Preferred for more complex queries, or when NULL values are involved.

---

Conclusion:

• NOT IN is simple but can be problematic when dealing with NULL values in the subquery.
• NOT EXISTS is safer, especially when NULL values may be present, and is often more efficient for larger datasets. It checks for the absence of rows rather than directly comparing values.

In general, NOT EXISTS is recommended for most cases, particularly when you need to handle complex conditions or NULL values.