Summary

The article compares the SQL methods LEFT JOIN, NOT EXISTS, and NOT IN for filtering out records without matching entries in another table, discussing their syntax, use cases, and performance implications.

Abstract

The blog post delves into the intricacies of SQL data filtering techniques, specifically focusing on scenarios where records from one table (students) need to be selected based on the absence of corresponding entries in another table (courses). It provides examples and explanations for three common approaches: LEFT JOIN combined with a WHERE clause to check for NULL values, the NOT EXISTS clause to verify non-existence of matches via a subquery, and the NOT IN clause to exclude values listed in a subquery. The article highlights the performance considerations of each method, suggesting that NOT EXISTS is generally more efficient for large datasets, while LEFT JOIN is suitable for smaller tables, and NOT IN should be used with caution due to potential issues with NULL values and performance.

Opinions

The author suggests that NOT EXISTS is often the most performant option for large datasets, as the database engine can stop processing upon finding the first match.
LEFT JOIN with IS NULL is recommended for smaller datasets or when the developer is more accustomed to using joins.
Caution is advised when using NOT IN, especially if the subquery might return NULL values or if the list of values is extensive, which can lead to slower performance.
The author emphasizes the importance of understanding the nuances of each method to write effective and efficient SQL queries.
The article encourages readers to engage with the author on LinkedIn and to consider subscribing to Medium using the author's referral link for unlimited access to content.

LEFT JOIN vs. NOT EXISTS vs. NOT IN in SQL: Filtering Data with No Matching Records

When working with SQL, you often need to filter data by excluding records that have matching entries in another table. There are several ways to achieve this, but the most common approaches are using `LEFT JOIN`, `NOT EXISTS`, and `NOT IN`. Each method has its nuances, and understanding these can help you write more efficient queries.

In this blog, we'll dive into how each of these methods works, their differences, and the performance considerations you should keep in mind. Let’s get started!

The Problem: Filtering Data with No Matching Records

Imagine you have two tables:

`students` table: Contains information about students.
`courses` table: Contains information about courses students are enrolled in.

Here are the sample datasets:

-- STUDENTS table

| student_id | name   | age |
|------------|--------|-----|
| 1          | Arjun  | 21  |
| 2          | Riya   | 22  |
| 3          | Kiran  | 23  |
| 4          | Sanjay | 21  |
| 5          | Priya  | 24  |



-- COURSES table

| course_id | student_id | course_name    |
|-----------|------------|----------------|
| 101       | 1          | Mathematics    |
| 102       | 3          | Physics        |
| 103       | 2          | Chemistry      |
| 104       | 4          | Biology        |

The task is to find students who are not enrolled in any courses. In other words, you want to find records in the `students` table that have no corresponding records in the `courses` table.

Method 1: Using `LEFT JOIN`

A `LEFT JOIN` returns all records from the left table (`students`), and the matching records from the right table (`courses`). If there is no match, the result is `NULL` on the right side.

Example: Filtering with `LEFT JOIN`

SELECT s.name
FROM students s
LEFT JOIN courses c
ON s.student_id = c.student_id
WHERE c.student_id IS NULL;

Result:

| name  |
|-------|
| Priya |

Explanation:

The query first performs a `LEFT JOIN`, returning all students along with any matching courses.
The `WHERE c.student_id IS NULL` clause filters out any rows where there is a matching course, leaving only those students who are not enrolled in any courses.

Method 2: Using `NOT EXISTS`

The `NOT EXISTS` clause checks if a subquery returns no rows. It’s useful when you want to verify that there’s no match in another table.

Example: Filtering with `NOT EXISTS`

SELECT s.name
FROM students s
WHERE NOT EXISTS (
    SELECT 1
    FROM courses c
    WHERE c.student_id = s.student_id
);

Result:

| name  |
|-------|
| Priya |

Explanation:

For each student in the `students` table, the subquery checks if there’s a corresponding entry in the `courses` table.
If the subquery finds no match, `NOT EXISTS` returns true, and the student is included in the result.

Method 3: Using `NOT IN`

The `NOT IN` clause is used to exclude values from a specified list or subquery result. It checks if a value does not match any value in a list or subquery.

Example: Filtering with `NOT IN`

SELECT name
FROM students
WHERE student_id NOT IN (
    SELECT student_id
    FROM courses
);

Result:

| name  |
|-------|
| Priya |

Explanation:

The subquery returns a list of `student_id`s from the `courses` table.
The `NOT IN` clause filters out any students whose `student_id` is in that list, leaving only those not enrolled in any courses.

Performance Considerations

While all three methods can produce the same results, their performance can vary depending on the size of your data and the structure of your tables. Here’s a quick overview of the performance considerations:

1. `LEFT JOIN` with `IS NULL`:

Performance: This method can be slower if the tables involved are large, especially if there are many matches in the join. The database has to join all rows and then filter out the ones with matches.
Best For: Situations where the `students` table is small or well-indexed.

2. NOT EXISTS:

Performance: Typically performs better with large datasets, especially when there are many matches in the subquery. The database can stop searching as soon as it finds a match, making it more efficient.
Best For: Scenarios with large datasets or when you expect the subquery to return many rows.

3. NOT IN:

Performance: Can be the slowest, especially if the subquery returns `NULL`s or if there are many values to check. SQL Server, for instance, can struggle with `NULL` values in the list.
Best For: Small datasets where `NULL` values are not present in the subquery.

Key Takeaways:

Use `NOT EXISTS` when dealing with large datasets or when the subquery might return many rows. It tends to perform better in these cases.
Use `LEFT JOIN` with `IS NULL` if your main table is relatively small, or if you’re more comfortable writing joins.
Use `NOT IN` carefully, ensuring that your subquery doesn’t return `NULL` values and is not too large.

Conclusion

Choosing the right method—whether `LEFT JOIN`, `NOT EXISTS`, or `NOT IN`—depends on the specific requirements of your query and the size of your datasets. While all three can achieve the same result, understanding their differences in performance and use cases can help you optimize your SQL queries.

`LEFT JOIN`: Best for simple joins, especially with small datasets.
`NOT EXISTS`: Often the most efficient choice for large datasets.
`NOT IN`: Use with caution, particularly when dealing with potential `NULL` values or large lists.

By understanding these differences, you can write SQL queries that are both effective and efficient, ensuring that you get the right data with minimal processing time.

Happy querying!

Connect with me on LinkedIn:

Resources used to write this blog:

Learn from YouTube Channels
LEFT JOIN — SQL Server Documentation
Understanding SQL LEFT JOIN
NOT EXISTS — SQL Server Documentation
SQL NOT EXISTS Explained
Using SQL NOT IN
I used Google to research and resolve my doubts
From my Experience
I used Grammarly to check my grammar and use the right words.

Join Medium with my referral link — Vishal Barvaliya

As a Medium member, a portion of your membership fee goes to writers you read, and you get full access to every story…

medium.com

if you enjoy reading my blogs, consider subscribing to my feeds. also, if you are not a medium member and you would like to gain unlimited access to the platform, consider using my referral link right here to sign up.