All Articles

The Ultimate SQL Cheat Sheet

Reference
#cheat-sheet#sql-reference#sql-syntax#sql-interview#quick-reference

How to Use This Cheat Sheet

A single-page, practical reference covering the SQL syntax and patterns used most often — organized so you can scan to the section you need rather than reading linearly. Every section links to a full, in-depth guide for further detail.

Query Structure and Execution Order

SELECT columns
FROM table
JOIN other_table ON condition
WHERE row_filter
GROUP BY columns
HAVING group_filter
ORDER BY columns
LIMIT n;

-- Logical execution order (different from written order):
-- FROM -> JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT

Full guide: GROUP BY vs HAVING vs WHERE

JOINs at a Glance

INNER JOIN   -- only matching rows from both tables
LEFT JOIN    -- all left rows + matches from right (NULL where no match)
RIGHT JOIN   -- all right rows + matches from left
FULL OUTER JOIN -- all rows from both sides
CROSS JOIN   -- every combination of both tables (Cartesian product)

Full guide: SQL JOINs Explained

Window Functions

ROW_NUMBER() OVER (PARTITION BY col ORDER BY col)   -- unique sequential number, ties broken arbitrarily
RANK()       OVER (PARTITION BY col ORDER BY col)   -- ties share rank, next rank skips
DENSE_RANK() OVER (PARTITION BY col ORDER BY col)   -- ties share rank, no gap after
LAG(col, n)  OVER (ORDER BY col)                     -- value from n rows before
LEAD(col, n) OVER (ORDER BY col)                     -- value from n rows after
SUM/AVG/COUNT(col) OVER (PARTITION BY col ORDER BY col ROWS BETWEEN ... AND ...)

Full guide: SQL Window Functions: The Complete Guide

Common Table Expressions

WITH cte_name AS (SELECT ...)
SELECT * FROM cte_name;

WITH RECURSIVE cte_name AS (
  SELECT ... -- anchor
  UNION ALL
  SELECT ... FROM table JOIN cte_name ON ... -- recursive step
)
SELECT * FROM cte_name;

Full guides: Subqueries Explained, Recursive CTEs Explained, CTE vs Subquery vs Temp Table vs View

NULL Handling

col IS NULL / col IS NOT NULL   -- NEVER use = NULL or != NULL
COALESCE(a, b, c)               -- first non-NULL value
NULLIF(a, b)                    -- NULL if a = b, else a  (use for safe division: a / NULLIF(b, 0))
COUNT(*)      -- counts all rows
COUNT(col)    -- counts only non-NULL values in col

Full guide: NULL Handling in SQL

CASE WHEN

CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ELSE default_result
END

-- Conditional aggregation (also the basis for manual pivoting)
SUM(CASE WHEN condition THEN 1 ELSE 0 END)

Full guides: CASE WHEN Beyond the Basics, Pivot and Unpivot Data

String Functions

CONCAT(a, b) / a || b        -- concatenation
SUBSTRING(str, start, len)   -- extract part of a string (1-indexed)
TRIM(str)                    -- remove leading/trailing whitespace
UPPER(str) / LOWER(str)      -- case conversion
REPLACE(str, old, new)       -- substitute literal text
LENGTH(str)                  -- string length

Full guide: String Functions in SQL

Date and Time Functions

CURRENT_DATE / NOW()
EXTRACT(YEAR FROM date) / YEAR(date)          -- database-dependent syntax
DATE_TRUNC('month', date)                     -- PostgreSQL — truncate to period start
DATEADD(DAY, 7, date) / date + INTERVAL '7 days'
DATEDIFF(DAY, date1, date2)                   -- calendar-boundary difference, not elapsed hours

Full guide: Date and Time Functions in SQL

Constraints

PRIMARY KEY          -- unique + NOT NULL, one per table
FOREIGN KEY ... REFERENCES table(col) ON DELETE CASCADE/SET NULL/RESTRICT
UNIQUE                -- unique, allows multiple NULLs
NOT NULL
CHECK (condition)
DEFAULT value

Full guide: SQL Constraints Explained

Data Types Quick Reference

INT / BIGINT                -- whole numbers
DECIMAL(p, s)                -- exact decimal — ALWAYS use for money, never FLOAT
VARCHAR(n) / TEXT            -- variable-length text
CHAR(n)                      -- fixed-length text
DATE / TIMESTAMP / TIMESTAMPTZ  -- prefer timezone-aware for global data
BOOLEAN
JSON / JSONB                 -- semi-structured data

Full guides: SQL Data Types Cheat Sheet, CHAR vs VARCHAR vs TEXT

Set Operations

SELECT a FROM t1
UNION            -- combines results, removes duplicates
UNION ALL        -- combines results, keeps duplicates (faster)
SELECT a FROM t2;

Query Optimization Quick Checklist

1. EXPLAIN ANALYZE to find the expensive step
2. Look for Seq Scan on large tables -> add an index
3. Check sargability -> avoid wrapping filtered/joined columns in functions
4. Avoid SELECT * -> select only needed columns
5. Use LIMIT / keyset pagination instead of large OFFSET
6. Run ANALYZE to keep planner statistics current

Full guides: SQL Query Optimization, SQL Indexes Explained

Common Query Patterns

-- Nth highest value (handles ties correctly)
SELECT col FROM (
  SELECT col, DENSE_RANK() OVER (ORDER BY col DESC) AS rnk FROM t
) x WHERE rnk = N;

-- Top N per group
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY grp ORDER BY val DESC) AS rn FROM t
) x WHERE rn <= N;

-- Find duplicates
SELECT col, COUNT(*) FROM t GROUP BY col HAVING COUNT(*) > 1;

-- Running total
SELECT *, SUM(val) OVER (ORDER BY date_col) AS running_total FROM t;

-- Consecutive streaks (gaps and islands)
SELECT *, date_col - ROW_NUMBER() OVER (ORDER BY date_col)::int AS grp FROM t;

Full guides: Nth Highest Salary, Top N Per Group, Find Duplicate Rows, Running Totals, Gaps and Islands

Transactions

BEGIN;
  -- statements
COMMIT;    -- makes changes permanent
ROLLBACK;  -- undoes all changes since BEGIN

-- Isolation levels (weakest to strongest):
-- Read Uncommitted -> Read Committed -> Repeatable Read -> Serializable

Full guide: Transactions and ACID Properties

Security: Always Parameterize

-- Never concatenate user input into SQL strings
-- Always use parameterized queries / prepared statements:
SELECT * FROM users WHERE username = $1;  -- value bound separately, never interpreted as SQL

Full guide: SQL Injection: How It Works and How to Prevent It

Frequently Asked Questions

Is this cheat sheet database-specific?

Most of this is standard ANSI SQL and works across PostgreSQL, MySQL, and SQL Server; the linked full guides call out database-specific differences (like date functions and pagination syntax) where they matter.

How should I actually use this page?

Bookmark it as a quick syntax refresher during practice or before an interview — but treat the linked full guides as the actual learning material, since real fluency comes from understanding the reasoning behind each pattern, not just recognizing the syntax.

Keep Practicing

A cheat sheet is a reference, not a substitute for hands-on practice. Work through real, graded problems with our SQL practice questions, or apply these patterns to full business scenarios in our case studies.

Back to All Articles