Dev Tool

SQL Cheat Sheet — Query Reference for Developers

Name: SQL Cheat Sheet — PostgreSQL Quick Reference & Query Guide
Author: AllOmnitools Editorial Team

SELECT, JOINs, window functions, CTEs, indexes, JSON — with copy-paste examples

AllOmnitools Editorial Team

Search SQL Commands

SELECT — Querying Data

DQL SELECT — retrieve rows

SELECT * FROM users;

SELECT name, email FROM users;

SELECT DISTINCT country FROM users;

; terminates statement. * returns all columns. List specific columns for better performance.

DQL WHERE — filter rows

SELECT * FROM users WHERE age >= 18;

SELECT * FROM orders WHERE status = 'pending';

SELECT * FROM products WHERE price BETWEEN 10 AND 100;

SELECT * FROM logs WHERE created_at >= NOW() - INTERVAL '7 days';

Operators: =, != or <>, <, >, IN, LIKE (% wildcard), IS NULL, IS NOT NULL.

Sorting & Limiting

ORDER BY — sort results
SELECT * FROM users ORDER BY created_at DESC;
ORDER BY name ASC, age DESC (multi-column)

LIMIT / OFFSET — paginate
SELECT * FROM posts LIMIT 10;
LIMIT 20 OFFSET 40 -- page 3, 20 per page
LIMIT 20 ROWS OFFSET 40 -- ANSI SQL

JOINs — Combine Tables

Join INNER JOIN — matching rows only

SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

INNER JOIN returns only rows with matches in both tables. JOIN keyword alone = INNER JOIN.

Join LEFT JOIN — all from left, match from right

SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

LEFT JOIN returns all rows from left table. Unmatched right columns are NULL. Use to find orphans: add WHERE o.id IS NULL.

Join FULL OUTER JOIN — all rows from both

SELECT COALESCE(u.name, 'Unknown'), o.total
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

FULL OUTER JOIN returns all rows from both tables. Unmatched sides are NULL. COALESCE() replaces NULL with fallback.

Join CROSS JOIN — Cartesian product

SELECT c.name, p.name
FROM colors c CROSS JOIN products p;

-- Equivalent to:
SELECT * FROM table1, table2;

Produces m — n rows. Use sparingly — result set grows exponentially.

Other Join Types

SELF JOIN — join table to itself

SELECT e.name, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

NATURAL JOIN
— auto-join on same-named cols
SELECT * FROM table1 NATURAL JOIN table2;
Implicit, brittle. Prefer explicit ON.

Aggregation & Grouping

Agg GROUP BY — aggregate groups

SELECT country, COUNT(*) AS cnt
FROM users
GROUP BY country;

SELECT category, AVG(price) AS avg_price
FROM products
GROUP BY category
HAVING AVG(price) > 50;

WHERE filters rows before grouping. HAVING filters groups after aggregation. Non-aggregated columns must appear in GROUP BY.

Aggregate Functions

COUNT(*) — row count
COUNT(col) — non-null count
SUM(col) — total
AVG(col) — average

MIN(col) — minimum
MAX(col) — maximum
STRING_AGG(col, ', ') — PostgreSQL string concat
ARRAY_AGG(col) — collect into array

Window Functions (Advanced)

Window OVER() — windowed calculations

SELECT name, salary,
       RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

Window functions compute across a set of rows related to current row, but don't collapse rows (unlike GROUP BY).

Window PARTITION BY — group within window

SELECT name, department, salary,
       AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

PARTITION BY splits rows into partitions. Window functions restart for each partition.

Window Functions Reference

Ranking:
ROW_NUMBER() — unique sequential
RANK() — standard ranking (gaps)
DENSE_RANK() — no gaps
NTILE(4) — quartiles

Analytics:
LAG(col, 1) — previous row
LEAD(col, 1) — next row
FIRST_VALUE(col)
LAST_VALUE(col)

Aggregates as windows:
SUM() OVER ()
AVG() OVER ()
COUNT() OVER ()
MIN()/MAX() OVER ()

CTEs — WITH Queries

CTE WITH — Common Table Expression

WITH active_users AS (
  SELECT * FROM users WHERE active = true
), recent_orders AS (
  SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '30 days'
)
SELECT u.name, COUNT(o.id) AS order_count
FROM active_users u
LEFT JOIN recent_orders o ON u.id = o.user_id
GROUP BY u.name;

CTEs make complex queries readable. They're like temp views scoped to one statement. Can be recursive (for trees, graphs).

