SQL Cheat Sheet
The most important bits of SQL, all on one page.
December 3, 2023
SELECT statement is used to select data from a database. It retrieves data from one or more columns of a table.
Example: The following query selects the
age columns from the
SELECT name, age FROM users;
To select all columns from a table, use
SELECT * FROM users;
DISTINCT keyword is used to return only distinct (different) values within a column.
Example: Get a list of unique countries from the
SELECT DISTINCT country FROM users;
WHERE clause is used to filter records based on one or more conditions.
Example: Select all records from
users where the
age is greater than or equal to 18.
SELECT * FROM users WHERE age >= 18;
Used for renaming a column in the output of the query. It does not change the column name in the database.
Example: Assign the alias
username to the
name column in the query result.
SELECT name AS username FROM users;
Used for giving a table a temporary name in the context of a query, which is particularly useful in complex queries involving multiple tables and joins.
AS u assigns a shorter alias
u to the
users table. This alias can then be used to refer to the table in the query, as is done here with
SELECT u.name FROM users AS u;
LIMIT clause is used to constrain the number of rows returned by a query.
Example: Retrieve only the first 5 users.
SELECT * FROM users LIMIT 5;
Filtering, Sorting, and Operators
AND, OR, NOT
These operators are used to filter records based on more than one condition.
AND: Select users aged between 18 and 30.
SELECT * FROM users WHERE age >= 18 AND age <= 30;
ORDER BY keyword is used to sort the result set in ascending or descending order.
Example: Sort users by their names in descending order.
SELECT * FROM users ORDER BY name DESC;
BETWEEN operator selects values within a given range.
Example: Select products with a price between 10 and 20.
SELECT * FROM products WHERE price BETWEEN 10 AND 20;
LIKE operator is used for pattern matching in a query.
Example: Find users whose names start with ‘J’.
SELECT * FROM users WHERE name LIKE 'J%';
IN operator allows you to specify multiple values in a
Example: Select users from either ‘USA’ or ‘UK’.
SELECT * FROM users WHERE country IN ('USA', 'UK');
INNER JOIN selects records that have matching values in both tables.
Example: Select all orders with user details by joining
SELECT users.name, orders.order_id FROM users INNER JOIN orders ON users.id = orders.user_id;
LEFT JOIN returns all records from the left table (table1), and the matched records from the right table (table2). Unmatched records from the right table will have NULLs.
Example: Retrieve all users and their orders, if any.
SELECT users.name, orders.order_id FROM users LEFT JOIN orders ON users.id = orders.user_id;
RIGHT JOIN works exactly opposite to a
LEFT JOIN. It returns all records from the right table, and the matched records from the left table.
Example: Show all orders and user details if the order is associated with a user.
SELECT users.name, orders.order_id FROM orders RIGHT JOIN users ON users.id = orders.user_id;
FULL JOIN combines the results of both left and right outer joins. It returns all records when there is a match in either left or right table.
Example: Select all users and all orders, with NULLs where there is no match.
SELECT users.name, orders.order_id FROM users FULL OUTER JOIN orders ON users.id = orders.user_id;
CROSS JOIN matches each row of the first table with every row of the second table. It’s used to create a Cartesian product of two tables.
Example: Combine every user with every product.
SELECT users.name, products.name FROM users CROSS JOIN products;
GROUP BY statement groups rows by one or more columns and is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns.
Example: Count the number of users in each country.
SELECT COUNT(id), country FROM users GROUP BY country;
HAVING clause is used to filter groups created by the
GROUP BY clause. It is different from
HAVING filters aggregated or grouped data.
Example: Select countries with more than 10 users.
SELECT COUNT(id), country FROM users GROUP BY country HAVING COUNT(id) > 10;
Subqueries are nested inside a SELECT, INSERT, UPDATE, or DELETE statement or inside another subquery.
Example: Select names of users who have made orders totaling over 500.
SELECT name FROM users WHERE id IN (SELECT user_id FROM orders WHERE total > 500);
Commands for adding, changing, or removing data.
INSERT INTO statement is used to add new rows to a table.
Example: Insert a new user into the
users table with name
'John Doe' and age
INSERT INTO users (name, age) VALUES ('John Doe', 28);
UPDATE statement is used to modify existing records in a table.
WHERE clause: Update the age of a user named ‘John Doe’.
UPDATE users SET age = 29 WHERE name = 'John Doe';
WHERE clause: Change the
status column for all records in the
UPDATE users SET status = 'active';
DELETE statement is used to delete records from a table.
WHERE clause: Delete users who are under 18.
DELETE FROM users WHERE age < 18;
WHERE clause: Delete all records from the
users table. Use with caution, as this will remove all data from the table.
DELETE FROM users;