SQL Cheat Sheet for Developers: From Basics to Advanced Queries in 2026

SQL remains one of the most important skills a developer can have in 2026. Whether you are building REST APIs, working with analytics pipelines, or debugging production data, you will inevitably need to write and understand SQL. This cheat sheet goes well beyond the basics, giving you a practical reference that covers everything from simple SELECT statements to advanced window functions and query optimization strategies.

Basic SELECT Queries

Every SQL journey starts with the SELECT statement. It is the foundation of all data retrieval operations.

Selecting Columns

-- Select specific columns
SELECT first_name, last_name, email
FROM users;

-- Select all columns (avoid in production code)
SELECT *
FROM users;

-- Aliasing columns for clarity
SELECT
  first_name AS "First Name",
  last_name AS "Last Name",
  created_at AS "Registration Date"
FROM users;

-- Selecting distinct values
SELECT DISTINCT department
FROM employees;

Filtering with WHERE

The WHERE clause lets you filter rows based on conditions. Understanding operator precedence and NULL handling is critical for writing correct queries.

-- Basic comparison
SELECT * FROM products
WHERE price > 50.00;

-- Multiple conditions with AND / OR
SELECT * FROM orders
WHERE status = 'shipped'
  AND total_amount > 100
  AND created_at >= '2026-01-01';

-- IN operator for multiple values
SELECT * FROM users
WHERE country IN ('US', 'CA', 'GB', 'DE');

-- BETWEEN for ranges (inclusive)
SELECT * FROM events
WHERE event_date BETWEEN '2026-01-01' AND '2026-12-31';

-- Pattern matching with LIKE
SELECT * FROM products
WHERE name LIKE '%wireless%';  -- contains 'wireless'

-- Handling NULLs (= does NOT work with NULL)
SELECT * FROM users
WHERE phone IS NULL;

SELECT * FROM users
WHERE phone IS NOT NULL;

A common mistake is writing WHERE column = NULL instead of WHERE column IS NULL. The former always evaluates to UNKNOWN and returns no rows.

Sorting with ORDER BY

-- Single column sort
SELECT * FROM products
ORDER BY price DESC;

-- Multiple column sort
SELECT * FROM employees
ORDER BY department ASC, salary DESC;

-- Sort by column position (less readable, but valid)
SELECT first_name, last_name, hire_date
FROM employees
ORDER BY 3 DESC;

-- NULLS FIRST / NULLS LAST (PostgreSQL, Oracle)
SELECT * FROM tasks
ORDER BY due_date ASC NULLS LAST;

Limiting Results

-- PostgreSQL / MySQL
SELECT * FROM logs
ORDER BY created_at DESC
LIMIT 100;

-- With offset for pagination
SELECT * FROM products
ORDER BY id
LIMIT 20 OFFSET 40;  -- Page 3 (20 items per page)

-- SQL Server
SELECT TOP 100 * FROM logs
ORDER BY created_at DESC;

-- Standard SQL (FETCH FIRST)
SELECT * FROM logs
ORDER BY created_at DESC
FETCH FIRST 100 ROWS ONLY;

Aggregate Functions and GROUP BY

Aggregate functions collapse multiple rows into a single summary value. They are almost always used alongside GROUP BY.

-- Common aggregate functions
SELECT
  department,
  COUNT(*) AS employee_count,
  AVG(salary) AS avg_salary,
  MIN(salary) AS min_salary,
  MAX(salary) AS max_salary,
  SUM(salary) AS total_payroll
FROM employees
GROUP BY department;

-- Filtering groups with HAVING
SELECT
  category,
  COUNT(*) AS product_count,
  AVG(price) AS avg_price
FROM products
GROUP BY category
HAVING COUNT(*) > 10
ORDER BY avg_price DESC;

-- COUNT variations
SELECT
  COUNT(*) AS total_rows,         -- counts all rows including NULLs
  COUNT(email) AS has_email,      -- counts non-NULL emails
  COUNT(DISTINCT city) AS cities  -- counts unique cities
FROM users;

The key distinction: WHERE filters individual rows before aggregation, while HAVING filters groups after aggregation. You cannot reference aggregate functions in a WHERE clause.

