SQL Cheatsheet

SQL operations, aggregations, joins, window functions and more

SQL Cheatsheet

92 snippets · click any snippet to copy

🏗️ DDL — Table Operations

Create table with common column types and constraints

Drop table if it exists (safe)

Delete all rows, keep structure (fast)

Add a column

Remove a column

Rename a column

Change column type (PostgreSQL)

Add unique constraint

Create index for faster lookups

Unique index

✏️ DML — Insert / Update / Delete

Insert single row

Insert multiple rows

Insert from another table

Update specific row

Update with date condition

Delete specific row

Delete by condition

Upsert — insert or update on conflict (PostgreSQL)

Upsert (MySQL)

🔍 SELECT & Filtering

Basic select with filter, sort, pagination

Unique values

Pattern match — starts with Al

Case-insensitive like (PostgreSQL)

Match any in list

Exclude subquery results

Range filter (inclusive)

Non-null filter

JSON field filter (PostgreSQL)

First non-null value

Return NULL if value equals given value

∑ Aggregations

Total row count

Count unique values

Sum, average, min, max

Group and count

Filter groups with HAVING

Group by month

Round average to 2 decimal places

Median (PostgreSQL)

Concatenate grouped strings (PostgreSQL)

Aggregate rows as JSON array (PostgreSQL)

🔗 JOINs

Inner join — only matching rows

Left join — all users, null if no orders

Right join — all orders, null if no user

Full outer join — all rows from both

Cartesian product of both tables

Self join — e.g. employee → manager

Rows in A not in B

🪟 Window Functions

Rank rows (gaps for ties)

Rank without gaps

Unique sequential row number

Rank within each department

Running total

Department average alongside each row

Previous row value

Next row value

First value in partition

Divide into N equal buckets

🔁 CTEs & Subqueries

Common table expression

Multiple CTEs

Recursive CTE — generate series

Subquery to get latest order per user

EXISTS — users who have orders

NOT EXISTS — users with no orders

⊕ Set Operations

Union — distinct rows from both

Union all — including duplicates

Rows in both A and B

Rows in A not in B

📅 Date & Time

Current timestamp with timezone

Current date only

Cast timestamp to date (PostgreSQL)

Truncate to week start

Extract year component

Add 7 days

Human-readable age/interval (PostgreSQL)

Days between dates (MySQL)

🔤 String Functions

Case and length

Remove leading/trailing spaces

Concatenate strings

Concatenate with || (PostgreSQL/SQLite)

Extract username from email

Remove characters

Strip non-digits (PostgreSQL)

Split and get nth part (PostgreSQL)

Left-pad to fixed width

Formatted string (PostgreSQL)

⚡ Transactions & Performance

Transaction — atomic transfer

Undo uncommitted changes

Partial rollback with savepoint

Show query plan

Run and show actual query plan

Reclaim space and update statistics (PostgreSQL)

Parameterized query (prevents SQL injection)

Loading tool…