SQL Cheatsheet

Quick reference for SQL — SELECT, JOINs, aggregation, subqueries, window functions, DDL, and common functions. Applies to PostgreSQL, MySQL, SQLite, and SQL Server with minor syntax variations.

Basic Queries

-- Select all columns
SELECT * FROM users;

-- Select specific columns
SELECT id, name, email FROM users;

-- With alias
SELECT id, name AS full_name FROM users;

-- Filter with WHERE
SELECT * FROM users WHERE age > 18;

-- Sort results
SELECT * FROM users ORDER BY name ASC;
SELECT * FROM users ORDER BY created_at DESC;

-- Limit and offset (pagination)
SELECT * FROM posts ORDER BY id LIMIT 10 OFFSET 20;

-- Distinct values
SELECT DISTINCT country FROM users;

Filtering

-- AND / OR / NOT
SELECT * FROM users WHERE age > 18 AND country = 'US';
SELECT * FROM users WHERE role = 'admin' OR role = 'mod';
SELECT * FROM users WHERE NOT active = false;

-- IN — match any in list
SELECT * FROM products WHERE category IN ('books', 'music', 'games');

-- BETWEEN — inclusive range
SELECT * FROM orders WHERE total BETWEEN 10 AND 100;

-- LIKE — pattern matching (% = wildcard, _ = single char)
SELECT * FROM users WHERE name LIKE 'J%';     -- starts with J
SELECT * FROM users WHERE email LIKE '%@gmail.com';

-- IS NULL / IS NOT NULL
SELECT * FROM users WHERE phone IS NULL;
SELECT * FROM users WHERE deleted_at IS NOT NULL;

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

Aggregation

-- Aggregate functions
SELECT COUNT(*) FROM users;
SELECT COUNT(DISTINCT country) FROM users;
SELECT SUM(total) FROM orders;
SELECT AVG(price) FROM products;
SELECT MIN(price), MAX(price) FROM products;

-- GROUP BY
SELECT country, COUNT(*) AS user_count
FROM users
GROUP BY country
ORDER BY user_count DESC;

-- HAVING — filter on aggregated values (like WHERE but for groups)
SELECT category, AVG(price) AS avg_price
FROM products
GROUP BY category
HAVING AVG(price) > 50;

Function	Description
`COUNT(*)`	Count all rows including NULLs
`COUNT(col)`	Count non-NULL values in column
`SUM(col)`	Sum of numeric values
`AVG(col)`	Arithmetic mean, ignores NULLs
`MIN(col) / MAX(col)`	Minimum / maximum value
`GROUP_CONCAT / STRING_AGG`	Concatenate values within group (MySQL / PostgreSQL)

JOINs

-- INNER JOIN — only matching rows from both tables
SELECT orders.id, users.name
FROM orders
INNER JOIN users ON orders.user_id = users.id;

-- LEFT JOIN — all rows from left, matching from right (NULL if no match)
SELECT users.name, orders.id
FROM users
LEFT JOIN orders ON orders.user_id = users.id;

-- RIGHT JOIN — all rows from right, matching from left
SELECT users.name, orders.id
FROM users
RIGHT JOIN orders ON orders.user_id = users.id;

-- FULL OUTER JOIN — all rows from both tables
SELECT u.name, o.id
FROM users u
FULL OUTER JOIN orders o ON o.user_id = u.id;

-- CROSS JOIN — every combination (cartesian product)
SELECT colors.name, sizes.name FROM colors CROSS JOIN sizes;

-- Self join — join table with itself
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

Join Type	Returns
INNER JOIN	Only rows with matching keys in both tables
LEFT JOIN	All left rows + matching right rows (NULL on no match)
RIGHT JOIN	All right rows + matching left rows (NULL on no match)
FULL OUTER JOIN	All rows from both tables, NULLs where no match
CROSS JOIN	Every row from left × every row from right

Modifying Data

-- INSERT
INSERT INTO users (name, email, age) VALUES ('Alice', 'alice@example.com', 30);

-- INSERT multiple rows
INSERT INTO users (name, email) VALUES
  ('Bob', 'bob@example.com'),
  ('Carol', 'carol@example.com');

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

-- UPDATE multiple columns
UPDATE products SET price = price * 1.1, updated_at = NOW()
WHERE category = 'electronics';

-- DELETE
DELETE FROM users WHERE id = 42;

-- DELETE with JOIN (PostgreSQL)
DELETE FROM orders
USING users
WHERE orders.user_id = users.id AND users.deleted_at IS NOT NULL;

-- TRUNCATE — delete all rows, faster than DELETE
TRUNCATE TABLE audit_logs;

Table DDL

-- CREATE TABLE
CREATE TABLE users (
  id         SERIAL PRIMARY KEY,
  email      VARCHAR(255) NOT NULL UNIQUE,
  name       VARCHAR(100) NOT NULL,
  age        INTEGER CHECK (age >= 0),
  created_at TIMESTAMP DEFAULT NOW()
);

-- ALTER TABLE
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users ALTER COLUMN name TYPE TEXT;
ALTER TABLE users ADD CONSTRAINT uq_email UNIQUE (email);

