Checking if Data Exists with EXISTS and NOT EXISTS

Welcome to a new SQL lecture! Today, we're gonna meet some of the most low-key but insanely powerful operators — EXISTS and NOT EXISTS. Imagine a secret agent who leaves no trace but instantly tells you: "Yep, the object exists" or "Nope, nothing here." These operators don't return data directly, but let you make super precise logical checks in your queries.

Let's start with the basics. EXISTS is an operator that checks for the existence of rows in the result of a subquery. If the subquery returns at least one row, the EXISTS condition returns TRUE, otherwise — FALSE.

SELECT 1
WHERE EXISTS (
    SELECT * 
    FROM students 
    WHERE grade > 3.5
);

As you can see, we don't care about the actual data from the subquery, just whether such rows exist. If at least one row matches the condition, the query returns 1.

Syntax of EXISTS

The syntax for EXISTS is pretty simple:

SELECT columns
FROM table
WHERE EXISTS (
    SELECT 1
    FROM another_table
    WHERE condition
);

Explanation:

• The nested subquery inside EXISTS can be any query.
• The result of the subquery is what determines if TRUE or FALSE is returned.

Example: Are there students with a grade above 4?

Let's imagine a students table:

Suppose we want to check if there are any students with a grade above 4. Let's use this query:

SELECT 'There are students with a high grade!'
WHERE EXISTS (
    SELECT 1
    FROM students
    WHERE grade > 4
);

Result:

There are students with a high grade!

Why does EXISTS work faster than IN?

The main advantage of EXISTS is that it stops running the subquery as soon as it finds the first match. That means if you have a condition checking for the existence of data, EXISTS can be super efficient.

For example, imagine the students table has millions of rows, but we're just looking for one existing match (grade > 4). As soon as SQL finds the first row that fits, the query is done.

Using NOT EXISTS

Now let's talk about NOT EXISTS. This operator works like the opposite of EXISTS. It returns TRUE if the subquery returns zero rows.

Example: find students with no grades (NULL)

Suppose our table has students who don't have grades yet:

We want to select all students with missing grades. Let's use NOT EXISTS:

SELECT *
FROM students s
WHERE NOT EXISTS (
    SELECT 1
    FROM students 
    WHERE grade IS NOT NULL
    AND id = s.id
);

Result:

Comparing EXISTS and IN

Sometimes it seems like EXISTS and IN do the same thing. At first glance — yeah, but there are some gotchas. Especially if there's a NULL sneaking around. Then IN can act weird, and EXISTS comes to the rescue.

Let's check out an example.

The courses table (courses you can take):

And here are the students:

The enrollments table (who signed up for which course):

We want to select the names of courses that someone is enrolled in. Seems simple.

Using IN:

SELECT name
FROM courses
WHERE course_id IN (
    SELECT course_id 
    FROM enrollments
);

If there's a NULL in enrollments.course_id, this query still works correctly and returns the courses that have any non-NULL match. PostgreSQL’s rule for IN is:

• a matching value is found → TRUE
• no match, but the subquery contains NULL → NULL (not FALSE, but WHERE filters it out)
• no match and no NULL → FALSE

The "NULL ruins everything and you get an empty result" trap is about NOT IN, not IN. Example: SELECT name FROM courses WHERE course_id NOT IN (SELECT course_id FROM enrollments) — if enrollments has even one NULL course_id, the result is empty for any data.

Using EXISTS:

SELECT name
FROM courses c
WHERE EXISTS (
SELECT 1
FROM enrollments e
WHERE c.course_id = e.course_id
);

But EXISTS just checks: "Is there at least one row where course_id matches?" — and that's it. It doesn't care if there's a NULL somewhere, since it's looking for actual matches, not a list of values.

Bottom line: if your subquery might have NULLs, it's better to use EXISTS so you don't get any surprises.

Real-world Examples

The students table:

The enrollments table:

Example 1. Students enrolled in courses

Let's find those who have already shown up somewhere — enrolled in a course (even if it's weird, like Maria Chi):

SELECT name
FROM students s
WHERE EXISTS (
    SELECT 1
    FROM enrollments e
    WHERE s.id = e.student_id
);

Result:

Alex Lin
Anna Song
Maria Chi

If a student shows up in enrollments at all — they're in the result, even if their course_id is unclear.

Example 2. Students without courses

Now let's find those who just exist in the system — but aren't enrolled anywhere yet:

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

Result:

Dan Seth
Shadow Moon

Looks like these two haven't found a course they like yet. Or maybe they just forgot to sign up :)

Example 3. Selecting courses with more than 5 registered students

The courses table:

The enrollments table:

We want to find courses that have more than five students enrolled. Here, EXISTS is kinda asking: "Is there at least one group of rows for this course where there are more than five students?"

SELECT name
FROM courses c
WHERE EXISTS (
    SELECT 1
    FROM enrollments e
    WHERE c.course_id = e.course_id
    GROUP BY e.course_id
    HAVING COUNT(*) > 5
);

Result:

Mathematics

Only the "Mathematics" course (course_id = 1) has six students enrolled. The other courses aren't that popular yet.

Common Mistakes When Using EXISTS and NOT EXISTS

1. Misunderstanding subquery syntax. Always check that your subquery correctly references the outer table.
2. Forgetting to handle NULL. Even if you use EXISTS, sometimes you need to explicitly handle NULL.
3. No index on subquery fields. This can really slow down your query.

That's it for today! Now you know how to use EXISTS and NOT EXISTS to check for data existence, and the differences between these operators and IN. In the next lecture, we'll keep diving into subqueries, looking at how to use them in SELECT for working with aggregated data.