SQL Cheat Sheet

Query rows

• Everything Every column of every row.Copy SELECT * FROM users;
• Some columns Only the columns you name.Copy SELECT name, email FROM users;
• No duplicates Each value once.Copy SELECT DISTINCT country FROM users;
• Sort Newest first. ASC, the default, is oldest first.Copy SELECT * FROM users ORDER BY created_at DESC;
• First rows Only the first 10 rows.Copy SELECT * FROM users LIMIT 10;
• Rename a column Give a column another name in the result.Copy SELECT name AS customer FROM users;

Filter rows

These go after FROM. NULL is never equal to anything, so test it with IS NULL, not = NULL.

• Compare Rows that pass a comparison: =, <>, <, >, <=, >=.Copy WHERE age >= 18
• Several tests Both must be true. OR needs only one.Copy WHERE country = 'US' AND active = 1
• One of a list Matches any value in the list.Copy WHERE country IN ('US', 'CA', 'MX')
• A range From 10 to 50, both included.Copy WHERE price BETWEEN 10 AND 50
• Text pattern Names starting with A. % stands for any text, _ for one character.Copy WHERE name LIKE 'A%'
• Missing value Rows where the column has no value. IS NOT NULL is the opposite.Copy WHERE email IS NULL

Join tables

The join examples assume FROM orders o in front of them.

• Matching rows Only orders that have a user.Copy JOIN users u ON u.id = o.user_id
• Keep all left rows Every order, with empty user columns where there is no match.Copy LEFT JOIN users u ON u.id = o.user_id
• Rows without a match After a LEFT JOIN: the orders that have no user.Copy WHERE u.id IS NULL
• Keep everything All rows from both tables. Not available in MySQL.Copy FULL OUTER JOIN users u ON u.id = o.user_id
• Stack two results One result under the other. Plain UNION also removes duplicates.Copy SELECT id FROM a UNION ALL SELECT id FROM b;
• Subquery Filter by the result of another query.Copy WHERE id IN (SELECT user_id FROM orders)

Group and summarize

• Count rows How many rows the table has.Copy SELECT COUNT(*) FROM orders;
• Count per group One row per country, with the number of users in it.Copy SELECT country, COUNT(*) FROM users GROUP BY country;
• Sum and average The total and the average of a column.Copy SELECT SUM(total), AVG(total) FROM orders;
• Smallest, largest The lowest and the highest value.Copy SELECT MIN(price), MAX(price) FROM products;
• Filter groups Goes after GROUP BY. Keeps only groups with more than 5 rows.Copy HAVING COUNT(*) > 5
• If, then, else A value that depends on a test. Use it like a column.Copy CASE WHEN total > 100 THEN 'big' ELSE 'small' END

Change data

An UPDATE or DELETE without WHERE changes every row in the table. Run it inside BEGIN, check the result, then COMMIT or ROLLBACK.

• Add a row Insert one row.Copy INSERT INTO users (name, age) VALUES ('Ann', 30);
• Change rows Change the rows that match.Copy UPDATE users SET active = 0 WHERE id = 42;
• Delete rows Remove the rows that match.Copy DELETE FROM users WHERE id = 42;
• Start a transaction Nothing after this is final until you commit.Copy BEGIN;
• Keep the changes Make everything since BEGIN permanent.Copy COMMIT;
• Undo the changes Throw away everything since BEGIN.Copy ROLLBACK;

Tables

• Create a table A new table with two columns.Copy CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(100));
• Add a column Add a column to an existing table. SQL Server leaves out the word COLUMN.Copy ALTER TABLE users ADD COLUMN age INT;
• Add an index Speed up lookups by a column.Copy CREATE INDEX idx_users_email ON users (email);
• Delete a table Remove the table and everything in it.Copy DROP TABLE users;
• Empty a table Remove every row but keep the table. In SQLite, use DELETE FROM users.Copy TRUNCATE TABLE users;
• Why is it slow How the database plans to run a query, and whether it uses an index.Copy EXPLAIN SELECT * FROM users WHERE id = 42;