SQL Cheat Sheet

Querying basics

SELECT col1, col2 FROM users;                -- pick columns
SELECT * FROM users WHERE age >= 18;         -- filter rows
SELECT DISTINCT country FROM users;          -- unique values
SELECT * FROM users ORDER BY created_at DESC; -- sort
SELECT * FROM users LIMIT 10 OFFSET 20;      -- pagination (MySQL/PostgreSQL)
SELECT * FROM users
WHERE name LIKE 'A%'                          -- pattern match
  AND country IN ('DE', 'FR')
  AND deleted_at IS NULL;

Joins

SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON o.user_id = u.id;   -- only matching rows

LEFT JOIN  orders o ON o.user_id = u.id;   -- keep all users
RIGHT JOIN orders o ON o.user_id = u.id;   -- keep all orders
FULL JOIN  orders o ON o.user_id = u.id;   -- keep both (not in MySQL)

-- anti-join: users without any order
SELECT u.* FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

Aggregation & GROUP BY

SELECT country,
       COUNT(*)      AS users,
       AVG(age)      AS avg_age,
       MIN(created_at) AS first_signup
FROM users
GROUP BY country
HAVING COUNT(*) > 100        -- filter groups (WHERE filters rows)
ORDER BY users DESC;

Window functions

-- rank orders per user by amount
SELECT user_id, id, total,
       ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY total DESC) AS rn,
       SUM(total)   OVER (PARTITION BY user_id)                     AS user_total,
       LAG(total)   OVER (PARTITION BY user_id ORDER BY created_at) AS prev_total
FROM orders;

-- top 1 order per user
SELECT * FROM (
  SELECT o.*, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY total DESC) rn
  FROM orders o
) t WHERE rn = 1;

Subqueries & CTEs

-- common table expression
WITH big_spenders AS (
  SELECT user_id, SUM(total) AS spent
  FROM orders GROUP BY user_id HAVING SUM(total) > 1000
)
SELECT u.name, b.spent
FROM big_spenders b JOIN users u ON u.id = b.user_id;

-- correlated subquery
SELECT * FROM products p
WHERE price > (SELECT AVG(price) FROM products WHERE category = p.category);

Modifying data

INSERT INTO users (name, email) VALUES ('Alice', 'a@x.com');

UPDATE users SET status = 'active', updated_at = NOW()
WHERE id = 42;

DELETE FROM users WHERE deleted_at < NOW() - INTERVAL '90 days';

-- upsert
INSERT INTO kv (k, v) VALUES ('a', 1)
ON CONFLICT (k) DO UPDATE SET v = EXCLUDED.v;          -- PostgreSQL
INSERT INTO kv (k, v) VALUES ('a', 1)
ON DUPLICATE KEY UPDATE v = VALUES(v);                  -- MySQL

Tables & indexes (DDL)

CREATE TABLE orders (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, -- PostgreSQL
  user_id    BIGINT NOT NULL REFERENCES users(id),
  total      NUMERIC(10,2) NOT NULL DEFAULT 0,
  status     VARCHAR(20) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE orders ADD COLUMN note TEXT;
ALTER TABLE orders DROP COLUMN note;

CREATE INDEX idx_orders_user   ON orders (user_id);
CREATE UNIQUE INDEX uq_users_email ON users (email);
DROP TABLE IF EXISTS temp_data;

Transactions

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

Dialect differences