EXISTS & NOT EXISTS

Ask whether a relationship exists

Lesson 17 of 20 · 12 min · Module 6: Trustworthy database features

Some questions need to know whether any related row exists, without returning that row or counting all its matches.

Free. No installation: you write and run the code in your browser, and it is checked automatically.

By the end, you’ll be able to…

Express an existence question directly
Correlate inner and outer rows

The idea

EXISTS (subquery) is true when its subquery returns at least one row. A correlated subquery can refer to the outer row to check that row’s relationships.

SELECT c.title
FROM courses AS c
WHERE EXISTS (
  SELECT 1 FROM enrollments AS e WHERE e.course_id = c.id
);

01Identify the outer row

The outer query visits courses. For each course, the inner query checks enrollments whose course_id equals that course’s id.

02Use the relationship key

Comparing the course title to a learner’s profile language would not describe enrollment. The foreign key relationship is the relevant evidence.

03Keep multiplicity out of the answer

EXISTS answers yes or no. A course with three enrollments still appears once when tested by the outer query.

Your turn

Return the titles of courses with no enrollments. Use a correlated NOT EXISTS check against enrollments and sort titles ascending.

What the checks look for

Finds the course without any matching enrollment

Try it in the editor →

Take this with you

An existence test is useful when the presence of a relationship is the whole question.

Go a little further

Find learners with no SQL enrollment, including those enrolled in other courses.