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.

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 filters the users table to only include users who have selected 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.

• 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.