SQL Cheat Sheet

SQL cheat sheet with queries, joins, window functions, CTEs, upserts, indexes, transactions, and interview patterns — with PostgreSQL, MySQL, and SQL Server syntax differences noted.

SELECT & Filtering

Select columnsSELECT name, email FROM users;

Select allSELECT * FROM users;

Distinct valuesSELECT DISTINCT country FROM users;

Filter rowsSELECT * FROM users WHERE age >= 18 AND active = TRUE;

IN listWHERE status IN ('active', 'trial')

RangeWHERE price BETWEEN 10 AND 50Inclusive on both ends

Pattern matchWHERE email LIKE '%@gmail.com'% = any chars, _ = one char; ILIKE is case-insensitive in PostgreSQL

NULL checkWHERE deleted_at IS NULLNever use = NULL — it is always unknown

Column aliasSELECT price * qty AS total FROM order_items;

CASE expression

SELECT name,
  CASE WHEN score >= 90 THEN 'A'
       WHEN score >= 75 THEN 'B'
       ELSE 'C' END AS grade
FROM students;

Sorting & Limiting

Order bySELECT * FROM products ORDER BY price DESC, name ASC;

NULLs lastORDER BY updated_at DESC NULLS LASTPostgreSQL/Oracle; MySQL sorts NULLs first on ASC

Limit (PostgreSQL/MySQL)SELECT * FROM posts ORDER BY created_at DESC LIMIT 10;

PaginationLIMIT 10 OFFSET 20Rows 21–30; prefer keyset pagination for large tables

Top N (SQL Server)SELECT TOP 10 * FROM posts ORDER BY created_at DESC;

Standard FETCHORDER BY id OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;PostgreSQL, SQL Server 2012+, Oracle 12c+

Aggregates & GROUP BY

Count rowsSELECT COUNT(*) FROM orders;

Count non-null / distinctSELECT COUNT(coupon_code), COUNT(DISTINCT user_id) FROM orders;

Sum / avg / min / maxSELECT SUM(total), AVG(total), MIN(total), MAX(total) FROM orders;

Group by

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

Filter groups (HAVING)

SELECT user_id, SUM(total) AS spent
FROM orders
GROUP BY user_id
HAVING SUM(total) > 1000;

WHERE filters rows before grouping; HAVING filters groups after

Conditional countSELECT COUNT(*) FILTER (WHERE status = 'paid') FROM orders;PostgreSQL; portable form: SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END)

SQL Joins

Inner join

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

Only rows that match in both tables

Left join

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

All left rows; NULLs where no match

Right join

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

All right rows; usually rewritten as a LEFT JOIN

Full outer join

SELECT a.id, b.id
FROM a
FULL OUTER JOIN b ON a.key = b.key;

Not supported in MySQL — emulate with LEFT JOIN UNION RIGHT JOIN

Anti-join (no match)

SELECT u.*
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

Users with no orders

Self join

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

Cross join

SELECT s.size, c.color
FROM sizes s
CROSS JOIN colors c;

Cartesian product — every combination

Join with USINGSELECT * FROM orders JOIN users USING (user_id);When both columns share the same name

Subqueries & CTEs

Subquery in WHERE

SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);

IN subquery

SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total > 500);

EXISTS

SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

Safer than NOT IN when the subquery can return NULLs (use NOT EXISTS)

Derived table

SELECT AVG(cnt) FROM (
  SELECT user_id, COUNT(*) AS cnt FROM orders GROUP BY user_id
) t;

CTE (WITH)

WITH big_spenders AS (
  SELECT user_id, SUM(total) AS spent
  FROM orders GROUP BY user_id
)
SELECT * FROM big_spenders WHERE spent > 1000;

Recursive CTE

