SQL Cheat Sheet

SELECT id, name, price
FROM   products
WHERE  price > 100
ORDER BY price DESC
LIMIT  20;

SELECT p.name        AS product,
       p.price * 1.2 AS price_incl_tax
FROM   products AS p;

SELECT DISTINCT country FROM customers;

WHERE age >= 18
  AND status <> 'banned'
  AND country IN ('US','CA','UK')
  AND price BETWEEN 10 AND 50

WHERE email LIKE '%@gmail.com'
  AND name ILIKE 'jo%'       -- case-insensitive (PostgreSQL)
  AND deleted_at IS NULL

SELECT * FROM orders o
WHERE EXISTS (
  SELECT 1 FROM refunds r WHERE r.order_id = o.id
);

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

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

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

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

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

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

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

-- same result
SELECT name FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

SELECT c.name, o.id FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
UNION ALL
SELECT c.name, o.id FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.id
WHERE c.id IS NULL;

-- USING: same column name on both sides
SELECT * FROM order_items JOIN shipments USING (order_id);

-- filter in ON: keeps unmatched left rows
LEFT JOIN orders o ON o.customer_id = c.id AND o.amount > 20
-- filter in WHERE: drops them (acts like INNER)
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.amount > 20

SELECT region, COUNT(*) AS n, SUM(amount) AS total
FROM   orders
GROUP BY region
HAVING SUM(amount) > 10000
ORDER BY total DESC;

COUNT(*)          COUNT(DISTINCT x)
SUM(x)  AVG(x)
MIN(x)  MAX(x)
STRING_AGG(name, ', ')   -- PostgreSQL, SQL Server
GROUP_CONCAT(name)       -- MySQL, SQLite

SELECT
  COUNT(*) FILTER (WHERE status='paid')  AS paid,
  SUM(CASE WHEN refunded THEN amount ELSE 0 END) AS refunded
FROM orders;

SELECT customer_id,
       COUNT(*)    AS orders,
       AVG(amount) AS avg_amount
FROM   orders
WHERE  status <> 'cancelled'   -- rows, before grouping
GROUP  BY customer_id
HAVING COUNT(*) >= 3;          -- groups, after

SELECT region, product, SUM(amount)
FROM sales
GROUP BY ROLLUP (region, product);
-- MySQL: GROUP BY region, product WITH ROLLUP

SELECT email FROM customers
UNION          -- removes duplicates
SELECT email FROM newsletter;

SELECT id FROM a UNION ALL SELECT id FROM b;  -- keeps them

-- in both
SELECT email FROM customers
INTERSECT
SELECT email FROM newsletter;

-- in the first, not the second
SELECT email FROM customers
EXCEPT
SELECT email FROM unsubscribed;

SELECT name, 'customer' AS kind FROM customers
UNION ALL
SELECT name, 'supplier' FROM suppliers
ORDER BY name;

SELECT * FROM posts
ORDER BY created_at DESC
LIMIT 20 OFFSET 40;

SELECT * FROM posts
ORDER BY created_at DESC
OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY;

SELECT * FROM posts
WHERE created_at < $last_seen
ORDER BY created_at DESC
LIMIT 20;

WITH top_customers AS (
  SELECT customer_id, SUM(amount) AS spent
  FROM orders GROUP BY customer_id
  HAVING SUM(amount) > 1000
)
SELECT c.name, t.spent
FROM top_customers t
JOIN customers c ON c.id = t.customer_id;

SELECT region, AVG(total) FROM (
  SELECT region, SUM(amount) AS total
  FROM orders GROUP BY region, month
) s
GROUP BY region;

WITH RECURSIVE tree AS (
  SELECT id, parent_id FROM cat WHERE id = 1
  UNION ALL
  SELECT c.id, c.parent_id
  FROM cat c JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree;

-- scalar: one value
SELECT name, price,
       price - (SELECT AVG(price) FROM products) AS vs_avg
FROM products;

-- correlated: runs per outer row
SELECT * FROM products p
WHERE price > (SELECT AVG(price) FROM products
               WHERE category = p.category);

WITH paid AS (
  SELECT * FROM orders WHERE status = 'paid'
), per_customer AS (
  SELECT customer_id, SUM(amount) AS total
  FROM paid GROUP BY customer_id
)
SELECT * FROM per_customer ORDER BY total DESC;

SELECT name, region, amount,
  ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn,
  RANK()       OVER (PARTITION BY region ORDER BY amount DESC) AS rnk
FROM sales;

SELECT day, amount,
  SUM(amount) OVER (ORDER BY day) AS running,
  LAG(amount) OVER (ORDER BY day) AS prev_day
FROM daily;

WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (
    PARTITION BY region ORDER BY amount DESC) AS rn
  FROM sales
)
SELECT * FROM ranked WHERE rn <= 3;

