Tutorial SQL Database Query Optimization Developer Tools Cheat Sheet

Free SQL Advanced Patterns Cheat Sheet Online — Interactive Query Reference

· 16 min read

Structured Query Language is the universal language of data. Every application that persists information — from a todo list to a global analytics platform — eventually translates user actions into SQL queries that read, filter, aggregate, and transform rows in a database. While sql cheat sheet references for basic CRUD are easy to find, advanced SQL patterns separate scripts that merely run from queries that scale. Understanding sql window functions, sql cte recursion, and sql join types deeply is what turns a data consumer into a data architect.

Our free interactive SQL Advanced Patterns cheat sheet provides fifty-five copyable code examples organized into eight categories: Advanced SELECT, JOINs, Aggregates & Grouping, Window Functions, CTEs & Recursive Queries, Data Modification, Performance & Optimization, and Advanced Patterns. The Clockwork Registrar's Archive aesthetic uses deep mahogany backgrounds, brass gear accents, and a subtle rotating gear animation — reinforcing the idea that every SQL query is a precisely engineered mechanism turning raw data into insight. Everything runs in your browser. No server, no signup, no data collection.

Why Advanced SQL Patterns Matter

Basic SQL — SELECT, INSERT, UPDATE, DELETE — gets you to a working prototype. But production databases demand more. Reporting queries need rolling totals and moving averages. Data pipelines need recursive traversal of hierarchical data. ETL processes need pivot transforms and deduplication. Performance optimization requires understanding indexes, execution plans, and covering indexes.

Advanced SQL provides three advantages. First, you reduce application-layer complexity. A single sql window functions query can replace pages of application code that fetches rows and computes rankings in memory. Second, you improve performance. Database engines are optimized for set-based operations. Pushing logic into SQL with sql common table expressions and window frames leverages decades of query-planning research. Third, you gain portability. SQL is the closest thing to a universal data language. Window functions, CTEs, and standard JOIN syntax work across PostgreSQL, MySQL, SQL Server, Oracle, and SQLite with only minor dialect differences.

Advanced SELECT

Before you can aggregate or window, you must retrieve the right rows. Sql subquery patterns, correlated subqueries, and existence tests are the building blocks of complex reporting queries.

Subqueries and Correlated Subqueries

A subquery is a query nested inside another query. It can appear in the SELECT list, the FROM clause, or the WHERE clause. A correlated subquery references columns from the outer query and executes once per outer row. Use correlated subqueries sparingly on large datasets because they scale poorly — but they are sometimes the clearest way to express row-by-row logic.

SELECT e.name, e.salary
FROM employees e
WHERE e.salary > (
  SELECT AVG(salary)
  FROM employees
  WHERE department_id = e.department_id
);

EXISTS and NOT EXISTS

The sql exists operator tests for the presence of rows in a subquery. It stops at the first match, making it often faster than IN with large subqueries. Sql not exists is the inverse: it returns true only when the subquery returns zero rows. These patterns are essential for semi-join and anti-join logic.

-- Departments with at least one employee
SELECT d.name FROM departments d
WHERE EXISTS (
  SELECT 1 FROM employees e WHERE e.dept_id = d.id
);

-- Departments with no employees
SELECT d.name FROM departments d
WHERE NOT EXISTS (
  SELECT 1 FROM employees e WHERE e.dept_id = d.id
);

CASE, COALESCE, and NULLIF

Conditional logic inside SQL is handled by the CASE expression. Sql case when supports both simple equality and searched boolean forms. Sql coalesce returns the first non-NULL value, making it ideal for providing fallback values. Sql nullif returns NULL when two expressions match, which prevents division-by-zero errors elegantly.

SELECT
  name,
  CASE
    WHEN salary >= 100000 THEN "Senior"
    WHEN salary >= 60000 THEN "Mid"
    ELSE "Junior"
  END AS level,
  COALESCE(phone, email, "No contact") AS contact,
  revenue / NULLIF(cost, 0) AS margin
FROM employees;

JOINs

Relational databases store data in normalized tables. JOINs are the mechanism for reassembling that data into meaningful rows. Understanding the full spectrum of sql join types — not just INNER JOIN — is essential for correct results.

INNER, LEFT, RIGHT, and FULL OUTER JOIN

INNER JOIN returns only rows with matches in both tables. Sql left join returns all rows from the left table and matching rows from the right, filling unmatched right columns with NULL. RIGHT JOIN is the mirror image. Sql full outer join returns all rows from both tables, with NULLs where there is no match. FULL OUTER JOIN is particularly useful for finding orphaned records on both sides of a relationship.

-- All customers and their orders, including customers who never ordered
SELECT c.name, o.order_date
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;

-- Rows in A but not B, and vice versa
SELECT a.id, a.value, b.value
FROM table_a a
FULL OUTER JOIN table_b b ON a.id = b.id
WHERE a.id IS NULL OR b.id IS NULL;

