SQL cheat sheet
96 one-line examples for SQL. Open it while you code, or print it and keep it near the keyboard. Free, with no signup and no ads.
Select and Filter Rows
| Code | What it does |
|---|---|
SELECT * FROM users; | Return all rows and columns from the table. |
SELECT name, email FROM users; | Return only the named columns. |
SELECT * FROM users WHERE age > 18; | Return rows that match the condition. |
SELECT * FROM users WHERE age >= 18; | Use >= for greater than or equal. |
SELECT * FROM users WHERE city = 'Oslo'; | Match text with single quotes. |
SELECT * FROM users WHERE age <> 30; | Return rows where age is not 30. |
SELECT * FROM users WHERE age BETWEEN 18 AND 65; | Match a range with inclusive limits. |
SELECT * FROM users WHERE city IN ('Oslo','Rome'); | Match any value in the list. |
SELECT * FROM users WHERE email IS NULL; | Find rows with no value. |
SELECT * FROM users WHERE name LIKE 'A%'; | Match names that start with A. |
SELECT * FROM users WHERE NOT active; | Return rows where the condition is false. |
SELECT * FROM users WHERE age > 18 AND active; | Combine two conditions with AND. |
Sort and Limit Results
| Code | What it does |
|---|---|
SELECT * FROM users ORDER BY name; | Sort rows by name in ascending order. |
SELECT * FROM users ORDER BY name DESC; | Sort rows by name in descending order. |
SELECT * FROM users ORDER BY age, name; | Sort by age first, then by name. |
SELECT * FROM users LIMIT 10; | Return only the first ten rows. |
SELECT * FROM users LIMIT 10 OFFSET 20; | Skip twenty rows, then return ten. |
SELECT DISTINCT city FROM users; | Return each city only once. |
SELECT COUNT(*) FROM users; | Count all rows in the table. |
SELECT COUNT(DISTINCT city) FROM users; | Count unique city values. |
SELECT * FROM users ORDER BY id LIMIT 1; | Return the row with the lowest id. |
SELECT * FROM users ORDER BY id DESC LIMIT 1; | Return the row with the highest id. |
SELECT * FROM users LIMIT 5 OFFSET 0; | Return the first page of five rows. |
SELECT * FROM users ORDER BY name LIMIT 3; | Return the first three names in order. |
Insert Rows
| Code | What it does |
|---|---|
INSERT INTO users (name) VALUES ('Ana'); | Add one row with a name value. |
INSERT INTO users (name, age) VALUES ('Ana', 30); | Add one row with two values. |
INSERT INTO users (name) VALUES ('Ana'), ('Bo'); | Add two rows in one statement. |
INSERT INTO users DEFAULT VALUES; | Add one row with default values. |
INSERT INTO users (name) SELECT name FROM old; | Copy rows from another table. |
INSERT INTO users (id, name) VALUES (1, 'Ana'); | Set the id value by hand. |
INSERT INTO users (name) VALUES (NULL); | Insert a null value into a column. |
INSERT INTO users (age) VALUES (25); | Insert a value into one column only. |
INSERT INTO users (name) VALUES ('O''Neil'); | Escape a single quote with two quotes. |
INSERT INTO users (name) VALUES ('Ana') RETURNING id; | Return the new id after insert. |
INSERT INTO users (name) VALUES ('Ana') ON CONFLICT DO NOTHING; | Skip the row if a conflict occurs. |
INSERT INTO users (name) VALUES ('Ana') RETURNING *; | Return the full new row. |
Update Rows
| Code | What it does |
|---|---|
UPDATE users SET age = 31 WHERE id = 1; | Change one row that matches the id. |
UPDATE users SET active = true; | Change a column in all rows. |
UPDATE users SET age = age + 1; | Add one to each age value. |
UPDATE users SET name = 'Ana', age = 30 WHERE id = 1; | Change two columns in one row. |
UPDATE users SET city = NULL WHERE id = 2; | Set a column value to null. |
UPDATE users SET name = 'Bo' WHERE name = 'Bob'; | Rename rows that match the old name. |
UPDATE users SET age = 18 WHERE age < 18; | Fix rows that fail a rule. |
UPDATE users SET active = false WHERE id IN (2, 3); | Change rows for a list of ids. |
UPDATE users SET age = 0 WHERE age IS NULL; | Replace null values with zero. |
UPDATE users SET name = 'X' WHERE id = 1 RETURNING *; | Return the changed row after update. |
UPDATE users SET age = 40 WHERE id = 1 AND active; | Update only when two conditions match. |
UPDATE users SET age = age * 2 WHERE age < 50; | Double the age for young rows. |
Delete Rows
| Code | What it does |
|---|---|
DELETE FROM users WHERE id = 1; | Delete one row that matches the id. |
DELETE FROM users WHERE age < 18; | Delete all rows that match the condition. |
DELETE FROM users; | Delete all rows but keep the table. |
DELETE FROM users WHERE id IN (1, 2); | Delete rows for a list of ids. |
DELETE FROM users WHERE email IS NULL; | Delete rows with no email value. |
DELETE FROM users WHERE name LIKE 'Test%'; | Delete rows with names that start with Test. |
DELETE FROM users WHERE id = 1 RETURNING *; | Return the deleted row after delete. |
DELETE FROM users WHERE age BETWEEN 1 AND 5; | Delete rows inside a range. |
DELETE FROM users WHERE city = 'Rome'; | Delete rows for one city. |
DELETE FROM users WHERE NOT active; | Delete rows where the condition is false. |
DELETE FROM users WHERE id = 1 AND age > 60; | Delete only when two conditions match. |
DELETE FROM users WHERE id = 99; | Delete nothing when no row matches. |
Join Tables
| Code | What it does |
|---|---|
SELECT * FROM users u JOIN orders o ON u.id = o.user_id; | Return rows that match in both tables. |
SELECT * FROM users u LEFT JOIN orders o ON u.id = o.user_id; | Keep all users, even without orders. |
SELECT * FROM orders o RIGHT JOIN users u ON u.id = o.user_id; | Keep all users from the right table. |
SELECT * FROM users u FULL JOIN orders o ON u.id = o.user_id; | Keep rows from both tables. |
SELECT u.name, o.total FROM users u JOIN orders o ON u.id = o.user_id; | Pick columns from two joined tables. |
SELECT * FROM a JOIN b USING (id); | Join on a column with the same name. |
SELECT * FROM a CROSS JOIN b; | Return every pair of rows from both tables. |
SELECT * FROM a JOIN b ON a.id = b.a_id JOIN c ON b.id = c.b_id; | Join three tables in one query. |
SELECT * FROM users u LEFT JOIN orders o ON u.id = o.user_id WHERE o.id IS NULL; | Find users who have no orders. |
SELECT * FROM a JOIN b ON a.id = b.a_id AND b.active; | Add an extra condition to the join. |
SELECT * FROM users u JOIN orders o ON u.id = o.user_id ORDER BY o.total; | Sort the joined result by order total. |
SELECT * FROM a LEFT JOIN b ON a.id = b.a_id LIMIT 5; | Limit rows after a left join. |
Group and Aggregate
| Code | What it does |
|---|---|
SELECT city, COUNT(*) FROM users GROUP BY city; | Count rows for each city. |
SELECT city, AVG(age) FROM users GROUP BY city; | Find the average age for each city. |
SELECT SUM(total) FROM orders; | Add all values in one column. |
SELECT MIN(age), MAX(age) FROM users; | Return the smallest and largest age. |
SELECT city, COUNT(*) FROM users GROUP BY city HAVING COUNT(*) > 5; | Keep groups with more than five rows. |
SELECT city, COUNT(*) AS n FROM users GROUP BY city ORDER BY n DESC; | Sort groups by their count. |
SELECT COUNT(*) FROM users WHERE active; | Count rows that match a condition. |
SELECT AVG(age) FROM users WHERE age IS NOT NULL; | Average age and skip null values. |
SELECT city, SUM(total) FROM users u JOIN orders o ON u.id = o.user_id GROUP BY city; | Sum order totals for each city. |
SELECT ROUND(AVG(age), 2) FROM users; | Round the average to two places. |
SELECT city, COUNT(*) FROM users GROUP BY city HAVING AVG(age) > 30; | Keep groups with an average age above 30. |
SELECT COUNT(*) FILTER (WHERE active) FROM users; | Count only the active rows. |
Tables, Keys, and Indexes
| Code | What it does |
|---|---|
CREATE TABLE users (id INT PRIMARY KEY, name TEXT); | Create a table with a primary key. |
CREATE TABLE users (id INT, name TEXT NOT NULL); | Create a table with a required column. |
CREATE TABLE orders (id INT, user_id INT REFERENCES users(id)); | Create a table with a foreign key. |
CREATE TABLE users (id INT, age INT CHECK (age >= 0)); | Add a rule that checks each value. |
CREATE TABLE users (id INT, email TEXT UNIQUE); | Require each email value to be unique. |
ALTER TABLE users ADD COLUMN phone TEXT; | Add a new column to a table. |
ALTER TABLE users DROP COLUMN phone; | Remove a column from a table. |
ALTER TABLE users RENAME COLUMN name TO full_name; | Rename a column in a table. |
DROP TABLE users; | Delete the table and all its rows. |
CREATE INDEX idx_users_city ON users (city); | Create an index on one column. |
CREATE UNIQUE INDEX idx_email ON users (email); | Create an index that blocks duplicates. |
DROP INDEX idx_users_city; | Remove an index from the database. |