</>
sqlbeginner

EXISTS vs IN Subquery Patterns

Choose between EXISTS and IN subqueries for optimal performance based on data distribution.

sql
-- EXISTS: stops at first match (good for large subquery results)
SELECT *
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id
    AND o.created_at > '2025-01-01'
);

-- NOT EXISTS: anti-join (find customers with no recent orders)
SELECT *
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id
    AND o.created_at > '2025-01-01'
);

-- IN: good for small, static sets
SELECT * FROM products
WHERE category_id IN (
  SELECT id FROM categories WHERE is_featured = true
);

Use Cases

  • Filtering with related tables
  • Anti-join patterns
  • Query optimization

Tags

#subquery#exists#performance#optimization

Related Snippets

Similar patterns you can reuse in the same workflow.

sqlintermediate

Covering Index (INCLUDE Columns)

Create covering indexes with INCLUDE columns to satisfy queries entirely from the index.

Best for: Index-only scans

#indexing#performance
sqladvanced

SQL Index Strategy Patterns

Create effective indexes including composite, partial, covering, and expression-based indexes.

Best for: Optimizing slow database queries

#sql#indexes
sqladvanced

Read EXPLAIN ANALYZE Output

Use EXPLAIN ANALYZE to understand and optimize query execution plans.

Best for: Query performance tuning

#sql#explain
typescriptbeginner

Next.js Image Optimization Patterns

Use next/image with responsive sizes, blur placeholders, and priority loading for optimal Core Web Vitals.

Best for: Hero images

#images#optimization