SQL Cheat Sheet

Return specific columns. Avoid SELECT * in application code.

SELECT id, email FROM users;

Filter rows before grouping.

SELECT * FROM orders WHERE status = 'paid' AND total > 100;

Sort, then take the first rows. SQL Server: SELECT TOP 10 …; Oracle/standard: FETCH FIRST 10 ROWS ONLY.

SELECT * FROM posts ORDER BY published_at DESC LIMIT 10 OFFSET 20;

Remove duplicate result rows.

SELECT DISTINCT country FROM customers;

Set membership, inclusive ranges, pattern match (% any run, _ one char).

SELECT * FROM products
WHERE category IN ('tea', 'coffee')
  AND price BETWEEN 5 AND 20
  AND name LIKE 'Earl%';

NULL never equals anything, not even NULL. Use IS NULL / IS NOT NULL.

SELECT * FROM users WHERE deleted_at IS NULL;

Conditional values inside a query.

SELECT name,
  CASE WHEN total >= 1000 THEN 'gold'
       WHEN total >= 100 THEN 'silver'
       ELSE 'bronze' END AS tier
FROM customers;

First non-NULL value; handy for defaults.

SELECT COALESCE(nickname, first_name, 'Guest') AS display FROM users;

COUNT, SUM, AVG, MIN, MAX over all rows.

SELECT COUNT(*), SUM(total), AVG(total) FROM orders;

One result row per group. Every selected column must be grouped or aggregated.

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

Filter groups after aggregation (WHERE filters rows before).

SELECT customer_id, SUM(total) AS spent
FROM orders
GROUP BY customer_id
HAVING SUM(total) > 500;

Count unique values.

SELECT COUNT(DISTINCT customer_id) FROM orders;

Rows that match in both tables.

SELECT o.id, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id;

All rows from the left table; NULLs where nothing matches.

SELECT c.name, o.id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;

Rows with no match.

SELECT c.*
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

A table joined to itself via two aliases.

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

Stack results. UNION removes duplicates (slower); UNION ALL keeps them.

SELECT email FROM customers
UNION
SELECT email FROM newsletter_signups;

A query inside a query.

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

Name a subquery to make long queries readable.

WITH big_spenders AS (
  SELECT customer_id FROM orders
  GROUP BY customer_id HAVING SUM(total) > 1000
)
SELECT c.* FROM customers c
JOIN big_spenders b ON b.customer_id = c.id;

Walk a hierarchy such as an org chart or category tree.

WITH RECURSIVE chain AS (
  SELECT id, manager_id, name FROM employees WHERE id = 7
  UNION ALL
  SELECT e.id, e.manager_id, e.name
  FROM employees e JOIN chain ON e.id = chain.manager_id
)
SELECT * FROM chain;

Number rows within a partition without collapsing them. Great for “latest per group”.

SELECT * FROM (
  SELECT o.*, ROW_NUMBER() OVER (
    PARTITION BY customer_id ORDER BY placed_at DESC) AS rn
  FROM orders o
) t WHERE rn = 1;

Aggregates over an ordered frame.

SELECT placed_at, total,
  SUM(total) OVER (ORDER BY placed_at) AS running_total
FROM orders;

Look at the previous or next row.

SELECT day, revenue,
  revenue - LAG(revenue) OVER (ORDER BY day) AS change
FROM daily_revenue;

Add rows; list the columns explicitly.

INSERT INTO users (email, name)
VALUES ('ada@example.com', 'Ada'), ('alan@example.com', 'Alan');

Copy rows from a query.

INSERT INTO archived_orders
SELECT * FROM orders WHERE placed_at < '2025-01-01';

Always test the WHERE clause with a SELECT first.

UPDATE users SET plan = 'pro' WHERE id = 42;

Without WHERE, deletes every row.

DELETE FROM sessions WHERE expires_at < NOW();

Insert or update on conflict. PostgreSQL/SQLite shown; MySQL: ON DUPLICATE KEY UPDATE; SQL Server/Oracle: MERGE.

INSERT INTO stock (sku, qty) VALUES ('A1', 5)
ON CONFLICT (sku) DO UPDATE SET qty = stock.qty + EXCLUDED.qty;

All-or-nothing changes.

BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
UPDATE accounts SET balance = balance + 50 WHERE id = 2;
COMMIT;  -- or ROLLBACK;

Define columns, keys and constraints.

CREATE TABLE orders (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id BIGINT NOT NULL REFERENCES customers(id),
  total NUMERIC(12,2) NOT NULL CHECK (total >= 0),
  placed_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Add, change or drop columns and constraints.

ALTER TABLE users ADD COLUMN last_login TIMESTAMPTZ;
ALTER TABLE users DROP COLUMN legacy_flag;

Speed up lookups on columns you filter, join or sort by.

CREATE INDEX idx_orders_customer ON orders (customer_id);
CREATE UNIQUE INDEX idx_users_email ON users (email);

A saved query that behaves like a table.

CREATE VIEW paid_orders AS
SELECT * FROM orders WHERE status = 'paid';

DROP removes the table; TRUNCATE empties it fast.

TRUNCATE TABLE staging_events;
DROP TABLE IF EXISTS tmp_import;

Show the query plan. PostgreSQL: EXPLAIN ANALYZE runs it and shows real timings.

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;