Cheatsheet

SQL Cheat Sheet – Commands, Queries & Syntax Reference

Essential SQL commands reference: SELECT, JOIN, GROUP BY, subqueries, indexes, and window functions. Copy any query with one click.

Basics

SELECT * FROM users;Select all columns from table
SELECT id, name FROM users;Select specific columns
SELECT DISTINCT country FROM users;Unique values only
SELECT * FROM users ORDER BY name ASC;Order results ascending
SELECT * FROM users LIMIT 10 OFFSET 20;Pagination: 10 rows, skip 20
SELECT name AS full_name FROM users;Column alias

Filtering

WHERE age > 18 AND active = 1Multiple conditions
WHERE age BETWEEN 18 AND 65Inclusive range
WHERE country IN ('US', 'CA', 'GB')Match any value in list
WHERE name LIKE 'J%'Pattern match (% = wildcard)
WHERE email IS NULLFilter null values
WHERE NOT active = 1Negate condition

Aggregations

SELECT COUNT(*) FROM orders;Count all rows
SELECT SUM(amount), AVG(amount) FROM orders;Sum and average
SELECT MIN(price), MAX(price) FROM products;Min and max values
SELECT country, COUNT(*) FROM users GROUP BY country;Group and count
GROUP BY country HAVING COUNT(*) > 10Filter after grouping (HAVING vs WHERE)

JOINs

INNER JOIN orders ON users.id = orders.user_idRows with matching keys in both tables
LEFT JOIN orders ON users.id = orders.user_idAll users, with or without orders
RIGHT JOIN users ON orders.user_id = users.idAll orders, even without a matching user
FULL OUTER JOINAll rows from both tables (nulls where no match)
CROSS JOINCartesian product — every combination
FROM employees e JOIN employees m ON e.manager_id = m.idSelf-join (table joined to itself)

Subqueries

WHERE id IN (SELECT user_id FROM orders)Subquery in WHERE clause
FROM (SELECT * FROM orders WHERE total > 100) AS bigDerived table / inline view
WHERE EXISTS (SELECT 1 FROM orders WHERE user_id = u.id)EXISTS — check for row existence
WITH cte AS (SELECT ...) SELECT * FROM cteCommon Table Expression (CTE)

Data Modification

INSERT INTO users (name, email) VALUES ('Alice', 'a@b.com');Insert a row
UPDATE users SET active = 0 WHERE last_login < '2023-01-01';Update matching rows
DELETE FROM users WHERE id = 42;Delete matching rows
TRUNCATE TABLE logs;Delete all rows fast (no rollback)

Table Operations

CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(100));Create a table
ALTER TABLE users ADD COLUMN age INT;Add a column
ALTER TABLE users DROP COLUMN age;Remove a column
ALTER TABLE users MODIFY COLUMN name VARCHAR(200);Change column type
DROP TABLE IF EXISTS temp_data;Drop table if it exists
CREATE INDEX idx_email ON users(email);Create index on column

Window Functions

ROW_NUMBER() OVER (ORDER BY salary DESC)Unique sequential row number
RANK() OVER (ORDER BY salary DESC)Rank with gaps (ties share same rank)
DENSE_RANK() OVER (ORDER BY salary DESC)Rank without gaps
LAG(salary, 1) OVER (ORDER BY hire_date)Value from previous row
LEAD(salary, 1) OVER (ORDER BY hire_date)Value from next row
SUM(sales) OVER (PARTITION BY dept ORDER BY month)Running total per department

SQL vs NoSQL

SQL databases (PostgreSQL, MySQL, SQLite) enforce a schema and use relations between tables. They excel at structured data and complex queries. NoSQL databases (MongoDB, DynamoDB, Redis) trade schema flexibility for scale and speed on unstructured or high-volume data.

Performance Tips: When to Use Indexes

  • Index columns you filter on — any column in WHERE, JOIN ON, or ORDER BY is a candidate
  • Composite indexes — index (a, b) helps queries filtering on a or (a AND b), but not b alone
  • Avoid over-indexing — indexes speed reads but slow writes; don't index every column
  • Use EXPLAIN — run EXPLAIN SELECT ... to see if your query uses indexes or does a full scan
  • Partial indexes — index only a subset of rows, e.g. WHERE active = 1, for large tables with sparse queries