SQL Cheat Sheet

Query Basics #

SELECT name, city
FROM customers
WHERE city = 'Antwerp'
ORDER BY name ASC
LIMIT 10;                 -- SQL Server: SELECT TOP 10 ... / FETCH FIRST 10 ROWS ONLY

Clause	Example
All columns	`SELECT * FROM customers`
Unique values	`SELECT DISTINCT city FROM customers`
Rename a column	`SELECT total * 1.21 AS total_incl_vat`
Paging	`LIMIT 10 OFFSET 20`

Filtering (WHERE) #

Operator	Example
`=` `<>` `<` `>` `<=` `>=`	`total >= 100`
`AND` / `OR` / `NOT`	`city = 'Gent' AND total > 50`
`IN`	`city IN ('Antwerp', 'Gent')`
`BETWEEN`	`created_at BETWEEN '2026-01-01' AND '2026-06-30'`
`LIKE`	`name LIKE 'Mil%'` (`%` = any text, `_` = one char)
`IS NULL` / `IS NOT NULL`	`city IS NULL` (never `= NULL`)

Aggregates & Grouping #

SELECT city, COUNT(*) AS customers
FROM customers
GROUP BY city
HAVING COUNT(*) > 5          -- filter groups (WHERE filters rows)
ORDER BY customers DESC;

Function	Returns
`COUNT(*)` / `COUNT(col)`	Rows / non-null values
`SUM(col)`, `AVG(col)`	Total, average
`MIN(col)`, `MAX(col)`	Smallest, largest

Joins #

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

Join	Keeps
`INNER JOIN` (or `JOIN`)	Only matching rows in both tables
`LEFT JOIN`	All rows from the left table, NULL where there is no match
`RIGHT JOIN`	All rows from the right table
`FULL OUTER JOIN`	Everything (not in MySQL)
`CROSS JOIN`	Every combination

Customers without orders: LEFT JOIN orders o ON ... WHERE o.id IS NULL.

Subqueries & CTEs #

-- Subquery
SELECT * FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE total > 500);

-- CTE (WITH): readable step-by-step queries
WITH big_spenders AS (
  SELECT customer_id, SUM(total) AS spent
  FROM orders
  GROUP BY customer_id
  HAVING SUM(total) > 1000
)
SELECT c.name, b.spent
FROM big_spenders b
JOIN customers c ON c.id = b.customer_id;

Window Functions #

SELECT customer_id, total,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS nr,
       SUM(total)  OVER (PARTITION BY customer_id) AS customer_total,
       RANK()      OVER (ORDER BY total DESC) AS rank_by_total
FROM orders;

Changing Data #

INSERT INTO customers (name, city) VALUES ('Milan', 'Antwerp');
INSERT INTO customers (name, city) VALUES ('A', 'Gent'), ('B', 'Brugge');

UPDATE customers SET city = 'Mechelen' WHERE id = 3;   -- always use WHERE!
DELETE FROM customers WHERE id = 3;                    -- always use WHERE!

Tables & Schema #

CREATE TABLE orders (
  id          INTEGER PRIMARY KEY,          -- auto id: SERIAL / AUTO_INCREMENT / IDENTITY
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  total       DECIMAL(10,2) DEFAULT 0 CHECK (total >= 0),
  created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE customers ADD COLUMN email VARCHAR(255) UNIQUE;
ALTER TABLE customers DROP COLUMN email;
CREATE INDEX idx_orders_customer ON orders(customer_id);
DROP TABLE orders;

Useful Expressions #

Expression	Use
`CASE WHEN total > 100 THEN 'big' ELSE 'small' END`	If / else
`COALESCE(city, 'unknown')`	First non-null value
`NULLIF(a, 0)`	NULL when a = 0 (avoids division by zero)
`CAST(x AS INTEGER)`	Convert a type
`UPPER()`, `LOWER()`, `TRIM()`, `LENGTH()`	Text functions
`CURRENT_DATE`, `CURRENT_TIMESTAMP`	Now

Transactions #

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

Order of Execution #

FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT

That is why you can't use a SELECT alias inside WHERE, but you can in ORDER BY.

Tips #

Always use parameterized queries in code (WHERE id = $1 / ?). Never paste user input into SQL strings.
Run the SELECT version of an UPDATE/DELETE first to check which rows it will hit.