SQL Cheatsheet

SELECT Basics

SELECT column1, column2 FROM table;
SELECT * FROM table;
SELECT DISTINCT column FROM table;
SELECT column AS alias FROM table;
SELECT COUNT(*) FROM table;

WHERE Clauses

Operator	Example
=, !=, <>	WHERE status = 'active'
<, >, <=, >=	WHERE age >= 18
AND, OR, NOT	WHERE age > 18 AND status = 'active'
IN	WHERE id IN (1, 2, 3)
NOT IN	WHERE id NOT IN (1, 2, 3)
BETWEEN	WHERE age BETWEEN 18 AND 65
LIKE	WHERE name LIKE 'J%'
ILIKE	WHERE name ILIKE 'j%' (case-insensitive, PostgreSQL)
IS NULL	WHERE email IS NULL
IS NOT NULL	WHERE email IS NOT NULL
EXISTS	WHERE EXISTS (SELECT 1 FROM ...)

LIKE Wildcards

Pattern	Description
%	Zero or more characters
_	Exactly one character
'A%'	Starts with A
'%z'	Ends with z
'%word%'	Contains “word”
'_r%'	Second letter is r

JOIN Types

-- INNER JOIN: rows matching in both tables
SELECT * FROM a INNER JOIN b ON a.id = b.a_id;

-- LEFT JOIN: all from left + matching right
SELECT * FROM a LEFT JOIN b ON a.id = b.a_id;

-- RIGHT JOIN: all from right + matching left
SELECT * FROM a RIGHT JOIN b ON a.id = b.a_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 a CROSS JOIN b;

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

GROUP BY & HAVING

SELECT department, COUNT(*), AVG(salary)
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

Aggregate Functions

Function	Description
COUNT(*)	Number of rows
COUNT(DISTINCT col)	Distinct values
SUM(col)	Sum of values
AVG(col)	Average
MIN(col)	Minimum
MAX(col)	Maximum
STRING_AGG(col, ',')	Concatenate (PostgreSQL)
GROUP_CONCAT(col)	Concatenate (MySQL)

ORDER BY & LIMIT

SELECT * FROM table ORDER BY col ASC;
SELECT * FROM table ORDER BY col DESC;
SELECT * FROM table ORDER BY col1, col2 DESC;
SELECT * FROM table LIMIT 10;
SELECT * FROM table LIMIT 10 OFFSET 20;

Window Functions

-- ROW_NUMBER: unique sequential number
SELECT *, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn FROM employees;

-- RANK: with gaps for ties
SELECT *, RANK() OVER (ORDER BY salary DESC) AS rnk FROM employees;

-- DENSE_RANK: no gaps
SELECT *, DENSE_RANK() OVER (ORDER BY salary DESC) AS dr FROM employees;

-- PARTITION BY: window per group
SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) FROM employees;

-- Running total
SELECT *, SUM(amount) OVER (ORDER BY date) AS running_total FROM orders;

-- LAG / LEAD: access previous/next row
SELECT *, LAG(salary, 1) OVER (ORDER BY id) AS prev_salary FROM employees;
SELECT *, LEAD(salary, 1) OVER (ORDER BY id) AS next_salary FROM employees;

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

Common Table Expressions (CTEs)

WITH active_users AS (
  SELECT * FROM users WHERE status = 'active'
),
user_orders AS (
  SELECT user_id, COUNT(*) AS order_count
  FROM orders GROUP BY user_id
)
SELECT a.name, u.order_count
FROM active_users a
JOIN user_orders u ON a.id = u.user_id;

Recursive CTE

WITH RECURSIVE hierarchy AS (
  SELECT id, name, manager_id, 1 AS level
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, h.level + 1
  FROM employees e JOIN hierarchy h ON e.manager_id = h.id
)
SELECT * FROM hierarchy;

INSERT / UPDATE / DELETE

INSERT INTO table (col1, col2) VALUES ('a', 'b');
INSERT INTO table (col1) SELECT col FROM other_table;
UPDATE table SET col = 'new' WHERE id = 1;
DELETE FROM table WHERE id = 1;

UPSERT

-- PostgreSQL
INSERT INTO table (id, name) VALUES (1, 'Alice')
ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;

-- MySQL
INSERT INTO table (id, name) VALUES (1, 'Alice')
ON DUPLICATE KEY UPDATE name = VALUES(name);

CASE Expression

SELECT name,
  CASE
    WHEN age < 18 THEN 'minor'
    WHEN age < 65 THEN 'adult'
    ELSE 'senior'
  END AS age_group
FROM users;