DML — Data Manipulation

DML INSERT — add rows

INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');

INSERT INTO users (name) VALUES ('Bob'), ('Charlie'), ('Dana'); -- multi-row

INSERT INTO users SELECT * FROM staging_users; -- from query

Omit column list only if providing values for ALL columns in exact table order. SERIAL columns (auto-increment) can be omitted.

DML UPDATE — modify rows

UPDATE users SET active = true WHERE last_login > NOW() - INTERVAL '30 days';

UPDATE products SET price = price * 1.1; -- increase all by 10%

UPDATE users SET meta = jsonb_set(meta, '{theme}', '"dark"') WHERE id = 1;

Always include a WHERE unless you truly mean to update every row. WHERE missing = full table update.

DML DELETE — remove rows

DELETE FROM users WHERE last_login < NOW() - INTERVAL '1 year';

DELETE FROM sessions WHERE user_id = 123;

DELETE FROM table_name without WHERE removes every row (but keeps table structure). Use TRUNCATE for faster full-table clear.

DML TRUNCATE — fast delete all rows

TRUNCATE TABLE logs;

TRUNCATE TABLE users, orders RESTART IDENTITY CASCADE;

TRUNCATE is DDL, not DML — it's fast (doesn't row-by-row delete), resets identity columns by default in PG. RESTART IDENTITY resets auto-increment. CASCADE truncates dependent tables.

DDL — Schema Objects

DDL CREATE TABLE — new table

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE,
  created_at TIMESTAMP DEFAULT NOW()
);

SERIAL auto-increment integer in PG. VARCHAR(n) variable string with limit. TIMESTAMP with time zone = TIMESTAMPTZ. DEFAULT values set on insert.

DDL ALTER TABLE — modify existing table

ALTER TABLE users ADD COLUMN age INT;

ALTER TABLE users DROP COLUMN middle_name;

ALTER TABLE users RENAME TO app_users;

ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id);

ALTER commands are transactional in PG — you can ROLLBACK an ALTER TABLE.

Indexes — Performance

Create & Manage Indexes

Basic index
CREATE INDEX idx_email ON users(email);

UNIQUE index
CREATE UNIQUE INDEX idx_users_email ON users(email);

Composite index
CREATE INDEX idx_last_first ON users(last_name, first_name);

Partial index (filtered)
CREATE INDEX idx_active_users ON users(email) WHERE active = true;

Drop index
DROP INDEX idx_email;

List indexes
SELECT * FROM pg_indexes WHERE tablename = 'users';

Indexes speed up queries but slow down writes. Use on WHERE, JOIN ON, and ORDER BY columns. Partial indexes are efficient for subset queries (e.g., only active users).

PostgreSQL Specific

Postgres JSONB — JSON with binary storage

-- Column type
CREATE TABLE users (
  meta JSONB
);

-- Insert JSON
INSERT INTO users (meta) VALUES ('{"theme": "dark", "notifications": true}');

-- Query jsonb field
SELECT meta->>'theme' AS theme FROM users;

-- Check contains
SELECT * FROM users WHERE meta @> '{"theme": "dark"}';

-- Update field
UPDATE users SET meta = jsonb_set(meta, '{theme}', '"light"') WHERE id = 1;

-> returns JSON object, ->> returns text. @> contains operator. jsonb_set() updates nested path. Use GIN indexes on JSONB for performance: CREATE INDEX idx_meta ON users USING GIN (meta);

Postgres ON CONFLICT — UPSERT

INSERT INTO users (id, name, email)
VALUES (1, 'Alice', 'alice@example.com')
ON CONFLICT (id) DO UPDATE
SET name = EXCLUDED.name, email = EXCLUDED.email;

-- Ignore duplicates
INSERT ... ON CONFLICT DO NOTHING;

EXCLUDED is the row that would have been inserted. ON CONFLICT (column) needs a UNIQUE or PRIMARY KEY constraint on that column.

Postgres RETURNING — get modified rows

INSERT INTO users (name) VALUES ('Bob') RETURNING id;

UPDATE users SET active = false WHERE last_login < '2020-01-01' RETURNING id, email;

DELETE FROM sessions WHERE expired = true RETURNING COUNT(*);

RETURNING works with INSERT, UPDATE, DELETE, and even MERGE. Saves a second query. Returns actual values (including defaults, triggers).

Recursive CTE — Hierarchical Data

