Skip to main content
Practice

EXISTS and NOT EXISTS

The EXISTS and NOT EXISTS operators are used to check whether a subquery returns any rows.

They don't care what the subquery returns, only whether it returns anything at all.


Syntax​

The basic syntax for EXISTS and NOT EXISTS is as follows:

Basic EXISTS syntax
SELECT ...
FROM table
WHERE EXISTS (
  SELECT 1
  FROM another_table
  WHERE condition
);
  • EXISTS: returns TRUE if the subquery returns at least one row
  • NOT EXISTS: returns TRUE if the subquery returns zero rows

Example 1: Users Who Selected Any Courses​

Find all users who have at least one course in user_courses.

Using EXISTS
SELECT name
FROM users u
WHERE EXISTS (
  SELECT 1
  FROM user_courses uc
  WHERE uc.user_id = u.user_id
);

The subquery in the WHERE clause filters the users table to only include users who have selected at least one course.

Output:​

name
Sofia
Ethan
Aisha
Olivia
Liam

All users have at least one course, so all of them are returned.


Example 2: Users who didn't take any courses​

To find users who didn't take any courses, use NOT EXISTS.

For example, since Olivia has no entries in user_courses, the query would return her:

Using NOT EXISTS
SELECT name
FROM users u
WHERE NOT EXISTS (
  SELECT 1
  FROM user_courses uc
  WHERE uc.user_id = u.user_id
);

Output:​

name
Olivia

NOT EXISTS returns only users who have no matching rows in user_courses.


Why Use EXISTS?​

  • More efficient than IN on large or correlated subqueries
  • Great for presence/absence checks
  • Very readable for permission filters, joins, or business logic

Tip: Use EXISTS when you care about the presence of a row, not the value itself.

Want to learn more?

Join CodeFriends Plus membership or enroll in a course to start your journey.