SQL Patterns

When to Use

• Write or optimize SQL queries (joins, subqueries, CTEs, window functions)
• Design or review a database schema
• Analyze query performance using EXPLAIN plans
• Choose indexing strategies for a workload
• Fix slow queries or identify anti-patterns
• Implement pagination, deduplication, or analytical queries

Instructions

1. Query Construction

• INNER JOIN

  — only matching rows in both tables (default, most common)

• LEFT JOIN

  — all rows from left table, NULLs where right has no match

• RIGHT JOIN

  — all rows from right table (rare; rewrite as LEFT JOIN for clarity)

• FULL OUTER JOIN

  — all rows from both, NULLs on the unmatched side

• CROSS JOIN

  — cartesian product (use intentionally for generating combinations)

WITH active_users AS (
    SELECT user_id, MAX(login_at) AS last_login
    FROM logins
    WHERE login_at > CURRENT_DATE - INTERVAL '30 days'
    GROUP BY user_id
),
user_orders AS (
    SELECT user_id, COUNT(*) AS order_count, SUM(total) AS revenue
    FROM orders
    GROUP BY user_id
)
SELECT u.name, au.last_login, uo.order_count, uo.revenue
FROM users u
JOIN active_users au USING (user_id)
LEFT JOIN user_orders uo USING (user_id);

SELECT
    department,
    employee,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
    salary - LAG(salary) OVER (PARTITION BY department ORDER BY salary) AS diff_from_prev,
    SUM(salary) OVER (PARTITION BY department) AS dept_total
FROM employees;

WHERE

GROUP BY

HAVING

HAVING

2. Query Optimization

1. Read the EXPLAIN plan — look for sequential scans on large tables, nested loops on large result sets, sort operations without indexes
2. Check indexes — ensure WHERE, JOIN, and ORDER BY columns are indexed
3. Reduce data early — filter in WHERE before joining; join on indexed columns
4. Avoid anti-patterns:

   • SELECT *

     in production queries (fetches unnecessary columns)
   • Functions on indexed columns in WHERE (

     WHERE YEAR(created_at) = 2025

     defeats the index; use range instead)

   • NOT IN

     with NULLable columns (use

     NOT EXISTS

     instead)
   • Correlated subqueries that execute per-row (rewrite as JOIN or CTE)
   • Missing LIMIT on exploratory queries
   • Implicit type conversions that prevent index use

3. Indexing Strategy

• B-tree (default) — equality, range, ORDER BY, LIKE 'prefix%'
• Hash — equality only, smaller than B-tree (Postgres-specific)
• GIN — full-text search, JSONB containment, array operations
• GiST — geometric data, range types, nearest-neighbor
• Composite indexes — column order matters; leftmost prefix rule applies
• Partial indexes —

  WHERE is_active = true

  for queries that always filter on that condition
• Covering indexes —

  INCLUDE

  columns to enable index-only scans

4. Transactions and Concurrency

• Use explicit transactions for multi-statement operations
• Choose the right isolation level: READ COMMITTED (default, good for most), REPEATABLE READ (consistent snapshots), SERIALIZABLE (full isolation, retry on conflict)
• Keep transactions short — avoid user interaction inside a transaction
• Use

  SELECT ... FOR UPDATE

  when reading rows you intend to modify
• Handle deadlocks with retry logic, not just longer timeouts

5. Schema Design Basics

references/schema-design.md

• Normalize to 3NF by default, denormalize intentionally with a documented reason
• Use surrogate keys (auto-increment or UUID) as primary keys
• Add

  created_at

  and

  updated_at

  timestamps to all tables
• Use foreign keys to enforce referential integrity
• Name constraints explicitly for readable error messages

references/query-patterns.md

Examples

ROW_NUMBER()

row_num <= 3

(category_id, revenue DESC)

EXPLAIN ANALYZE

(status, created_at)

email

WHERE status = 'pending'