JOINs: Combining Tables

JOINs are where SQL becomes truly powerful. Understanding the different types is essential for working with relational data.

INNER JOIN

Returns only rows that have matching values in both tables.

SELECT
  o.id AS order_id,
  o.order_date,
  c.name AS customer_name,
  c.email
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;

LEFT JOIN (LEFT OUTER JOIN)

Returns all rows from the left table and matching rows from the right table. Unmatched right-side columns return NULL.

-- Find all customers and their orders (including customers with no orders)
SELECT
  c.name,
  c.email,
  o.id AS order_id,
  o.total_amount
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;

-- Find customers who have never placed an order
SELECT c.name, c.email
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.id IS NULL;

RIGHT JOIN and FULL OUTER JOIN

-- RIGHT JOIN: all rows from the right table
SELECT
  e.name AS employee,
  d.name AS department
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;

-- FULL OUTER JOIN: all rows from both tables
SELECT
  s.name AS student,
  c.title AS course
FROM students s
FULL OUTER JOIN enrollments e ON s.id = e.student_id
FULL OUTER JOIN courses c ON e.course_id = c.id;

CROSS JOIN

Produces the Cartesian product of two tables. Every row in the first table is combined with every row in the second table.

-- Generate all possible size-color combinations
SELECT
  s.size_name,
  c.color_name
FROM sizes s
CROSS JOIN colors c;

Self JOIN

A table joined to itself, useful for hierarchical or comparative data.

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

Subqueries

Subqueries are queries nested inside other queries. They can appear in SELECT, FROM, WHERE, and HAVING clauses.

Subquery in WHERE

-- Find products priced above the average
SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- Find customers who placed orders in the last 30 days
SELECT name, email
FROM customers
WHERE id IN (
  SELECT DISTINCT customer_id
  FROM orders
  WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
);

Correlated Subqueries

A correlated subquery references columns from the outer query and executes once per outer row. They are powerful but can be slower than JOINs on large datasets.

-- Find employees who earn more than their department average
SELECT e.name, e.salary, e.department_id
FROM employees e
WHERE e.salary > (
  SELECT AVG(e2.salary)
  FROM employees e2
  WHERE e2.department_id = e.department_id
);

-- EXISTS check
SELECT c.name
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.id
    AND o.total_amount > 1000
);

Subquery in FROM (Derived Tables)

SELECT
  dept_stats.department,
  dept_stats.avg_salary,
  dept_stats.employee_count
FROM (
  SELECT
    department,
    AVG(salary) AS avg_salary,
    COUNT(*) AS employee_count
  FROM employees
  GROUP BY department
) AS dept_stats
WHERE dept_stats.employee_count > 5
ORDER BY dept_stats.avg_salary DESC;

Common Table Expressions (CTEs)

CTEs provide a way to write modular, readable SQL. They define temporary named result sets that exist for the duration of a single query.

Basic CTE

WITH active_customers AS (
  SELECT
    c.id,
    c.name,
    c.email,
    COUNT(o.id) AS order_count,
    SUM(o.total_amount) AS total_spent
  FROM customers c
  JOIN orders o ON c.id = o.customer_id
  WHERE o.order_date >= '2026-01-01'
  GROUP BY c.id, c.name, c.email
)
SELECT *
FROM active_customers
WHERE total_spent > 500
ORDER BY total_spent DESC;

Multiple CTEs

WITH
monthly_revenue AS (
  SELECT
    DATE_TRUNC('month', order_date) AS month,
    SUM(total_amount) AS revenue
  FROM orders
  WHERE order_date >= '2025-01-01'
  GROUP BY DATE_TRUNC('month', order_date)
),
revenue_with_growth AS (
  SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue,
    ROUND(
      (revenue - LAG(revenue) OVER (ORDER BY month))
      / LAG(revenue) OVER (ORDER BY month) * 100, 2
    ) AS growth_pct
  FROM monthly_revenue
)
SELECT *
FROM revenue_with_growth
ORDER BY month;

Recursive CTEs