-- CREATE INDEX
CREATE INDEX idx_users_email ON users(email);
CREATE UNIQUE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user ON orders(user_id, created_at DESC);

-- DROP TABLE
DROP TABLE IF EXISTS temp_data;

Subqueries & CTEs

-- Subquery in WHERE
SELECT * FROM orders
WHERE user_id IN (SELECT id FROM users WHERE country = 'US');

-- Subquery in FROM (derived table)
SELECT dept, avg_salary FROM (
  SELECT department AS dept, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
) dept_stats
WHERE avg_salary > 60000;

-- CTE (Common Table Expression)
WITH active_users AS (
  SELECT id, name FROM users WHERE active = true
),
user_orders AS (
  SELECT user_id, COUNT(*) AS order_count
  FROM orders
  GROUP BY user_id
)
SELECT au.name, COALESCE(uo.order_count, 0) AS orders
FROM active_users au
LEFT JOIN user_orders uo ON au.id = uo.user_id;

-- Recursive CTE (org chart)
WITH RECURSIVE org AS (
  SELECT id, name, manager_id, 0 AS depth FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, org.depth + 1
  FROM employees e JOIN org ON e.manager_id = org.id
)
SELECT * FROM org ORDER BY depth;

Window Functions

Window functions perform calculations over a set of rows related to the current row, without collapsing rows like GROUP BY does.

-- ROW_NUMBER — unique sequential row number
SELECT name, salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
FROM employees;

-- RANK / DENSE_RANK — rank with gaps / without gaps on ties
SELECT name, salary,
  RANK()       OVER (ORDER BY salary DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

-- LAG / LEAD — access previous / next row value
SELECT date, revenue,
  LAG(revenue, 1)  OVER (ORDER BY date) AS prev_revenue,
  LEAD(revenue, 1) OVER (ORDER BY date) AS next_revenue
FROM sales;

-- Running total with SUM OVER
SELECT date, amount,
  SUM(amount) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING) AS running_total
FROM transactions;

-- NTILE — divide rows into N buckets
SELECT name, salary,
  NTILE(4) OVER (ORDER BY salary) AS quartile
FROM employees;

Function	Description
`ROW_NUMBER()`	Unique 1-based row number per partition
`RANK()`	Rank with gaps for ties (1, 1, 3…)
`DENSE_RANK()`	Rank without gaps (1, 1, 2…)
`LAG(col, n)`	Value from n rows before current
`LEAD(col, n)`	Value from n rows after current
`PARTITION BY`	Resets the window per group
`OVER (...)`	Defines the window — required for all window functions

Common Functions

-- COALESCE — return first non-NULL value
SELECT COALESCE(phone, email, 'no contact') FROM users;

-- NULLIF — return NULL if two values are equal
SELECT NULLIF(discount, 0) FROM orders;  -- returns NULL instead of 0

-- CAST — type conversion
SELECT CAST(price AS INTEGER) FROM products;
SELECT price::INTEGER FROM products; -- PostgreSQL shorthand

-- CASE WHEN
SELECT name,
  CASE
    WHEN score >= 90 THEN 'A'
    WHEN score >= 80 THEN 'B'
    WHEN score >= 70 THEN 'C'
    ELSE 'F'
  END AS grade
FROM students;

-- String functions
SELECT CONCAT(first_name, ' ', last_name) FROM users;
SELECT LENGTH(description) FROM posts;
SELECT UPPER(country), LOWER(email) FROM users;
SELECT TRIM('  hello  ');
SELECT SUBSTRING(text, 1, 100) FROM articles;

-- Date/time functions
SELECT NOW();                            -- current timestamp
SELECT CURRENT_DATE;                     -- current date
SELECT DATE_TRUNC('month', created_at) FROM orders; -- PostgreSQL
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY);  -- MySQL
SELECT EXTRACT(YEAR FROM created_at) FROM orders;

Frequently Asked Questions

What is the difference between WHERE and HAVING in SQL?

WHERE filters rows before grouping — it operates on individual rows and cannot reference aggregate functions like COUNT or SUM. HAVING filters after grouping — it operates on group results and can use aggregate functions. For example, WHERE age > 18 filters before grouping, while HAVING COUNT(*) > 5 filters groups with more than 5 rows. The execution order is: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT.

What is an INNER JOIN vs LEFT JOIN?

An INNER JOIN returns only the rows where the join condition matches in both tables — rows with no match in either table are excluded. A LEFT JOIN returns all rows from the left (first) table regardless of whether there is a match in the right table. When there is no match, right-table columns are filled with NULL. Use LEFT JOIN when you need all rows from one table, including those without related records (e.g., users who have never placed an order).

What are window functions in SQL?

Window functions perform calculations across a set of rows related to the current row, defined by an OVER() clause. Unlike GROUP BY aggregate functions, they do not collapse rows — each row retains its identity while gaining access to computed values across the window. Common window functions include ROW_NUMBER(), RANK(), LAG(), LEAD(), and running aggregates like SUM() OVER(). They are useful for rankings, running totals, moving averages, and comparing each row to its neighbors.