Essential SQL — SELECT, JOIN, GROUP BY, window functions, and more with copy-ready examples
SQL Cheatsheet is a quick-reference guide for SQL syntax and common queries. Find the right syntax for SELECT, INSERT, UPDATE, DELETE, JOINs, GROUP BY, subqueries, window functions, CTEs, and index management. Each entry includes syntax, description, and a concrete example. Filter by SQL dialect (MySQL, PostgreSQL, SQLite, SQL Server) to see dialect-specific syntax differences.
SQL (Structured Query Language) is the standard language for relational databases, standardized by ANSI/ISO (SQL-92, SQL:1999, SQL:2003, SQL:2016, SQL:2023). Core SQL is highly portable across databases. But each RDBMS adds its own extensions: PostgreSQL has JSONB, array types, and powerful window functions. MySQL has AUTO_INCREMENT (vs IDENTITY in SQL Server). SQLite has no separate BOOLEAN or DATE types (uses TEXT and INTEGER). These differences matter when migrating databases or writing portable queries.
Modern SQL features often overlooked: window functions (ROW_NUMBER, RANK, LAG, LEAD, SUM OVER) introduced in SQL:2003 for running totals and rankings. CTEs (WITH clauses) for readable recursive queries. FILTER clause for conditional aggregation. LATERAL joins (PostgreSQL) and CROSS APPLY (SQL Server) for row-by-row subqueries. JSON functions in modern MySQL, PostgreSQL, and SQLite for working with JSON columns.
Find rows matching criteria
Result: SELECT * FROM users WHERE status = 'active' AND created_at > '2026-01-01'
Count rows per group
Result: SELECT department, COUNT(*) as count FROM employees GROUP BY department HAVING count > 5
Running total (window function)
Result: SELECT date, amount, SUM(amount) OVER (ORDER BY date) as running_total FROM sales
What is the difference between WHERE and HAVING?
WHERE filters individual rows before grouping and aggregation. HAVING filters groups after aggregation. Rule: use WHERE for non-aggregated conditions (column values), use HAVING for aggregated conditions (COUNT, SUM, AVG). Example: SELECT department, AVG(salary) FROM employees WHERE hire_year > 2020 GROUP BY department HAVING AVG(salary) > 60000. The WHERE removes non-2020+ employees first, then groups, then HAVING removes low-salary departments.
What is the difference between INNER JOIN, LEFT JOIN, and FULL JOIN?
INNER JOIN: returns only rows that have matches in both tables. Excludes non-matches. LEFT JOIN (LEFT OUTER JOIN): returns all rows from the left table, and matched rows from the right table. Non-matching right rows get NULL. RIGHT JOIN: opposite of LEFT JOIN. FULL JOIN (FULL OUTER JOIN): all rows from both tables, NULLs where no match exists. Most common: INNER JOIN (only matches needed), LEFT JOIN (keep all left rows even if no match). MySQL doesn't support FULL OUTER JOIN natively — use UNION of LEFT and RIGHT JOINs.
What are window functions and when should I use them?
Window functions compute values across a 'window' of related rows without collapsing them into a single row (unlike GROUP BY). Syntax: function() OVER (PARTITION BY col ORDER BY col). Common window functions: ROW_NUMBER() — sequential row number within partition. RANK() — rank with gaps for ties. DENSE_RANK() — rank without gaps. LAG(col, n) — value from n rows before. LEAD(col, n) — value from n rows after. SUM/AVG/COUNT OVER — running totals. Use for: ranking items within categories, calculating moving averages, comparing a row to the previous/next row.
What is a CTE and how is it different from a subquery?
CTE (Common Table Expression) is a named temporary result set defined with WITH: WITH cte_name AS (SELECT ...) SELECT * FROM cte_name. Benefits over subqueries: readable (named, defined before main query), reusable (reference the same CTE multiple times), supports recursion (WITH RECURSIVE for hierarchical data like org charts or graph traversal). Subqueries are inline — harder to read when nested. CTEs are not always faster than subqueries (the optimizer may inline them anyway), but they're much more maintainable for complex queries.
How do I write an upsert (insert or update) in SQL?
PostgreSQL: 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: INSERT INTO users ... ON DUPLICATE KEY UPDATE name = VALUES(name), email = VALUES(email). SQLite: INSERT OR REPLACE INTO users ... (replaces the whole row on conflict). SQL Server: MERGE (complex syntax but powerful). The EXCLUDED table in PostgreSQL refers to the row that was attempted to be inserted.