SQL Cheat Sheet
The most important bits of SQL, all on one page.
December 3, 2023
Basics
SELECT
The 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 name and age columns from the users table.
SELECT name, age FROM users;To select all columns from a table, use SELECT *.
SELECT * FROM users;DISTINCT
The DISTINCT keyword is used to return only distinct (different) values within a column.
Example: Get a list of unique countries from the users table.
SELECT DISTINCT country FROM users;WHERE
The 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;ALIAS
Column Alias
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;Table Alias
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.
Example: Here, 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 u.name.
SELECT u.name FROM users AS u;Limit
The 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.
Example using AND: Select users aged between 18 and 30.
SELECT * FROM users WHERE age >= 18 AND age <= 30;ORDER BY
The 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
The 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
The 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
The IN operator allows you to specify multiple values in a WHERE clause.
Example: Select users from either ‘USA’ or ‘UK’.
SELECT * FROM users WHERE country IN ('USA', 'UK');Joins
INNER JOIN
INNER JOIN selects records that have matching values in both tables.
Example: Select all orders with user details by joining users and orders.
SELECT users.name, orders.order_id FROM users INNER JOIN orders ON users.id = orders.user_id;LEFT JOIN
A 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
A 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
A 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
A 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;Aggregation
GROUP BY
The 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
The HAVING clause is used to filter groups created by the GROUP BY clause. It is different from WHERE, as 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
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);Data Manipulation
Commands for adding, changing, or removing data.
INSERT INTO
The 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 28.
INSERT INTO users (name, age) VALUES ('John Doe', 28);UPDATE
The UPDATE statement is used to modify existing records in a table.
Example with WHERE clause: Update the age of a user named ‘John Doe’.
UPDATE users SET age = 29 WHERE name = 'John Doe';Example without WHERE clause: Change the status column for all records in the users table.
UPDATE users SET status = 'active';DELETE
The DELETE statement is used to delete records from a table.
Example with WHERE clause: Delete users who are under 18.
DELETE FROM users WHERE age < 18;Example without WHERE clause: Delete all records from the users table. Use with caution, as this will remove all data from the table.
DELETE FROM users;