Recursive CTEs are perfect for hierarchical data such as org charts, category trees, or graph traversal.

WITH RECURSIVE org_chart AS (
  -- Base case: top-level managers (no manager)
  SELECT id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive case: employees with a manager already in the result
  SELECT e.id, e.name, e.manager_id, oc.level + 1
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT
  REPEAT('  ', level - 1) || name AS org_tree,
  level
FROM org_chart
ORDER BY level, name;

Window Functions

Window functions perform calculations across a set of rows related to the current row without collapsing them into a single output row. They are one of the most powerful features in modern SQL.

ROW_NUMBER, RANK, and DENSE_RANK

SELECT
  name,
  department,
  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;

The difference: if two employees tie at rank 2, ROW_NUMBER assigns them 2 and 3 arbitrarily, RANK assigns both 2 and skips to 4, and DENSE_RANK assigns both 2 and continues to 3.

Partitioned Window Functions

-- Top 3 earners per department
WITH ranked AS (
  SELECT
    name,
    department,
    salary,
    ROW_NUMBER() OVER (
      PARTITION BY department
      ORDER BY salary DESC
    ) AS dept_rank
  FROM employees
)
SELECT * FROM ranked
WHERE dept_rank <= 3;

LAG and LEAD

These functions let you access values from preceding or following rows.

SELECT
  order_date,
  total_amount,
  LAG(total_amount, 1) OVER (ORDER BY order_date) AS prev_day_amount,
  LEAD(total_amount, 1) OVER (ORDER BY order_date) AS next_day_amount,
  total_amount - LAG(total_amount, 1) OVER (ORDER BY order_date) AS daily_change
FROM daily_sales;

Running Totals and Moving Averages

SELECT
  order_date,
  total_amount,
  SUM(total_amount) OVER (
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total,
  AVG(total_amount) OVER (
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS seven_day_avg
FROM daily_sales;

NTILE for Bucketing

-- Divide customers into 4 quartiles by spending
SELECT
  name,
  total_spent,
  NTILE(4) OVER (ORDER BY total_spent DESC) AS spending_quartile
FROM customer_summary;

Indexing Strategies

Writing efficient SQL is not just about query syntax; it is also about ensuring the database can execute your queries quickly. Indexes are the primary mechanism for this.

Types of Indexes

-- B-tree index (default, good for equality and range queries)
CREATE INDEX idx_users_email ON users (email);

-- Composite index (column order matters)
CREATE INDEX idx_orders_customer_date
ON orders (customer_id, order_date DESC);

-- Unique index (also enforces uniqueness)
CREATE UNIQUE INDEX idx_users_email_unique ON users (email);

-- Partial index (PostgreSQL - index only a subset of rows)
CREATE INDEX idx_orders_pending
ON orders (created_at)
WHERE status = 'pending';

-- GIN index for full-text search (PostgreSQL)
CREATE INDEX idx_articles_search
ON articles USING GIN (to_tsvector('english', title || ' ' || body));

When to Create Indexes

Indexes speed up reads but slow down writes. Create indexes on columns that appear frequently in WHERE clauses, JOIN conditions, and ORDER BY clauses. Avoid indexing columns with very low cardinality (such as a boolean column) unless you are using a partial index.

A composite index on (a, b, c) can satisfy queries filtering on a, on a AND b, or on a AND b AND c, but not on b alone or c alone. Column order matters.

Checking Query Performance with EXPLAIN

-- PostgreSQL
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 42
  AND order_date >= '2026-01-01';

-- MySQL
EXPLAIN
SELECT * FROM orders
WHERE customer_id = 42
  AND order_date >= '2026-01-01';

Look for sequential scans (Seq Scan) on large tables, which indicate a missing index. An Index Scan or Index Only Scan is what you want to see.

Query Optimization Tips

1. Avoid SELECT *

Always specify the columns you need. This reduces I/O, memory usage, and can enable index-only scans.

-- Bad
SELECT * FROM users WHERE id = 42;

-- Good
SELECT id, name, email FROM users WHERE id = 42;

2. Use EXISTS Instead of IN for Large Subqueries

-- Slower with large subquery results
SELECT * FROM customers
WHERE id IN (SELECT customer_id FROM orders);

-- Faster: stops at first match
SELECT * FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

3. Avoid Functions on Indexed Columns

-- Bad: index on created_at is not used
SELECT * FROM orders
WHERE YEAR(created_at) = 2026;

-- Good: index-friendly range scan
SELECT * FROM orders
WHERE created_at >= '2026-01-01'
  AND created_at < '2027-01-01';

4. Use UNION ALL Instead of UNION When Possible

UNION removes duplicates (requiring a sort), while UNION ALL does not. If you know the result sets are already distinct, always prefer UNION ALL.

-- Slower (deduplicates)
SELECT name FROM employees
UNION
SELECT name FROM contractors;

-- Faster (no deduplication)
SELECT name FROM employees
UNION ALL
SELECT name FROM contractors;

5. Batch Operations for Large Data

-- Instead of deleting millions of rows at once
-- Delete in batches to avoid locking the table
DELETE FROM logs
WHERE created_at < '2025-01-01'
LIMIT 10000;
-- Repeat until 0 rows affected

6. Use Appropriate Data Types

Smaller data types mean more rows fit in memory and index pages. Use INTEGER instead of BIGINT when the range allows it. Use VARCHAR with appropriate lengths rather than TEXT for indexed columns.

Useful SQL Patterns

UPSERT (INSERT ON CONFLICT)

-- PostgreSQL
INSERT INTO user_settings (user_id, theme, language)
VALUES (42, 'dark', 'en')
ON CONFLICT (user_id)
DO UPDATE SET
  theme = EXCLUDED.theme,
  language = EXCLUDED.language;

-- MySQL
INSERT INTO user_settings (user_id, theme, language)
VALUES (42, 'dark', 'en')
ON DUPLICATE KEY UPDATE
  theme = VALUES(theme),
  language = VALUES(language);

Pivot Data with CASE

SELECT
  product_id,
  SUM(CASE WHEN month = 1 THEN revenue ELSE 0 END) AS jan,
  SUM(CASE WHEN month = 2 THEN revenue ELSE 0 END) AS feb,
  SUM(CASE WHEN month = 3 THEN revenue ELSE 0 END) AS mar
FROM monthly_revenue
GROUP BY product_id;

Generating Date Series

-- PostgreSQL: generate a series of dates
SELECT generate_series(
  '2026-01-01'::date,
  '2026-12-31'::date,
  '1 day'::interval
)::date AS date;

-- Use with LEFT JOIN to fill gaps in time series data
SELECT
  d.date,
  COALESCE(s.total, 0) AS daily_total
FROM generate_series('2026-01-01'::date, '2026-03-31'::date, '1 day') AS d(date)
LEFT JOIN daily_sales s ON s.sale_date = d.date
ORDER BY d.date;

Quick Reference Table

Operation	Syntax
Filter rows	WHERE condition
Sort results	ORDER BY column ASC/DESC
Limit rows	LIMIT n or FETCH FIRST n ROWS ONLY
Remove duplicates	SELECT DISTINCT
Group and aggregate	GROUP BY ... HAVING
Combine tables	JOIN ... ON
Temporary result set	WITH cte AS (...)
Row numbering	ROW_NUMBER() OVER (...)
Previous/next row	LAG() / LEAD() OVER (...)
Running total	SUM() OVER (ORDER BY ... ROWS ...)
Insert or update	INSERT ... ON CONFLICT DO UPDATE

Conclusion

SQL is a vast language, but mastering the patterns in this cheat sheet will cover the vast majority of real-world scenarios you will encounter as a developer. Start with solid SELECT and JOIN fundamentals, then progressively add CTEs and window functions to your toolkit. Always keep indexing and query performance in mind, especially as your data grows.

Try our SQL Formatter to beautify your queries.

Bookmark this page and come back whenever you need a quick reference. The best way to internalize SQL is to practice regularly, whether that means writing queries against your own databases, using platforms like SQLite in-memory sandboxes, or exploring open datasets. The patterns you build today will serve you throughout your entire career.