CROSS JOIN, SELF JOIN, and NATURAL JOIN

Sql cross join returns the Cartesian product: every row from table A paired with every row from table B. Use it for generating combinations, but avoid implicit cross joins (comma-separated tables without a WHERE clause) because they are error-prone. Sql self join joins a table to itself, which is essential for hierarchical data like employee-manager relationships. NATURAL JOIN automatically joins on all columns with matching names — convenient but risky, because schema changes can silently alter query semantics.

-- Self join: employee and their manager
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- Cross join: all product-store combinations
SELECT p.name AS product, s.name AS store
FROM products p
CROSS JOIN stores s;

Aggregates and Grouping

Aggregation collapses many rows into summary rows. The combination of sql group by, sql having, and sql aggregate functions like COUNT, SUM, AVG, MIN, and MAX is the foundation of analytics.

GROUP BY and HAVING

Sql group by divides rows into groups sharing the same values in specified columns. Sql having filters these groups after aggregation. WHERE filters rows before grouping — they are not interchangeable. A common mistake is trying to use WHERE with aggregate conditions.

-- Wrong: WHERE COUNT(*) > 5
SELECT department, COUNT(*) AS headcount
FROM employees
WHERE status = "active"
GROUP BY department
HAVING COUNT(*) > 5;

ROLLUP, CUBE, and GROUPING SETS

ROLLUP generates subtotals and a grand total for hierarchical dimensions. CUBE generates all possible combinations of grouping columns, producing 2^n grouping sets. GROUPING SETS lets you specify exactly which combinations you want. These operators replace verbose UNION ALL queries and let the database optimize the aggregation internally.

SELECT year, quarter, SUM(revenue)
FROM sales
GROUP BY ROLLUP(year, quarter);

-- Produces: (year, quarter), (year, NULL), (NULL, NULL)

Window Functions

Sql window functions are among the most powerful additions to the SQL standard. They compute values across a set of rows related to the current row without collapsing groups. This makes them ideal for rankings, running totals, and period-over-period comparisons.

ROW_NUMBER, RANK, and DENSE_RANK

Sql row_number assigns a unique sequential integer starting at 1. Sql rank assigns a rank with gaps after ties. Sql dense_rank assigns a rank without gaps. Use ROW_NUMBER when you need a unique identifier per row. Use RANK for competition-style rankings where ties share a position. Use DENSE_RANK when you want consecutive ranks after ties.

