Examples of Complex Nested Queries: Combining EXISTS, IN, HAVING

Congrats, we’ve reached the point where things get really interesting! Today we’ll see how to mix different types of subqueries to tackle complex problems. EXISTS, IN, HAVING—that’s the trio that’ll make you feel like a database wizard. We’ll pull data from one table, filter it using another, group it, and then filter those groups. And as a bonus, we’ll check out some tricks to make your queries more efficient.

Let’s start by setting up a general problem that we’ll solve step by step during this lecture.

Problem Statement

Let’s say we have a university database with three tables:

Table students

Table courses

Table enrollments

We need to select all students who:

1. Are enrolled in at least one course EXISTS.
2. Don’t have a grade for at least one of their enrolled courses IN.
3. Belong to groups where the average grade is above 80 HAVING.

Solution Using EXISTS and IN

Step 1: Check enrolled students (EXISTS). Let’s start with the simplest condition. We need to figure out which students are enrolled in at least one course. For that, we can use EXISTS.

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

1. The outer query selects names from the students table.
2. In the subquery, we check if there are any records in the enrollments table that match the student from the outer query (WHERE e.student_id = s.id).
3. SELECT 1 is used to show that we only care about the existence of records, not their content.

Result:

Now we know who’s enrolled in courses. But we want more. We want to filter them by missing grades.

Step 2: Check for missing grades (IN + NULL). Now let’s add a filter: we only want students who are missing a grade for at least one course. Here, IN and knowing how to work with NULL will help.

SELECT name
FROM students s
WHERE id IN (
  SELECT e.student_id
  FROM enrollments e
  WHERE e.grade IS NULL
);

1. The outer query selects student names.
2. The subquery builds a list of student_id from the enrollments table where grade IS NULL.

Result:

So, Otto is the only student with a course that’s missing a grade. How dramatic! But we’re not done yet: we need to only consider groups where the average grade is above 80.

Solution Using HAVING

Step 3: Grouping and filtering with HAVING.

Now it’s time to put it all together. We need to:

1. Calculate the average grade for each group.
2. Filter groups where the average grade is above 80.
3. Show students from those groups, considering the previous conditions.

SELECT name
FROM students s
WHERE s.group_id IN (
  SELECT group_id
  FROM students
  JOIN enrollments ON students.id = enrollments.student_id
  WHERE grade IS NOT NULL
  GROUP BY group_id
  HAVING AVG(grade) > 80
)
AND id IN (
  SELECT e.student_id
  FROM enrollments e
  WHERE e.grade IS NULL
);

1. The outer query selects names of students who meet all the conditions.
2. The first subquery in WHERE returns a list of group_id for groups with an average grade above 80.
   • We join students with enrollments to get the grades.
   • We filter only records where grade IS NOT NULL.
   • We group the data by group_id.
   • We use HAVING to filter the groups.
3. The second subquery in WHERE checks that the student has at least one course with a missing grade.
4. Both parts of the condition are combined with AND.

Result:

So, we found out that Otto is not only the only student with a missing grade, but also belongs to a group that’s killing it.

Comparing Approaches: EXISTS vs IN

EXISTS works best when you just want to quickly check if records exist. It’s efficient because it stops searching as soon as it finds the first record. That’s especially important for big tables.

At the same time, IN is useful when you care about the actual data. For example, if you’re outputting a list of IDs for further filtering. But keep in mind, IN can get slow if your subquery returns a ton of values.

When to use HAVING

For aggregated data, where you need to filter based on results, HAVING is your best friend. But if you can move the condition to WHERE (like filtering by a column), that’ll make your query simpler and faster.

Full Example

To lock it in, let’s break down another example: select groups where at least one student has a grade below 75, but at the same time isn’t enrolled in the “Philosophy” course.

Just to remind you, here are our tables:

Table students

Table courses

Table enrollments

SELECT DISTINCT group_id
FROM students s
WHERE group_id IN (
  SELECT s.group_id
  FROM students s
  JOIN enrollments e ON s.id = e.student_id
  WHERE e.grade < 75
)
AND group_id NOT IN (
  SELECT s.group_id                                 -- nested query level 1
  FROM students s
  JOIN enrollments e ON s.id = e.student_id
  WHERE e.course_id = (
    SELECT id FROM courses WHERE name = 'Philosophy' -- nested query level 2 :P
  )
);

1. The first subquery selects groups where there’s at least one student with a grade below 75.
2. The second subquery excludes groups linked to the “Philosophy” course.
3. We combine the conditions using IN and NOT IN to get the final result.

Result:

How Useful Is This?

In real life, these approaches save the day when you have to analyze complex data relationships. For example:

• In analytics, to highlight “special” client groups (VIP, problematic, etc.).
• In building recommendation systems, where you filter users by a bunch of criteria.
• In interviews, when you’re asked to optimize a gnarly SQL query.

Practice! That’s your path to mastery.