Course contentsShow
← Data Science
Lesson 988 of 2,14525. SQL Subqueries and CTEsFree lesson

Common EXISTS Patterns and Anti-Patterns

Recognize typical use cases for EXISTS and avoid common mistakes like unnecessary SELECT * in the subquery.

Common EXISTS Patterns and Anti-Patterns

You'll learn: How to recognize typical use cases for EXISTS and avoid common mistakes that hurt readability or performance.

The Core Idea

Now that you understand EXISTS, let's identify patterns that work well and pitfalls to avoid. The most important anti-pattern? Writing SELECT * in your EXISTS subquery when you don't need to — EXISTS only checks if rows exist, not what data they contain.

Smart Patterns ✅

Pattern 1: Use SELECT 1 or SELECT NULL Since EXISTS only checks for row existence, use a constant instead of selecting actual columns:

-- Good: Lightweight and clear
SELECT customer_id
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

Pattern 2: Multiple EXISTS for Complex Logic Chain EXISTS conditions to express "must have A AND must have B":

WHERE EXISTS (SELECT 1 FROM orders WHERE ...)
  AND EXISTS (SELECT 1 FROM reviews WHERE ...);

Pattern 3: NOT EXISTS for Anti-Joins Finding missing relationships is EXISTS's sweet spot — clearer than LEFT JOIN with NULL checks.

Common Anti-Patterns ❌

Anti-Pattern 1: Unnecessary Column Selection

-- Wasteful: Database might fetch unneeded data
WHERE EXISTS (SELECT customer_name, email FROM ...);

Anti-Pattern 2: Using EXISTS When IN with Literals Would Be Clearer If you're checking against a small, static list, IN ('A', 'B', 'C') is simpler than EXISTS with a subquery.

Anti-Pattern 3: Forgetting the Correlation An uncorrelated EXISTS subquery returns the same result for every row — probably not what you want.

Key Takeaway:
Always use SELECT 1 (or similar constant) in EXISTS subqueries since only row existence matters, and ensure your subquery is properly correlated to the outer query when checking row-specific conditions.