SQL Quick Reference - ZeroKit.dev

Select Queries

SELECT col1, col2 FROM table_name;
SELECT DISTINCT col FROM table_name;
SELECT * FROM users WHERE age > 18;
SELECT * FROM users ORDER BY name ASC;
SELECT * FROM users LIMIT 10 OFFSET 20;
SELECT col AS alias_name FROM t;

Filtering & Conditions

WHERE col = 'value'
WHERE col IN ('a', 'b', 'c')
WHERE col BETWEEN 10 AND 50
WHERE col LIKE '%pattern%'
WHERE col IS NULL / IS NOT NULL
WHERE a = 1 AND (b = 2 OR c = 3)
WHERE col REGEXP '^[A-Z]'  -- MySQL
WHERE col ~ '^[A-Z]'       -- PostgreSQL

Insert

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

-- Multiple rows
INSERT INTO users (name, email) VALUES
  ('Alice', 'a@b.com'),
  ('Bob', 'b@b.com');

-- Insert from select
INSERT INTO archive SELECT * FROM users
WHERE created < '2024-01-01';

-- Upsert (PostgreSQL)
INSERT INTO t (id, val) VALUES (1, 'x')
ON CONFLICT (id) DO UPDATE SET val = 'x';

-- Upsert (MySQL)
INSERT INTO t (id, val) VALUES (1, 'x')
ON DUPLICATE KEY UPDATE val = 'x';

Update

UPDATE users SET email = 'new@b.com'
WHERE id = 1;

UPDATE users SET
  name = 'Alice',
  age = age + 1
WHERE active = TRUE;

Delete

DELETE FROM users WHERE id = 1;
DELETE FROM users WHERE active = FALSE;
TRUNCATE TABLE users;  -- fast, no WHERE

Joins

-- INNER JOIN (matching rows only)
SELECT * FROM orders o
INNER JOIN users u ON o.user_id = u.id;

-- LEFT JOIN (all left + matching right)
SELECT * FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- RIGHT JOIN (all right + matching left)
SELECT * FROM orders o
RIGHT JOIN users u ON o.user_id = u.id;

-- FULL OUTER JOIN (all from both)
SELECT * FROM a
FULL OUTER JOIN b ON a.id = b.a_id;

-- CROSS JOIN (cartesian product)
SELECT * FROM colors CROSS JOIN sizes;

-- Self join
SELECT e.name, m.name AS manager
FROM employees e
JOIN employees m ON e.mgr_id = m.id;

Aggregations

SELECT COUNT(*) FROM users;
SELECT COUNT(DISTINCT country) FROM users;
SELECT SUM(amount), AVG(amount),
       MIN(amount), MAX(amount)
FROM orders;

SELECT country, COUNT(*) AS cnt
FROM users
GROUP BY country
HAVING COUNT(*) > 5
ORDER BY cnt DESC;

Subqueries

-- Scalar subquery
SELECT * FROM users
WHERE age > (SELECT AVG(age) FROM users);

-- IN subquery
SELECT * FROM users
WHERE id IN (
  SELECT user_id FROM orders
  WHERE amount > 100
);

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

-- Derived table
SELECT * FROM (
  SELECT dept, AVG(sal) AS avg_sal
  FROM employees GROUP BY dept
) AS t WHERE avg_sal > 50000;

Window Functions

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

-- Ranking
RANK() OVER (...)       -- gaps on ties
DENSE_RANK() OVER (...) -- no gaps
NTILE(4) OVER (...)     -- quartiles

-- Aggregates as windows
SUM(amount) OVER (ORDER BY date) -- running total
AVG(amount) OVER (
  ORDER BY date ROWS BETWEEN
  2 PRECEDING AND CURRENT ROW
) -- 3-row moving average

-- Offset functions
LAG(col, 1) OVER (ORDER BY date)
LEAD(col, 1) OVER (ORDER BY date)
FIRST_VALUE(col) OVER (...)

Common Table Expressions (CTEs)

WITH active_users AS (
  SELECT * FROM users WHERE active = TRUE
)
SELECT * FROM active_users WHERE age > 25;

-- Recursive CTE
WITH RECURSIVE tree AS (
  SELECT id, name, parent_id, 0 AS depth
  FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.parent_id, t.depth+1
  FROM categories c JOIN tree t
  ON c.parent_id = t.id
)
SELECT * FROM tree;

Table Operations (DDL)

CREATE TABLE users (
  id SERIAL PRIMARY KEY,   -- PG
  id INT AUTO_INCREMENT,    -- MySQL
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE,
  created TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE users ADD COLUMN age INT;
ALTER TABLE users DROP COLUMN age;
ALTER TABLE users RENAME COLUMN a TO b;
DROP TABLE IF EXISTS users;

Indexes

CREATE INDEX idx_email ON users(email);
CREATE UNIQUE INDEX idx_u ON users(email);
DROP INDEX idx_email;              -- PG
DROP INDEX idx_email ON users;     -- MySQL

MySQL vs PostgreSQL

-- Auto-increment
MySQL:  INT AUTO_INCREMENT
PG:     SERIAL or GENERATED ALWAYS AS IDENTITY

-- String concat
MySQL:  CONCAT(a, b)
PG:     a || b

-- Current time
MySQL:  NOW()
PG:     NOW() or CURRENT_TIMESTAMP

-- LIMIT
MySQL:  LIMIT 10, 20  -- offset, limit
PG:     LIMIT 20 OFFSET 10

-- Boolean
MySQL:  TINYINT(1)
PG:     BOOLEAN

-- JSON
Both:   col->>'key'  -- extract as text