-- amounts: 90, 80, 80, 70
ROW_NUMBER()  -- 1, 2, 3, 4
RANK()        -- 1, 2, 2, 4
DENSE_RANK()  -- 1, 2, 2, 3
NTILE(4)      -- splits rows into 4 buckets

SELECT day, amount,
  AVG(amount) OVER (
    ORDER BY day
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS avg_7d,
  LEAD(amount) OVER (ORDER BY day)         AS next_day,
  FIRST_VALUE(amount) OVER (ORDER BY day)  AS first_day
FROM daily;

INSERT INTO users (name, email)
VALUES ('Ada', 'ada@x.com'),
       ('Bo',  'bo@x.com');

UPDATE orders SET status = 'shipped'
WHERE id = 42;

DELETE FROM carts
WHERE updated_at < NOW() - INTERVAL '30 days';

INSERT INTO counters (key, n) VALUES ('hits', 1)
ON CONFLICT (key) DO UPDATE
  SET n = counters.n + 1;

CREATE TABLE products (
  id      SERIAL PRIMARY KEY,
  name    VARCHAR(120) NOT NULL,
  price   NUMERIC(10,2) DEFAULT 0,
  created TIMESTAMPTZ DEFAULT NOW()
);

ALTER TABLE products ADD COLUMN sku TEXT;
ALTER TABLE products
  ADD CONSTRAINT uq_sku UNIQUE (sku);

CREATE INDEX idx_orders_cust ON orders (customer_id);
CREATE UNIQUE INDEX uq_email ON users (email);

FOREIGN KEY (customer_id) REFERENCES customers(id)

-- PostgreSQL 10+
id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY
-- MySQL / MariaDB
id INT AUTO_INCREMENT PRIMARY KEY
-- SQLite
id INTEGER PRIMARY KEY
-- SQL Server
id INT IDENTITY(1,1) PRIMARY KEY

CREATE TABLE order_items (
  order_id INT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  sku      TEXT NOT NULL,
  qty      INT  NOT NULL CHECK (qty > 0),
  PRIMARY KEY (order_id, sku)
);

CREATE INDEX idx_orders_cust_date
  ON orders (customer_id, created_at);

-- uses the index
WHERE customer_id = 7 AND created_at > '2026-01-01'
WHERE customer_id = 7
-- usually can't seek on it (leading column missing)
WHERE created_at > '2026-01-01'

-- PostgreSQL, SQLite, SQL Server
CREATE INDEX idx_orders_open
  ON orders (created_at)
  WHERE status = 'open';

-- PostgreSQL 11+, SQL Server
CREATE INDEX idx_orders_cust
  ON orders (customer_id) INCLUDE (amount);

EXPLAIN SELECT * FROM orders WHERE customer_id = 7;

EXPLAIN ANALYZE SELECT ...;   -- PostgreSQL, MySQL 8.0.18+
EXPLAIN QUERY PLAN SELECT ...; -- SQLite

DROP INDEX idx_orders_open;              -- PostgreSQL, SQLite, Oracle
DROP INDEX idx_orders_open ON orders;    -- MySQL, SQL Server

COALESCE(nickname, name, 'anon')
NULLIF(a, b)         -- NULL when a = b
CASE WHEN x > 0 THEN 'pos' ELSE 'neg' END

UPPER(s)  LOWER(s)  TRIM(s)  LENGTH(s)
CONCAT(a, ' ', b)
SUBSTRING(s FROM 1 FOR 3)
REPLACE(s, 'a', 'b')

NOW()   CURRENT_DATE
DATE_TRUNC('month', ts)          -- PostgreSQL
EXTRACT(YEAR FROM ts)
ts + INTERVAL '7 days'

BEGIN;                 -- MySQL: START TRANSACTION
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;                -- or ROLLBACK; to undo both

BEGIN;
INSERT INTO orders (id, customer_id) VALUES (105, 1);
SAVEPOINT before_items;
INSERT INTO order_items (order_id, sku) VALUES (105, 'X');
ROLLBACK TO SAVEPOINT before_items;  -- undo only the item
COMMIT;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- READ UNCOMMITTED | READ COMMITTED
-- REPEATABLE READ  | SERIALIZABLE

BEGIN;
ALTER TABLE users ADD COLUMN phone TEXT;
UPDATE users SET phone = '';
ROLLBACK;  -- the new column is gone too