SQL Cheat Sheet

Structured Query Language (SQL) is widely used for managing data in relational databases. It is ubiquitous in data engineering and analytics. This SQL cheat sheet offers a concise reference to the most commonly used SQL commands and techniques.

Querying Tables

Get all columns
Avoid usage in production queries.

_10
SELECT * FROM my_table;

Get specified columns

_10
SELECT col_1, col_2 FROM my_table;

Sort values, ascending
Defaults to ascending order. "ASC" is optional.

_10
SELECT col_1, col_2 FROM my_table
_10
ORDER BY col_1 ASC;

Sort values, descending

_10
SELECT col_1, col_2 FROM my_table
_10
ORDER BY col_1 DESC;

Alias a column
Give a column a temporary name

_10
SELECT col_name AS alias_name
_10
FROM my_table;

Alias a table
Give a table a temporary name

_10
SELECT *
_10
FROM table_name AS alias_name;

Filtering Rows

Filter rows by comparing values
= != < > <= >=

_10
SELECT * FROM cars
_10
WHERE age >= 10;

Filter within a specified range
Includes beginning and ending values.

_10
SELECT * FROM cars
_10
WHERE age BETWEEN 5 AND 10;

Filter outside a specified range
Excludes begin and end values.

_10
SELECT * FROM cars
_10
WHERE age NOT BETWEEN 5 AND 10;

Filter based on list of values

_10
SELECT * FROM cars
_10
WHERE color IN ('Red', 'Blue', 'Gold');

Filter by a string pattern
% represents zero, one or many characters.
_ represents a single character.

_10
-- Find country names that start with "A"
_10
SELECT * FROM countries
_10
WHERE country_name LIKE 'A%';
_10
_10
-- Find countries with a three-letter code
_10
-- where second letter is "A"
_10
SELECT * FROM countries
_10
WHERE country_code LIKE '_A_';

Grouping Rows and Aggregating Values

Get unique values in a column
If you select and group a single column, you'll get all its unique values. Better to use SELECT DISTINCT.

_10
SELECT make FROM cars
_10
GROUP BY make;
_10
_10
-- Does the same thing
_10
SELECT DISTINCT make FROM cars;

Get unique combinations across multiple columns

_10
-- Audi (make) A3 (model) will appear
_10
-- twice if it comes in two colors
_10
SELECT make, model FROM cars
_10
GROUP BY make, model, color;

Count number of rows

_10
-- Find total number of rows in a table
_10
SELECT COUNT(*) FROM cars;
_10
_10
-- Count non-NULL rows in a column
_10
SELECT COUNT(make) FROM cars;
_10
_10
-- Count unique non-NULL values in a column
_10
SELECT COUNT(DISTINCT(make)) FROM cars;

Sum up values

_10
-- Add up all values in a column
_10
SELECT SUM(mileage) FROM cars;
_10
_10
-- Add up values in filtered results
_10
SELECT SUM(mileage) FROM cars
_10
WHERE make = 'Ford';

Get average values
AVG excludes NULL values

_10
-- Get the average value of a column
_10
SELECT AVG(price) from cars;
_10
_10
-- Get average value of filtered results
_10
SELECT AVG(price) FROM cars
_10
WHERE make = 'Ferrari';

Aggregate values in a group
COUNT MAX MIN SUM AVG

_10
SELECT
_10
make,
_10
AVG(mileage) as avg_mileage
_10
FROM cars
_10
GROUP BY make;

Aggregate values in a filtered group
To filter rows after groupings have been made, use HAVING instead of WHERE.

_10
-- Find car makes having an
_10
-- average price of above $20K
_10
SELECT
_10
make,
_10
AVG(price)
_10
FROM cars
_10
GROUP BY make
_10
HAVING AVG(PRICE) > 20000;

Joining tables

Join by matching values in both tables
INNER JOIN combines rows from two tables where there is a match in the specified column.

_10
SELECT cars.make_id, makers.country FROM cars
_10
INNER JOIN makers
_10
ON cars.make_id = makers.id;

Join all rows in first table, matching rows in second table where possible
LEFT JOIN returns all rows from the left table, matched with corresponding rows in the right table or NULL if no match exists.

_10
SELECT cars.make_id, makers.country FROM cars
_10
LEFT JOIN makers
_10
ON cars.make_id = makers.id;

Join all rows in second table, matching rows in first table where possible
Right JOIN returns all rows from the right table, matched with corresponding rows in the left table or NULL if no match exists.

_10
SELECT cars.make_id, makers.country FROM cars
_10
RIGHT JOIN makers
_10
ON cars.make_id = makers.id;

Set Operations

Combine two result sets
UNION appends rows of one query to another.

_10
-- Duplicates are removed
_10
SELECT color FROM cars
_10
UNION
_10
SELECT color from motorcycles;
_10
_10
-- Duplicates not removed
_10
SELECT color FROM cars
_10
UNION ALL
_10
SELECT color FROM motorcycles;

Find common rows between two result sets
INTERSECT returns distinct rows common to both SELECT queries.

_10
-- Find colors that appear in both tables
_10
SELECT color FROM cars
_10
INTERSECT
_10
SELECT color from motorcycles;

Identify rows absent in another result set
EXCEPT finds rows that appear in the first result set but not the second.

_10
SELECT color FROM cars
_10
EXCEPT
_10
SELECT color from motorcycles;