SQL Cheatsheet: Queries Every Developer Needs

SQL is the lingua franca of relational databases. Whether you are using PostgreSQL, MySQL, SQLite, or SQL Server, the core syntax is largely compatible. This cheatsheet covers the queries and patterns you will write repeatedly as a backend developer.

SELECT Basics

SELECT * FROM users;
SELECT id, name, email FROM users;
SELECT name AS full_name, email FROM users;   /* alias */
SELECT DISTINCT country FROM users;           /* unique values */
SELECT COUNT(*) FROM users;                   /* aggregate */

Filtering with WHERE

SELECT * FROM orders WHERE status = 'active';
SELECT * FROM orders WHERE amount > 100 AND status = 'paid';
SELECT * FROM users  WHERE country IN ('US', 'CA', 'UK');
SELECT * FROM users  WHERE email LIKE '%@gmail.com';
SELECT * FROM users  WHERE name LIKE 'Al%';    /* starts with Al */
SELECT * FROM orders WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';
SELECT * FROM users  WHERE deleted_at IS NULL;
SELECT * FROM users  WHERE deleted_at IS NOT NULL;

Sorting and Limiting

SELECT * FROM products ORDER BY price ASC;
SELECT * FROM products ORDER BY price DESC, name ASC;  /* multi-column sort */

SELECT * FROM products LIMIT 10;              /* first 10 rows */
SELECT * FROM products LIMIT 10 OFFSET 20;   /* rows 21-30 (pagination) */

/* PostgreSQL style */
SELECT * FROM products FETCH FIRST 10 ROWS ONLY;

Aggregates and GROUP BY

SELECT status, COUNT(*) AS count
FROM orders
GROUP BY status;

SELECT user_id, SUM(amount) AS total_spent
FROM orders
GROUP BY user_id
HAVING SUM(amount) > 500;     /* HAVING filters groups, WHERE filters rows */

/* Common aggregate functions */
COUNT(*)    -- row count
COUNT(col)  -- non-null count
SUM(col)
AVG(col)
MIN(col)
MAX(col)
STRING_AGG(col, ', ')   -- PostgreSQL: join strings

JOINs

JOINs combine rows from multiple tables. Understanding which type to use is one of the most important SQL skills.

/* INNER JOIN — only rows with matching keys in both tables */
SELECT u.name, o.amount
FROM users u
INNER JOIN orders o ON o.user_id = u.id;

/* LEFT JOIN — all users, even those with no orders */
SELECT u.name, o.amount
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

/* RIGHT JOIN — all orders, even without a matching user */
SELECT u.name, o.amount
FROM users u
RIGHT JOIN orders o ON o.user_id = u.id;

/* FULL OUTER JOIN — all rows from both tables */
SELECT u.name, o.amount
FROM users u
FULL OUTER JOIN orders o ON o.user_id = u.id;

Subqueries

/* Scalar subquery — returns one value */
SELECT name, (SELECT COUNT(*) FROM orders WHERE user_id = u.id) AS order_count
FROM users u;

/* IN subquery */
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);

/* EXISTS — often faster than IN for large tables */
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

Window Functions

Window functions compute values across a set of rows related to the current row — without collapsing them into groups like GROUP BY.

/* ROW_NUMBER — unique sequential number per partition */
SELECT
  name,
  country,
  ROW_NUMBER() OVER (PARTITION BY country ORDER BY created_at) AS row_num
FROM users;

/* RANK and DENSE_RANK */
SELECT
  product,
  sales,
  RANK() OVER (ORDER BY sales DESC) AS rank
FROM sales_data;

/* Running total */
SELECT
  date,
  amount,
  SUM(amount) OVER (ORDER BY date) AS running_total
FROM daily_sales;

/* LAG / LEAD — access previous/next row value */
SELECT date, amount,
  LAG(amount)  OVER (ORDER BY date) AS prev_day,
  LEAD(amount) OVER (ORDER BY date) AS next_day
FROM daily_sales;

Modifying Data

/* INSERT */
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com'), ('Charlie', 'c@example.com');

/* UPDATE */
UPDATE users SET status = 'inactive' WHERE last_login < '2023-01-01';

/* DELETE */
DELETE FROM sessions WHERE expires_at < NOW();

/* UPSERT (PostgreSQL) */
INSERT INTO settings (user_id, theme)
VALUES (1, 'dark')
ON CONFLICT (user_id) DO UPDATE SET theme = EXCLUDED.theme;

Indexes

Indexes speed up reads at the cost of slower writes and more disk space. Always index foreign keys and columns used in WHERE clauses on large tables.

CREATE INDEX idx_users_email ON users (email);
CREATE UNIQUE INDEX idx_users_email_unique ON users (email);
CREATE INDEX idx_orders_user_created ON orders (user_id, created_at);  /* composite */

/* See which index a query uses */
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1;

CTEs (WITH Clauses)

Common Table Expressions make complex queries readable by letting you name intermediate results.

WITH active_users AS (
  SELECT id, name FROM users WHERE status = 'active'
),
user_totals AS (
  SELECT user_id, SUM(amount) AS total
  FROM orders
  GROUP BY user_id
)
SELECT u.name, ut.total
FROM active_users u
JOIN user_totals ut ON ut.user_id = u.id
ORDER BY ut.total DESC;

Use the JSON Tools on io9.me to format query results that come back as JSON from your database driver.