PostgreSQL Exists
Understanding How to Check If Linked Records Are Present
PostgreSQL offers a method to verify whether a related entry exists inside another query. This technique is helpful when you want to confirm if a match occurs in a different table.
🔍 How Does It Function?
The EXISTS clause returns a true result when the attached subquery finds at least one match. If no matches appear, it gives back false.
It doesn’t return the actual data from the subquery—it just says whether something was found.
Example: Find Customers With At Least One Order
Let’s say we want to pick out clients who have placed any kind of purchase:
SELECT c.name
FROM clients c
WHERE EXISTS (
SELECT 1
FROM purchases p
WHERE p.client_id = c.id
); The subquery checks if any matching purchase belongs to each person. If yes, the outer query includes that person’s name.
How Is This Useful?
It’s fast and efficient because the database stops looking once it finds a single match—it doesn’t scan the entire subquery result.
NOT EXISTS: Checking for Missing Links
When you want to list entries that don’t appear in a connected table, you flip it using NOT EXISTS.
Example: List Customers With No Orders
Suppose we want to show only those who haven’t bought anything yet:
SELECT c.name
FROM clients c
WHERE NOT EXISTS (
SELECT 1
FROM purchases p
WHERE p.client_id = c.id
); Now, only the people without any purchase history will show up.
Quick Summary
| Operator | Returns True When... | Used For |
|---|---|---|
| EXISTS | Subquery finds at least one matching entry | Confirming existence |
| NOT EXISTS | Subquery fails to find a related record | Finding what’s missing |
Prefer Learning by Watching?
Watch these YouTube tutorials to understand POSTGRESQL Tutorial visually:
What You'll Learn:
- 📌 How to use the SQL EXISTS and NOT EXISTS conditional operators
- 📌 SQL Tutorial #13 - SQL EXISTS and NOT EXISTS Operator