Multiple EXISTS Conditions in a Single Query
You'll learn how to chain multiple EXISTS clauses together using AND/OR logic to express sophisticated multi-relationship requirements in a single query.
The Core Idea
Just as you can combine simple filters with AND/OR in a WHERE clause, you can combine multiple EXISTS conditions to find records that satisfy several relationship requirements simultaneously. This is like filtering job candidates who must have both management experience and technical certifications—each EXISTS checks for a different relationship independently.
Combining EXISTS with Boolean Logic
You can use AND to require all relationships exist:
SELECT *
FROM employees e
WHERE EXISTS (
SELECT 1 FROM projects p WHERE p.employee_id = e.id
)
AND EXISTS (
SELECT 1 FROM certifications c WHERE c.employee_id = e.id
);
This finds employees who are assigned to at least one project and hold at least one certification.
Use OR when any relationship suffices:
SELECT *
FROM customers c
WHERE EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.status = 'pending'
)
OR EXISTS (
SELECT 1 FROM support_tickets t WHERE t.customer_id = c.id AND t.status = 'open'
);
This finds customers with either pending orders or open support tickets (or both).
Mixing EXISTS and NOT EXISTS
You can also combine positive and negative checks:
SELECT *
FROM products p
WHERE EXISTS (
SELECT 1 FROM sales s WHERE s.product_id = p.id
)
AND NOT EXISTS (
SELECT 1 FROM returns r WHERE r.product_id = p.id
);
This finds products that have sales but have never been returned—perfect for identifying reliable products.
Key Takeaway: Multiple EXISTS conditions with AND/OR logic let you express complex multi-relationship requirements cleanly, checking whether records satisfy combinations of independent relationship criteria.