Language cheatsheet

SQL 2023 Cheatsheet

Everyday SQL for querying, joining, aggregating and changing data, with notes where PostgreSQL, MySQL and SQLite differ.

Querying

Select columns — Choose the columns to return. Avoid SELECT * in application code.
Filter rows — Return only rows that match a condition.
Sort and limit — Return the 10 newest rows. SQL Server uses TOP and Oracle uses FETCH FIRST.
Paginate results — Skip 20 rows and return the next 10. Large offsets get slow, so prefer keyset pagination.
Remove duplicates — Return each distinct value once.
Match text patterns — Find names that start with Ad. % matches any characters and _ matches one.
Match a list or a range — Use IN for a list of values and BETWEEN for an inclusive range.
Check for NULL — Compare with IS NULL, because = NULL is never true.
Branch inside a query — Compute a value conditionally.
Replace NULL with a default — Return the first value that isn't NULL.

Join two tables — Return rows that have a match in both tables.
Keep rows without a match — Return all users, with NULL order columns for users who have no orders.
Find rows with no match — Return users who have never placed an order.
Check that related rows exist — Return users that have at least one order. EXISTS stops at the first match.
Filter with a subquery — Use one query's result inside another.
Stack two result sets — Combine rows from two queries and remove duplicates. UNION ALL keeps duplicates and is faster.
Name a subquery with a CTE — Break a query into readable steps.

Count rows — Count all rows, or only non-NULL values of a column with COUNT(column).
Group and total — Compute a value per group.
Filter groups — Keep only groups that meet a condition. WHERE filters rows before grouping, HAVING filters groups after.
Number rows within groups — Number each user's orders from newest to oldest. Filter on this to get the latest per user.
Compute a running total — Add up amounts in date order.
Rank rows — Rank products by price, giving ties the same rank.
Compare with the previous row — Read the value of the previous row in the same ordering.

Insert a row — Add one row, naming the columns.
Insert from a query — Copy rows from a query into another table.
Update rows — Change columns in the rows that match.
Delete rows — Remove the rows that match.
Empty a table quickly — Remove all rows from a table at once.
Insert or update (PostgreSQL and SQLite) — Insert a row, or update it if the key already exists.
Insert or update (MySQL) — MySQL's version of an upsert.
Group statements into a transaction — Make several changes succeed or fail together. Use ROLLBACK instead of COMMIT to undo them.

Create a table — Define columns, a primary key, constraints and a foreign key.
Add a column — Add a column to an existing table.
Rename a column — Rename a column without changing its data.
Create an index — Speed up lookups and joins on a column.
Enforce uniqueness — Reject duplicate values in a column.
See how a query runs — Show the query plan to find out whether an index is used.
Drop a table — Delete a table and all its data.

C++ 23 cheatsheet — Compile and debug C++ with g++ and CMake, and use modern C++ (up to C++23): containers, algorithms, smart pointers, templates and threads.
C# 14 cheatsheet — Modern C# 14 syntax for strings, nullability, collections, LINQ, records, pattern matching, async code and generics.
CSS 3 cheatsheet — Layout with flexbox and grid, responsive design, custom properties, animations and modern selectors.
Go 1 cheatsheet — The go command for modules, building, testing and cross-compiling, plus the code patterns you write every day.
HTML 5 cheatsheet — HTML5 page structure, text, links, images, forms, tables and metadata, with accessible patterns.
Java, Maven and Gradle 25 cheatsheet — JDK commands to compile, run and diagnose Java programs, plus the Maven and Gradle commands for builds and tests.