All Posts

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;

Get started with DB Pilot

Download DB Pilot, the database GUI with a twist.
Speed up your SQL workflows today.