WITH RECURSIVE subtree AS (
  SELECT id, parent_id, name FROM categories WHERE id = 5
  UNION ALL
  SELECT c.id, c.parent_id, c.name
  FROM categories c
  INNER JOIN subtree s ON c.parent_id = s.id
)
SELECT * FROM subtree;

Recursive CTEs traverse trees (org charts, categories). Anchor query + recursive UNION ALL. Use WHERE NOT EXISTS or max depth to prevent infinite loops.

PostgreSQL Index Types

Index Types Quick Ref

BTREE (default)
CREATE INDEX ... — normal
Great for range queries, equality

HASH
CREATE INDEX ... ON table USING HASH (col);
Only equality, very fast, no ordering

GIN (Generalized Inverted)
CREATE INDEX ... ON table USING GIN (jsonb_col);
JSONB, arrays, full-text tsvector

GiST / SP-GiST
CREATE INDEX ... ON table USING GiST (point_col);
Geospatial, range types, full-text

BRIN (Block Range)
CREATE INDEX ... ON table USING BRIN (created_at);
Large sorted tables, time-series data. Small, fast.

SQL quick reference — queries you write every week

Covers the SQL patterns that come up in real work: selecting and filtering, joins, aggregation, window functions, CTEs. PostgreSQL-flavored but most of this works in MySQL and SQLite too.

SELECT basics

SELECT * FROM users LIMIT 10 OFFSET 20; — paginate results
SELECT DISTINCT country FROM users; — unique values
SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '7 days'; — last 7 days
SELECT * FROM users WHERE email ILIKE '%@gmail.com'; — case-insensitive pattern (PG)
SELECT * FROM products WHERE tags @> ARRAY['sale']; — array contains (PG)

JOINs

INNER JOIN — only rows that match in both tables
LEFT JOIN — all rows from left, matching from right (nulls if no match)
RIGHT JOIN — all rows from right, matching from left
FULL OUTER JOIN — all rows from both, nulls where no match
CROSS JOIN — every combination (Cartesian product)

Aggregation

SELECT status, COUNT(*), SUM(amount) FROM orders GROUP BY status;
HAVING COUNT(*) > 10 — filter after grouping (WHERE filters before)
COUNT(DISTINCT user_id) — count unique values
ROUND(AVG(price), 2) — average rounded to 2 decimals

Window functions

ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) — row number within each user's orders
RANK() OVER (ORDER BY score DESC) — rank with gaps for ties
LAG(amount, 1) OVER (ORDER BY date) — previous row's value
SUM(amount) OVER (PARTITION BY user_id) — running total per user

CTEs (Common Table Expressions)

WITH active_users AS (SELECT * FROM users WHERE active = true) SELECT * FROM active_users;
Chain multiple CTEs: WITH a AS (...), b AS (...) SELECT ...
Recursive CTEs: WITH RECURSIVE tree AS (...) — for hierarchical data

Complete Developer Toolkit

This SQL cheat sheet pairs directly with our SQL formatter — look up the syntax here, then paste your query into the formatter to beautify and validate it before running it. When your queries return or store JSON data using JSON_EXTRACT, JSON_ARRAYAGG, or similar functions, our JSON formatter validates and prettifies that output. The regex tester is useful for building the patterns used in SQL REGEXP clauses in MySQL or PostgreSQL — test in the browser before adding to your query.

For safely passing SQL query parameters through web requests, our URL encoder percent-encodes values used in URL-based APIs, and our Base64 encoder handles binary column data. Our UUID generator creates standards-compliant UUIDs for use as primary key values in INSERT statements. The diff checker compares two SQL script versions to review schema migration changes before running them. Our API response simulator mocks the JSON responses from the APIs that query your database. For Python-based database code using SQLAlchemy or psycopg2, our Python cheat sheet covers the relevant syntax. The Linux terminal cheat sheet covers the mysql, psql, and sqlite3 CLI commands for running SQL from the terminal.

FAQ

What's the difference between WHERE and HAVING?

WHERE filters rows before aggregation. HAVING filters after. You can't use aggregate functions (COUNT, SUM, AVG) in a WHERE clause — use HAVING for that. Example: WHERE status = 'active' filters rows going into the GROUP BY. HAVING COUNT(*) > 5 filters the groups after counting.

When should I use a CTE vs a subquery?

How do I find duplicate rows in SQL?

Learn More

SQL — WikipediaSQL language standards, dialects, and history SQL Keywords — W3SchoolsComplete SQL keyword reference