The Ultimate SQL Cheat Sheet

Your quick reference guide to Common SQL commands and syntax. Perfect for beginners and pros alike.

Basic Queries

Select All

Retrieve all columns from a table.

SELECT * FROM table_name;

Select Specific Columns

Retrieve specific columns.

SELECT column1, column2 FROM table_name;

Select Distinct

Return only distinct (different) values.

SELECT DISTINCT column1 FROM table_name;

Where Clause

Filter records.

SELECT * FROM table_name WHERE condition;

Filtering Data

AND Operator

Record must satisfy both conditions.

SELECT * FROM table_name WHERE condition1 AND condition2;

OR Operator

Record must satisfy at least one condition.

SELECT * FROM table_name WHERE condition1 OR condition2;

IN Operator

Specify multiple values in a WHERE clause.

SELECT * FROM table_name WHERE column_name IN ('value1', 'value2');

BETWEEN Operator

Select values within a given range.

SELECT * FROM table_name WHERE column_name BETWEEN value1 AND value2;

LIKE Operator

Search for a pattern. % represents zero or more characters.

SELECT * FROM table_name WHERE column_name LIKE 'a%';

IS NULL

Test for empty values.

SELECT * FROM table_name WHERE column_name IS NULL;

Joins

Inner Join

Returns records that have matching values in both tables.

SELECT *
FROM table1
INNER JOIN table2 ON table1.id = table2.id;

Left Join

Returns all records from the left table, and the matched records from the right table.

SELECT *
FROM table1
LEFT JOIN table2 ON table1.id = table2.id;

Right Join

Returns all records from the right table, and the matched records from the left table.

SELECT *
FROM table1
RIGHT JOIN table2 ON table1.id = table2.id;

Full Join

Returns all records when there is a match in either left or right table.

SELECT *
FROM table1
FULL OUTER JOIN table2 ON table1.id = table2.id;

Aggregation

COUNT

Returns the number of rows.

SELECT COUNT(column_name) FROM table_name;

SUM

Returns the total sum of a numeric column.

SELECT SUM(column_name) FROM table_name;

AVG

Returns the average value of a numeric column.

SELECT AVG(column_name) FROM table_name;

MAX / MIN

Returns the largest / smallest value.

SELECT MAX(column_name) FROM table_name;

GROUP BY

Group rows that have the same values into summary rows.

SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name;

HAVING

Filter groups (WHERE cannot be used with aggregate functions).

SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name
HAVING COUNT(*) > 5;

String Functions

CONCAT

Add two or more strings together.

SELECT CONCAT(first_name, ' ', last_name) FROM table_name;

LENGTH

Returns the length of a string.

SELECT LENGTH(column_name) FROM table_name;

UPPER / LOWER

Convert string to upper / lower case.

SELECT UPPER(column_name) FROM table_name;

SUBSTRING

Extract some characters from a string.

SELECT SUBSTRING(column_name, 1, 5) FROM table_name;

Date & Time

Current Date/Time

Get the current date or timestamp.

SELECT CURRENT_DATE;
SELECT CURRENT_TIMESTAMP;

Date Intervals

Add or subtract time (SQLite syntax).

SELECT date('now', '+1 day');
SELECT date('now', '-1 month');

Date Formatting

Format dates using strftime.

SELECT strftime('%Y-%m-%d', 'now');

Extract Year/Month

Extract specific parts from a date.

SELECT strftime('%Y', 'now') AS year;
SELECT strftime('%m', 'now') AS month;

DML (Data Manipulation)

INSERT

Insert new rows into a table.

INSERT INTO table_name (col1, col2)
VALUES ('val1', 'val2');

UPDATE

Update existing records.

UPDATE table_name
SET column1 = 'value1', column2 = 'value2'
WHERE condition;

DELETE

Delete records from a table.

DELETE FROM table_name WHERE condition;

DDL (Data Definition)

CREATE TABLE

Create a new table with constraints.

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE

Modify an existing table structure.

ALTER TABLE table_name ADD COLUMN new_col TEXT;
ALTER TABLE table_name RENAME TO new_name;

CREATE INDEX

Create an index for performance.

CREATE INDEX idx_user_email ON users(email);

DROP TABLE

Delete a table and all its data.

DROP TABLE IF EXISTS table_name;

Window Functions

ROW_NUMBER

Assigns a unique integer to rows within a partition.

SELECT column_name, ROW_NUMBER() OVER (PARTITION BY group_col ORDER BY sort_col) FROM table_name;

RANK

Assigns a rank to each row within a partition (gaps in rank allowed).

SELECT column_name, RANK() OVER (ORDER BY score DESC) FROM table_name;

DENSE_RANK

Assigns a rank to each row within a partition (no gaps in rank).

SELECT column_name, DENSE_RANK() OVER (ORDER BY score DESC) FROM table_name;

LEAD / LAG

Access data from the next (LEAD) or previous (LAG) row.

SELECT value, LAG(value, 1) OVER (ORDER BY date) FROM table_name;

Advanced & Sets

CTE (Common Table Expression)

Defines a temporary result set.

WITH Sales_CTE AS (
    SELECT employee_id, SUM(amount) as total
    FROM sales
    GROUP BY employee_id
)
SELECT * FROM Sales_CTE WHERE total > 1000;

UNION / UNION ALL

Combine result sets. UNION removes duplicates, UNION ALL keeps them.

SELECT column_name FROM table1
UNION ALL
SELECT column_name FROM table2;

EXISTS

Test for the existence of any record in a subquery.

SELECT * FROM table1
WHERE EXISTS (SELECT 1 FROM table2 WHERE table1.id = table2.id);

CASE

Go through conditions and return a value when the first condition is met.

SELECT column_name,
CASE
    WHEN condition1 THEN 'Result1'
    WHEN condition2 THEN 'Result2'
    ELSE 'Result3'
END
FROM table_name;

Order of Execution

Logical Flow

The internal order in which a SQL query is processed.

1. FROM (and JOINs)
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. DISTINCT
7. ORDER BY
8. LIMIT / OFFSET