Chapter 40: PostgreSQL EXISTS Operator
The EXISTS operator.
Many students (and even some experienced developers) avoid EXISTS at first because it looks “strange” or “complicated” compared to IN or JOIN. But once you understand it properly, you’ll realize:
EXISTS is often the cleanest, fastest, and most readable way to answer questions like:
- “Show me all students who have at least one enrollment”
- “Show me all courses that were taken by someone”
- “Show me all customers who placed at least one order in 2025”
1. What does EXISTS actually do? (Very clear teacher explanation)
EXISTS checks whether a subquery returns at least one row.
- If the subquery returns one or more rows → EXISTS returns TRUE
- If the subquery returns zero rows → EXISTS returns FALSE
Very important points:
- It stops as soon as it finds the first matching row — no need to count all matches
- It does not care what columns the subquery returns (most people write SELECT 1 or SELECT *)
- It is correlated in 99% of real uses → the subquery refers to a column from the outer query
- PostgreSQL is extremely efficient with EXISTS (often better than IN or certain JOIN patterns)
2. Basic syntax skeleton (the pattern you will write 95% of the time)
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT … FROM outer_table WHERE EXISTS ( SELECT 1 -- or SELECT * — doesn't matter FROM inner_table WHERE inner_table.foreign_key = outer_table.primary_key AND [optional extra conditions] ); |
The subquery is correlated — it depends on the current row of the outer query.
3. Real, detailed examples (using our college schema)
Tables reminder:
students
| id | first_name | last_name | gpa | |
|---|---|---|---|---|
| 1 | Aarav | Patel | aarav@hyd.edu | 8.95 |
| 2 | Priya | Reddy | priya@gmail.com | 9.60 |
| 3 | Rahul | Sharma | rahul@yahoo.com | 7.85 |
| 4 | Sneha | Kumar | sneha@gmail.com | 9.20 |
| 5 | Vikram | Naik | vikram@hyd.edu | 8.40 |
enrollments
| student_id | course_id | grade |
|---|---|---|
| 1 | 1 | A |
| 1 | 2 | A+ |
| 2 | 1 | B+ |
| 4 | 3 | A |
| 5 | 2 | B |
→ Rahul (id=3) has no enrollments
Example 1: Students who are enrolled in at least one course
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SELECT first_name || ' ' || last_name AS student_name, gpa FROM students s WHERE EXISTS ( SELECT 1 FROM enrollments e WHERE e.student_id = s.id ) ORDER BY gpa DESC; |
Result:
text
|
0 1 2 3 4 5 6 7 8 9 10 11 |
student_name | gpa ---------------|------ Priya Reddy | 9.60 Sneha Kumar | 9.20 Aarav Patel | 8.95 Vikram Naik | 8.40 |
→ Rahul is excluded because his subquery returns zero rows
Example 2: Courses that have at least one enrollment
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SELECT code, name, credits FROM courses c WHERE EXISTS ( SELECT 1 FROM enrollments e WHERE e.course_id = c.id ) ORDER BY code; |
→ “Artificial Intelligence” (no enrollments) is excluded
Example 3: NOT EXISTS – the classic anti-join pattern
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SELECT first_name || ' ' || last_name AS student_name, email FROM students s WHERE NOT EXISTS ( SELECT 1 FROM enrollments e WHERE e.student_id = s.id ) ORDER BY first_name; |
Result:
text
|
0 1 2 3 4 5 6 7 8 |
student_name | email ---------------|----------------- Rahul Sharma | rahul@yahoo.com |
→ This is the most common real-world use of NOT EXISTS: “Show me records that do not have any related records”
Example 4: EXISTS + extra conditions in subquery
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
-- Students who have at least one 'A' or 'A+' grade SELECT s.first_name || ' ' || s.last_name AS student_name, COUNT(e.id) AS total_enrollments FROM students s JOIN enrollments e ON e.student_id = s.id WHERE EXISTS ( SELECT 1 FROM enrollments e2 WHERE e2.student_id = s.id AND e2.grade IN ('A', 'A+') ) GROUP BY s.id, s.first_name, s.last_name ORDER BY total_enrollments DESC; |
→ Only students who have at least one excellent grade
4. EXISTS vs IN vs JOIN – when to choose what (2026 best practice)
| Requirement | Recommended approach | Why / Performance notes |
|---|---|---|
| “Has at least one related row” | EXISTS or NOT EXISTS | Usually fastest, stops at first match |
| “Value is in a small static list” | IN (1,2,3) | Simple & readable |
| “Value is in a large/dynamic list” | EXISTS or IN (subquery) | EXISTS usually wins on large tables |
| “Show details from both tables” | INNER JOIN or LEFT JOIN | You need columns from the related table |
| “Count / aggregate related rows” | JOIN + GROUP BY | EXISTS cannot return count/sum |
Very common performance rule in PostgreSQL:
- EXISTS(subquery) is often faster than IN (subquery) when the subquery is large
- NOT EXISTS is usually faster than LEFT JOIN … WHERE IS NULL (anti-join pattern)
5. Quick cheat-sheet (keep in your notes)
| Goal | Typical EXISTS pattern | Equivalent but often slower |
|---|---|---|
| Has at least one related row | WHERE EXISTS (SELECT 1 FROM child WHERE …) | WHERE id IN (SELECT parent_id FROM child) |
| Has no related rows | WHERE NOT EXISTS (SELECT 1 FROM child WHERE …) | LEFT JOIN child ON … WHERE child.id IS NULL |
| Has related row matching extra condition | EXISTS (SELECT 1 FROM … WHERE … AND grade = ‘A’) | — |
| Best practice in subquery | SELECT 1 or SELECT id LIMIT 1 | Avoid SELECT * (unnecessary) |
Your mini homework right now
- Write EXISTS query: show all students who have at least one enrollment
- Write NOT EXISTS query: show students with zero enrollments
- Write EXISTS query: show students who have at least one grade ‘A’ or ‘A+’
- Compare: write the same query with IN (subquery) → see which one you prefer
Next class?
Tell me:
- Want EXISTS vs IN vs JOIN performance & EXPLAIN comparison?
- EXISTS inside UPDATE / DELETE (very powerful)?
- Correlated subqueries vs uncorrelated?
- Or move to CTE, WINDOW functions, LATERAL joins?
Your teacher is ready — what’s the next topic? 🚀
