</>
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.

#indexing#performance
typescriptbeginner

Next.js Image Optimization Patterns

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

#images#optimization
sqlintermediate

Keyset Pagination vs Offset

Efficient keyset (cursor) pagination pattern compared to traditional OFFSET for large datasets in PostgreSQL.

#pagination#performance
sqladvanced

Materialized View with Auto-Refresh

Create and maintain materialized views for expensive aggregate queries with concurrent refresh support.

#materialized-view#performance