Cheatsheets
SQL
31 entries
SQL
SQL reference for querying, joining, aggregating, and managing relational databases
31 commands
CommandDescriptionExample
SELECT col1, col2 FROM table;
Select specific columns from a table
SELECT name, email FROM users;SELECT * FROM table LIMIT 10;
Select all columns, returning only the first 10 rows
SELECT * FROM orders LIMIT 10;SELECT DISTINCT col FROM table;
Return only unique values in a column
SELECT DISTINCT city FROM customers;SELECT col AS alias FROM table;
Rename a column in the result set using an alias
SELECT COUNT(*) AS total FROM orders;SELECT * FROM table ORDER BY col DESC;
Sort results by a column in descending order
SELECT * FROM posts ORDER BY created_at DESC;SELECT * FROM a INNER JOIN b ON a.id = b.a_id;
Return rows that have matching values in both tables
SELECT u.name, o.total FROM users u INNER JOIN orders o ON u.id = o.user_id;SELECT * FROM a LEFT JOIN b ON a.id = b.a_id;
Return all rows from the left table and matched rows from the right
SELECT u.name, o.id FROM users u LEFT JOIN orders o ON u.id = o.user_id;SELECT * FROM a FULL OUTER JOIN b ON a.id = b.a_id;
Return all rows from both tables, with NULLs where there is no match
SELECT * FROM a FULL OUTER JOIN b ON a.id = b.a_id;SELECT * FROM a CROSS JOIN b;
Return the Cartesian product of both tables (every combination)
SELECT * FROM sizes CROSS JOIN colors;SELECT a.*, b.name FROM a JOIN b USING (id);
Join tables on a column with the same name using USING shorthand
SELECT * FROM orders JOIN users USING (user_id);SELECT COUNT(*) FROM table;
Count all rows in a table
SELECT COUNT(*) FROM users WHERE active = true;SELECT col, SUM(amount) FROM table GROUP BY col;
Sum values grouped by a column
SELECT dept, SUM(salary) FROM employees GROUP BY dept;SELECT col, AVG(score) FROM table GROUP BY col HAVING AVG(score) > 80;
Filter groups using HAVING after GROUP BY
SELECT class, AVG(grade) FROM students GROUP BY class HAVING AVG(grade) > 80;SELECT MIN(col), MAX(col) FROM table;
Find the minimum and maximum value of a column
SELECT MIN(price), MAX(price) FROM products;SELECT col, COUNT(*) FROM table GROUP BY col ORDER BY COUNT(*) DESC;
Count occurrences of each value and sort by frequency
SELECT status, COUNT(*) FROM orders GROUP BY status ORDER BY COUNT(*) DESC;SELECT * FROM table WHERE col IN (SELECT col FROM other);
Filter rows using values returned by a subquery
SELECT * FROM users WHERE id IN (SELECT user_id FROM admins);SELECT * FROM (SELECT col, ROW_NUMBER() OVER () as rn FROM t) sub WHERE rn = 1;
Use a derived table (subquery in FROM) to filter on computed columns
SELECT * FROM (SELECT *, ROW_NUMBER() OVER () AS rn FROM t) sub WHERE rn <= 5;SELECT *, (SELECT AVG(price) FROM products) AS avg_price FROM products;
Correlated scalar subquery in the SELECT clause
SELECT *, (SELECT AVG(price) FROM products) AS avg FROM products;WITH cte AS (SELECT * FROM table WHERE active = 1) SELECT * FROM cte;
Common Table Expression (CTE) to simplify complex queries
WITH active AS (SELECT * FROM users WHERE active) SELECT * FROM active;CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE);
Create a new table with a primary key and constraints
CREATE TABLE posts (id SERIAL PRIMARY KEY, title TEXT NOT NULL);ALTER TABLE users ADD COLUMN age INT;
Add a new column to an existing table
ALTER TABLE users ADD COLUMN bio TEXT;DROP TABLE IF EXISTS table;
Delete a table if it exists, without raising an error
DROP TABLE IF EXISTS temp_data;CREATE INDEX idx_name ON table (col);
Create an index on a column to speed up queries
CREATE INDEX idx_email ON users (email);SELECT col, ROW_NUMBER() OVER (PARTITION BY group ORDER BY col) AS rn FROM table;
Assign a sequential row number within each partition
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC)SELECT col, RANK() OVER (ORDER BY score DESC) AS rank FROM table;
Rank rows by score, with gaps for ties
RANK() OVER (ORDER BY score DESC)SELECT col, SUM(amount) OVER (PARTITION BY user_id ORDER BY date) AS running_total FROM table;
Calculate a running total partitioned by user and ordered by date
SUM(amount) OVER (ORDER BY date) AS runningSELECT col, LAG(col, 1) OVER (ORDER BY date) AS previous FROM table;
Access the value from the previous row using LAG
LAG(price, 1) OVER (ORDER BY date)SELECT * FROM table WHERE col LIKE '%pattern%';
Filter rows where a column contains a substring
SELECT * FROM users WHERE name LIKE 'J%';SELECT * FROM table WHERE col BETWEEN 10 AND 50;
Filter rows where a value is within an inclusive range
SELECT * FROM orders WHERE total BETWEEN 100 AND 500;SELECT * FROM table WHERE col IS NULL;
Filter rows where a column value is NULL
SELECT * FROM users WHERE phone IS NULL;SELECT * FROM table WHERE col NOT IN ('a', 'b');
Exclude rows where a column matches any value in the list
SELECT * FROM users WHERE role NOT IN ('bot', 'test');