SELECT
  name, salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
  RANK() OVER (ORDER BY salary DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

LEAD, LAG, FIRST_VALUE, and LAST_VALUE

Sql lead lag functions access data from subsequent or preceding rows. They are essential for computing changes between consecutive periods. FIRST_VALUE and LAST_VALUE return boundary values within a window frame. Combined with sql partition by, these functions let you compare each row to its peers within the same group.

SELECT
  month, revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_month,
  revenue - LAG(revenue) OVER (ORDER BY month) AS change,
  FIRST_VALUE(revenue) OVER (
    PARTITION BY year ORDER BY month
  ) AS year_start
FROM monthly_sales;

Window Frames with ROWS and RANGE

The OVER clause defines a window. You can further restrict it with ROWS or RANGE to create sliding windows for moving averages and running totals. ROWS operates on physical row offsets; RANGE operates on logical value offsets.

SELECT
  date, revenue,
  SUM(revenue) OVER (
    ORDER BY date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total,
  AVG(revenue) OVER (
    ORDER BY date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS ma_7day
FROM daily_sales;

CTEs and Recursive Queries

Sql common table expressions (CTEs) create named temporary result sets that exist for the duration of a single query. They improve readability and enable recursion.

Basic and Multiple CTEs

A basic CTE with the WITH clause simplifies complex queries by breaking them into named steps. Multiple CTEs can be chained, with later CTEs referencing earlier ones.

WITH
  dept_stats AS (
    SELECT dept, AVG(salary) AS avg_sal
    FROM employees GROUP BY dept
  ),
  above_avg AS (
    SELECT e.name, e.salary, e.dept, d.avg_sal
    FROM employees e
    JOIN dept_stats d ON e.dept = d.dept
    WHERE e.salary > d.avg_sal
  )
SELECT * FROM above_avg;

Recursive CTEs

A sql recursive query CTE references itself. It requires an anchor member (the base case) and a recursive member that joins back to the CTE. Recursive CTEs are the standard way to traverse hierarchical data like org charts, bill-of-materials trees, and file systems.

WITH RECURSIVE subordinates AS (
  SELECT id, name, manager_id, 1 AS level
  FROM employees WHERE manager_id = 1

  UNION ALL

  SELECT e.id, e.name, e.manager_id, s.level + 1
  FROM employees e
  INNER JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;

Data Modification

Bulk data operations are safer and faster when performed in SQL rather than row-by-row in application code.

INSERT INTO SELECT and MERGE / UPSERT

INSERT INTO SELECT transfers data between tables in a single statement. MERGE (or UPSERT) atomically inserts new rows or updates existing ones based on a key match. Dialects differ: PostgreSQL uses ON CONFLICT, MySQL uses ON DUPLICATE KEY UPDATE, and SQL Server has a dedicated MERGE statement.

-- PostgreSQL UPSERT
INSERT INTO users (id, name, email)
VALUES (1, "Alice", "alice@example.com")
ON CONFLICT (id) DO UPDATE
SET name = EXCLUDED.name, email = EXCLUDED.email;

-- MySQL UPSERT
INSERT INTO users (id, name, email)
VALUES (1, "Alice", "alice@example.com")
ON DUPLICATE KEY UPDATE
  name = VALUES(name), email = VALUES(email);

UPDATE and DELETE with JOIN

Updating or deleting rows based on conditions in related tables requires JOIN syntax in the modification statement. PostgreSQL, MySQL, and SQL Server each have slightly different syntax for this pattern.

-- PostgreSQL
UPDATE employees e
SET salary = salary * 1.1
FROM departments d
WHERE e.dept_id = d.id AND d.name = "Engineering";

-- MySQL
UPDATE employees e
JOIN departments d ON e.dept_id = d.id
SET salary = salary * 1.1
WHERE d.name = "Engineering";

Performance and Optimization

Query correctness is table stakes. Query performance is what keeps systems running at scale.

Indexes and Covering Indexes

An index is a data structure that speeds up lookups at the cost of slower writes. A covering index includes all columns needed for a query, allowing the database to satisfy the query entirely from the index without touching the table. Composite indexes are ordered — put the most selective column first and match your query's WHERE clause pattern.

-- Composite index for: WHERE status = ? AND created_at > ?
CREATE INDEX idx_orders_status_date
ON orders(status, created_at);

-- Covering index for: SELECT last_name, first_name FROM users WHERE last_name = ?
CREATE INDEX idx_users_name ON users(last_name, first_name);

EXPLAIN and Query Plan Analysis

Sql explain shows the execution plan. EXPLAIN ANALYZE executes the query and shows actual timings. Look for sequential scans on large tables, unindexed nested loops, and expensive sorts. These are your first targets for optimization.

EXPLAIN ANALYZE
SELECT e.name, d.name
FROM employees e
JOIN departments d ON e.dept_id = d.id
WHERE e.salary > 80000;

Advanced Patterns

Beyond the standard operators, there are patterns that solve common but non-obvious problems.

Pivot, Unpivot, and Deduplication

Sql pivot transforms row-based data into columns using conditional aggregation. Unpivot does the reverse. Deduplication uses ROW_NUMBER() to identify and remove duplicate rows while keeping one representative.

-- Pivot: quarters as columns
SELECT year,
  SUM(CASE WHEN quarter = "Q1" THEN revenue ELSE 0 END) AS q1,
  SUM(CASE WHEN quarter = "Q2" THEN revenue ELSE 0 END) AS q2,
  SUM(CASE WHEN quarter = "Q3" THEN revenue ELSE 0 END) AS q3,
  SUM(CASE WHEN quarter = "Q4" THEN revenue ELSE 0 END) AS q4
FROM sales GROUP BY year;

-- Deduplication: keep most recent per email
WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC) AS rn
  FROM users
)
SELECT * FROM ranked WHERE rn = 1;

Running Totals and Moving Averages

Window frames make running totals and moving averages straightforward. These patterns are common in financial reporting, inventory tracking, and trend analysis.

SELECT
  date, revenue,
  SUM(revenue) OVER (
    ORDER BY date
    ROWS UNBOUNDED PRECEDING
  ) AS running_total,
  AVG(revenue) OVER (
    ORDER BY date
    ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
  ) AS ma_30day
FROM daily_sales;

Interactive Cheat Sheet

Reading about SQL patterns is useful. Having a searchable, filterable reference at your fingertips is better. Our SQL Advanced Patterns cheat sheet organizes fifty-five entries across eight categories with copyable code, category filtering, real-time search, and SQL syntax highlighting. The Clockwork Registrar's Archive aesthetic pairs a dark mahogany background with brass gear animations — every category gets a distinct accent color mapped to its theme.

The cheat sheet is 100% client-side. Open it, search for "window function" or "recursive CTE", and copy the exact syntax you need into your query editor. No network requests, no tracking, no account required.

Related Resources

SQL does not exist in isolation. Data flows through HTTP APIs, gets processed by backend services, and is consumed by frontend applications. If you are building the full stack, these related cheat sheets will speed up your workflow:

All tools are free, open, and run entirely in your browser. Bookmark the DevToolkit homepage for quick access to the full directory.

Found this useful? Check out our free developer tools or browse more articles.