SQL Cheat Sheet — Complete Reference (Postgres, MySQL, SQLite)

A practical SQL reference for daily querying. Examples work in PostgreSQL; flagged where MySQL or SQLite differ. Format messy queries with the SQL formatter.

SELECT basics

-- Get specific columns
SELECT id, email, created_at FROM users;

-- All columns
SELECT * FROM users;

-- Filter
SELECT * FROM users WHERE active = true AND age >= 18;

-- Distinct values
SELECT DISTINCT country FROM users;

-- Limit + offset (pagination)
SELECT * FROM users ORDER BY created_at DESC LIMIT 20 OFFSET 40;

-- Top N (SQL Server)
SELECT TOP 20 * FROM users ORDER BY created_at DESC;

-- Conditional column
SELECT name, CASE WHEN age < 18 THEN 'minor' ELSE 'adult' END AS category FROM users;

WHERE — filter operators

Operator	Example
= != <>	WHERE status = 'active'
> < >= <=	WHERE age >= 18
BETWEEN	WHERE age BETWEEN 18 AND 65
IN	WHERE country IN ('US','UK','CA')
NOT IN	WHERE id NOT IN (1,2,3)
LIKE	WHERE email LIKE '%@gmail.com'
ILIKE (Postgres)	Case-insensitive LIKE
IS NULL / IS NOT NULL	WHERE deleted_at IS NULL
AND / OR / NOT	Combine conditions
EXISTS	WHERE EXISTS (SELECT 1 FROM orders WHERE user_id = users.id)

JOINs

-- INNER JOIN: rows where match exists in both
SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON o.user_id = u.id;

-- LEFT JOIN: all left rows, NULLs for missing right
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name;

-- RIGHT JOIN: all right rows (rarely needed; flip the tables)
-- FULL OUTER JOIN: all rows from both, NULLs where no match (Postgres yes, MySQL no)
-- CROSS JOIN: cartesian product (every left × every right)

-- Self-join (find pairs)
SELECT a.name, b.name
FROM employees a
JOIN employees b ON b.manager_id = a.id;

Aggregation

SELECT
  country,
  COUNT(*) AS total_users,
  COUNT(DISTINCT email) AS unique_emails,
  AVG(age) AS avg_age,
  MIN(created_at) AS earliest,
  MAX(created_at) AS latest,
  SUM(balance) AS total_balance
FROM users
WHERE active = true
GROUP BY country
HAVING COUNT(*) > 100
ORDER BY total_users DESC;

HAVING vs WHERE: WHERE filters rows before aggregation; HAVING filters groups after.

Window functions

-- Rank within partition
SELECT name, department, salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;

-- Running total
SELECT date, amount,
  SUM(amount) OVER (ORDER BY date) AS running_total
FROM transactions;

-- Compare to previous row
SELECT date, price,
  LAG(price, 1) OVER (ORDER BY date) AS prev_price,
  price - LAG(price, 1) OVER (ORDER BY date) AS daily_change
FROM stock_prices;

-- N-day moving average
SELECT date, price,
  AVG(price) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7
FROM stock_prices;

CTEs (WITH clauses)

-- Readable alternative to subqueries
WITH active_users AS (
  SELECT * FROM users WHERE active = true AND deleted_at IS NULL
),
recent_orders AS (
  SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '30 days'
)
SELECT u.name, COUNT(o.id) AS recent_count
FROM active_users u
LEFT JOIN recent_orders o ON o.user_id = u.id
GROUP BY u.name;

-- Recursive CTE (tree traversal)
WITH RECURSIVE tree AS (
  SELECT id, name, parent_id, 0 AS depth FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.parent_id, t.depth + 1
  FROM categories c JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree ORDER BY depth, name;

INSERT, UPDATE, DELETE

-- Insert one row
INSERT INTO users (email, name) VALUES ('[email protected]', 'Alice');

-- Insert + return inserted row (Postgres)
INSERT INTO users (email, name) VALUES ('[email protected]', 'Alice') RETURNING id, created_at;

-- Insert multiple
INSERT INTO users (email, name) VALUES
  ('[email protected]', 'Alice'),
  ('[email protected]', 'Bob');

-- Insert from select
INSERT INTO archive_users SELECT * FROM users WHERE created_at < '2020-01-01';

-- Upsert (Postgres)
INSERT INTO users (email, name) VALUES ('[email protected]', 'Alice')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;

-- UPDATE
UPDATE users SET active = false WHERE last_login < NOW() - INTERVAL '1 year';

-- UPDATE with JOIN (Postgres)
UPDATE orders o SET status = 'expired'
FROM users u
WHERE o.user_id = u.id AND u.deleted_at IS NOT NULL;

-- DELETE
DELETE FROM users WHERE active = false AND last_login < NOW() - INTERVAL '5 years';

Schema (DDL)

-- Create table
CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,                    -- Postgres; MySQL: BIGINT AUTO_INCREMENT
  email TEXT UNIQUE NOT NULL,
  name TEXT NOT NULL,
  age INTEGER CHECK (age >= 0),
  active BOOLEAN DEFAULT true,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_active ON users(active) WHERE active = true;  -- partial (Postgres)
CREATE UNIQUE INDEX idx_users_email_lower ON users(LOWER(email));     -- expression

-- Foreign key
ALTER TABLE orders ADD COLUMN user_id BIGINT REFERENCES users(id) ON DELETE CASCADE;

-- Add column
ALTER TABLE users ADD COLUMN avatar_url TEXT;

-- Drop column
ALTER TABLE users DROP COLUMN avatar_url;

-- Rename
ALTER TABLE users RENAME COLUMN name TO full_name;

Performance — EXPLAIN

-- See the query plan
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

-- Run + measure
EXPLAIN ANALYZE SELECT * FROM users WHERE email = '[email protected]';

Look for Seq Scan (full-table read — usually means missing index) vs Index Scan (using an index — usually fast).

Date / time functions (Postgres)

Function	Returns
NOW() / CURRENT_TIMESTAMP	Current timestamp with timezone
CURRENT_DATE	Today's date
EXTRACT(YEAR FROM created_at)	Year as integer
DATE_TRUNC('month', created_at)	Round down to month
created_at + INTERVAL '7 days'	Add 7 days
AGE(created_at)	Time since (interval)

JSON in Postgres (JSONB)

-- Access field
SELECT data->'name' FROM users;          -- returns JSONB
SELECT data->>'name' FROM users;         -- returns text

-- Nested path
SELECT data#>>'{address,city}' FROM users;

-- Filter
SELECT * FROM users WHERE data->>'role' = 'admin';

-- Index for JSONB queries
CREATE INDEX idx_users_data ON users USING GIN (data);

Related tools

Format messy queries: SQL formatter. Inspect JSONB query results: JSON formatter. Convert query results to JSON for an API: CSV to JSON. Comparing schema migrations side by side: diff checker.

Database-specific edge cases (MySQL's LIMIT, SQL Server's TOP, SQLite's date functions) differ. The patterns here work in Postgres; for cross-database SQL, stick to ANSI standards: SELECT / JOIN / WHERE / GROUP BY / HAVING / UNION / CASE.