EXISTS and NOT EXISTS

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

They don’t evaluate the actual values from the subquery — only whether rows are returned.

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

Copy
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

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

Copy
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 ensures the users table only returns those with at least one course.

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

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:

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

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

• Often more efficient than IN, especially with large or correlated subqueries
• Great for presence/absence checks
• Improves readability for permission checks, conditional joins, or business rules

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