WITH RECURSIVE tree AS (
  SELECT id, parent_id, name FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.parent_id, c.name
  FROM categories c JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree;

SQL Server omits the RECURSIVE keyword

UNION vs UNION ALL

SELECT email FROM customers
UNION
SELECT email FROM leads;

UNION removes duplicates; UNION ALL keeps them and is faster

Window Functions

Row number

SELECT name, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
FROM employees;

RANK vs DENSE_RANK

SELECT name, salary,
  RANK()       OVER (ORDER BY salary DESC) AS rnk,
  DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk
FROM employees;

RANK leaves gaps after ties (1,1,3); DENSE_RANK does not (1,1,2)

Partition by

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

Aggregate per group without collapsing rows

Previous / next row

SELECT day, revenue,
  LAG(revenue)  OVER (ORDER BY day) AS prev_day,
  LEAD(revenue) OVER (ORDER BY day) AS next_day
FROM daily_sales;

Running total

SELECT day, revenue,
  SUM(revenue) OVER (ORDER BY day
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM daily_sales;

Moving averageAVG(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)7-day rolling average

First value in groupFIRST_VALUE(name) OVER (PARTITION BY dept ORDER BY salary DESC)

BucketsNTILE(4) OVER (ORDER BY score) AS quartile

Insert, Update, Delete & Upsert

Insert rowINSERT INTO users (name, email) VALUES ('Ada', '[email protected]');

Insert manyINSERT INTO tags (name) VALUES ('sql'), ('db'), ('postgres');

Insert from select

INSERT INTO archive (id, total)
SELECT id, total FROM orders WHERE created_at < '2024-01-01';

UpdateUPDATE users SET active = FALSE WHERE last_login < '2024-01-01';Always test the WHERE with a SELECT first

DeleteDELETE FROM sessions WHERE expires_at < NOW();

Upsert (PostgreSQL)

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

Upsert (MySQL)

INSERT INTO users (email, name) VALUES ('[email protected]', 'Ada')
ON DUPLICATE KEY UPDATE name = VALUES(name);

MySQL 8.0.20+ prefers a row alias: AS new ... SET name = new.name

Upsert (SQL Server)

MERGE INTO users AS t
USING (VALUES ('[email protected]', 'Ada')) AS s (email, name)
  ON t.email = s.email
WHEN MATCHED THEN UPDATE SET name = s.name
WHEN NOT MATCHED THEN INSERT (email, name) VALUES (s.email, s.name);

Return changed rowsUPDATE users SET active = FALSE WHERE id = 7 RETURNING *;PostgreSQL; SQL Server uses OUTPUT inserted.*

DDL: Tables, Constraints & Indexes

Create table

CREATE TABLE users (
  id         BIGSERIAL PRIMARY KEY,
  email      VARCHAR(255) NOT NULL UNIQUE,
  name       TEXT,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Auto-increment: BIGSERIAL / GENERATED ALWAYS AS IDENTITY (PostgreSQL), AUTO_INCREMENT (MySQL), IDENTITY(1,1) (SQL Server)

Foreign keyuser_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE

Check constraintALTER TABLE products ADD CONSTRAINT price_positive CHECK (price > 0);

Add columnALTER TABLE users ADD COLUMN phone VARCHAR(20);

Drop columnALTER TABLE users DROP COLUMN phone;

Rename columnALTER TABLE users RENAME COLUMN name TO full_name;SQL Server: EXEC sp_rename 'users.name', 'full_name', 'COLUMN';

Create indexCREATE INDEX idx_orders_user_id ON orders (user_id);

Composite / unique indexCREATE UNIQUE INDEX idx_users_tenant_email ON users (tenant_id, email);Column order matters — leftmost prefix is usable alone

Inspect query planEXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42;PostgreSQL/MySQL 8.0.18+; SQL Server uses SET STATISTICS PROFILE ON or the graphical plan

Drop / truncate

DROP TABLE IF EXISTS temp_data;
TRUNCATE TABLE logs;

Transactions

Basic transaction

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

MySQL: START TRANSACTION; SQL Server: BEGIN TRANSACTION

Roll backROLLBACK;

Savepoint

SAVEPOINT before_delete;
DELETE FROM items WHERE id = 9;
ROLLBACK TO SAVEPOINT before_delete;

SQL Server: SAVE TRANSACTION name / ROLLBACK TRANSACTION name

Isolation levelSET TRANSACTION ISOLATION LEVEL SERIALIZABLE;READ UNCOMMITTED < READ COMMITTED < REPEATABLE READ < SERIALIZABLE

Lock rows for updateSELECT * FROM accounts WHERE id = 1 FOR UPDATE;PostgreSQL/MySQL; SQL Server uses WITH (UPDLOCK, ROWLOCK)

String & Date Functions

ConcatenateSELECT first_name || ' ' || last_name FROM users;PostgreSQL/SQLite/Oracle; MySQL: CONCAT(a, ' ', b); SQL Server: a + ' ' + b or CONCAT()

Case & trimSELECT UPPER(name), LOWER(email), TRIM(title) FROM users;

Substring & lengthSELECT SUBSTRING(sku, 1, 3), LENGTH(name) FROM products;SQL Server uses LEN(); PostgreSQL LENGTH() counts characters

ReplaceSELECT REPLACE(phone, '-', '') FROM users;

Null fallbackSELECT COALESCE(nickname, name, 'Anonymous') FROM users;Returns the first non-NULL argument; NULLIF(a, b) returns NULL when a = b

Current date/timeSELECT CURRENT_DATE, CURRENT_TIMESTAMP;SQL Server: GETDATE() / SYSDATETIME(); MySQL also NOW()

Add intervalcreated_at + INTERVAL '7 days'PostgreSQL; MySQL: DATE_ADD(created_at, INTERVAL 7 DAY); SQL Server: DATEADD(day, 7, created_at)

Truncate to monthDATE_TRUNC('month', created_at)PostgreSQL; MySQL: DATE_FORMAT(created_at, '%Y-%m-01'); SQL Server 2022: DATETRUNC(month, created_at)

Extract partEXTRACT(YEAR FROM created_at)SQL Server: YEAR(created_at) or DATEPART(year, created_at)

Date differenceSELECT CURRENT_DATE - signup_date FROM users;PostgreSQL (days); MySQL: DATEDIFF(end, start); SQL Server: DATEDIFF(day, start, end)

SQL Interview Patterns

Nth highest salary

SELECT DISTINCT salary FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

2nd highest; OFFSET N-1 for the Nth

Nth highest (window)

SELECT salary FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) t
WHERE rnk = 3
LIMIT 1;

Find duplicates

SELECT email, COUNT(*)
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

Delete duplicates (keep lowest id)

DELETE FROM users
WHERE id IN (
  SELECT id FROM (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
    FROM users
  ) t WHERE rn > 1
);

Top N per group

SELECT * FROM (
  SELECT dept, name, salary,
    ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
  FROM employees
) t
WHERE rn <= 3;

Employees earning more than manager

SELECT e.name
FROM employees e
JOIN employees m ON m.id = e.manager_id
WHERE e.salary > m.salary;

Month-over-month change

SELECT month, revenue,
  revenue - LAG(revenue) OVER (ORDER BY month) AS change
FROM monthly_revenue;

Pivot with CASE

SELECT user_id,
  SUM(CASE WHEN status = 'paid' THEN total ELSE 0 END)     AS paid,
  SUM(CASE WHEN status = 'refunded' THEN total ELSE 0 END) AS refunded
FROM orders
GROUP BY user_id;

Keep the references coming

One email a week: new and trending developer tools, fresh comparisons, and what shipped. Unsubscribe in one click.