SQL Cheat Sheet: Commands You'll Use Daily

Whether you're a developer working with databases daily or just starting your SQL journey, having a quick reference for common commands saves time and mental energy. This comprehensive SQL cheat sheet covers the commands you'll reach for most often, from basic queries to advanced window functions.

Bookmark this page—you'll be coming back to it.

1. Basic SELECT Queries

The SELECT statement is the foundation of SQL. Here's how to retrieve data from your tables.

Select All Columns

SELECT * FROM users;

Select Specific Columns

SELECT first_name, last_name, email FROM users;

Aliasing Columns

SELECT
    first_name AS "First Name",
    last_name AS "Last Name",
    email AS "Email Address"
FROM users;

Removing Duplicates

SELECT DISTINCT country FROM users;

Limiting Results

-- PostgreSQL, MySQL, SQLite
SELECT * FROM users LIMIT 10;

-- With offset (skip first 20, then get 10)
SELECT * FROM users LIMIT 10 OFFSET 20;

-- SQL Server
SELECT TOP 10 * FROM users;

Sorting Results

-- Ascending order (default)
SELECT * FROM users ORDER BY created_at;

-- Descending order
SELECT * FROM users ORDER BY created_at DESC;

-- Multiple columns
SELECT * FROM users ORDER BY country ASC, created_at DESC;

2. Filtering with WHERE

The WHERE clause lets you filter rows based on conditions.

Basic Comparisons

-- Equals
SELECT * FROM users WHERE status = 'active';

-- Not equals
SELECT * FROM users WHERE status != 'inactive';
SELECT * FROM users WHERE status <> 'inactive';

-- Greater than, less than
SELECT * FROM products WHERE price > 100;
SELECT * FROM products WHERE price >= 100;
SELECT * FROM products WHERE stock < 10;
SELECT * FROM products WHERE stock <= 10;

Multiple Conditions

-- AND: Both conditions must be true
SELECT * FROM users
WHERE status = 'active' AND country = 'USA';

-- OR: At least one condition must be true
SELECT * FROM users
WHERE country = 'USA' OR country = 'Canada';

-- Combining AND and OR (use parentheses for clarity)
SELECT * FROM users
WHERE status = 'active' AND (country = 'USA' OR country = 'Canada');

Pattern Matching with LIKE

-- Starts with 'John'
SELECT * FROM users WHERE first_name LIKE 'John%';

-- Ends with 'son'
SELECT * FROM users WHERE last_name LIKE '%son';

-- Contains 'admin'
SELECT * FROM users WHERE email LIKE '%admin%';

-- Single character wildcard
SELECT * FROM users WHERE first_name LIKE 'J_hn';

-- Case-insensitive (PostgreSQL)
SELECT * FROM users WHERE first_name ILIKE 'john%';

IN and NOT IN

-- Match any value in list
SELECT * FROM users
WHERE country IN ('USA', 'Canada', 'Mexico');

-- Exclude values in list
SELECT * FROM users
WHERE country NOT IN ('USA', 'Canada');

BETWEEN for Ranges

-- Inclusive range
SELECT * FROM orders
WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';

SELECT * FROM products
WHERE price BETWEEN 10 AND 100;

NULL Checks

-- Find NULL values
SELECT * FROM users WHERE phone IS NULL;

-- Find non-NULL values
SELECT * FROM users WHERE phone IS NOT NULL;

3. JOINs (INNER, LEFT, RIGHT)

JOINs combine rows from multiple tables based on related columns.

INNER JOIN

Returns only rows with matches in both tables.

SELECT
    users.first_name,
    users.last_name,
    orders.order_date,
    orders.total
FROM users
INNER JOIN orders ON users.id = orders.user_id;

LEFT JOIN (LEFT OUTER JOIN)

Returns all rows from the left table, plus matched rows from the right table. Unmatched rows show NULL.

SELECT
    users.first_name,
    users.last_name,
    orders.order_date
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

-- Find users without orders
SELECT users.*
FROM users
LEFT JOIN orders ON users.id = orders.user_id
WHERE orders.id IS NULL;

RIGHT JOIN (RIGHT OUTER JOIN)

Returns all rows from the right table, plus matched rows from the left table.

SELECT
    users.first_name,
    orders.order_date,
    orders.total
FROM users
RIGHT JOIN orders ON users.id = orders.user_id;

FULL OUTER JOIN

Returns all rows from both tables, with NULLs where there's no match.

SELECT
    users.first_name,
    orders.order_date
FROM users
FULL OUTER JOIN orders ON users.id = orders.user_id;

Multiple JOINs

SELECT
    users.first_name,
    orders.order_date,
    products.name AS product_name,
    order_items.quantity
FROM users
INNER JOIN orders ON users.id = orders.user_id
INNER JOIN order_items ON orders.id = order_items.order_id
INNER JOIN products ON order_items.product_id = products.id;

Self JOIN

Join a table to itself (useful for hierarchical data).

-- Find employees and their managers
SELECT
    e.name AS employee,
    m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

4. Aggregations

Aggregate functions compute values across multiple rows.

COUNT

-- Count all rows
SELECT COUNT(*) FROM users;

-- Count non-NULL values
SELECT COUNT(phone) FROM users;

-- Count distinct values
SELECT COUNT(DISTINCT country) FROM users;

SUM, AVG, MIN, MAX

SELECT
    SUM(total) AS total_revenue,
    AVG(total) AS average_order,
    MIN(total) AS smallest_order,
    MAX(total) AS largest_order
FROM orders;

GROUP BY

Group rows that share values and apply aggregate functions.

-- Total orders per country
SELECT
    country,
    COUNT(*) AS order_count,
    SUM(total) AS total_revenue
FROM orders
GROUP BY country;

-- Multiple grouping columns
SELECT
    country,
    status,
    COUNT(*) AS order_count
FROM orders
GROUP BY country, status;

HAVING

Filter groups after aggregation (WHERE filters before, HAVING filters after).

-- Countries with more than 100 orders
SELECT
    country,
    COUNT(*) AS order_count
FROM orders
GROUP BY country
HAVING COUNT(*) > 100;

-- Customers who spent more than $1000
SELECT
    user_id,
    SUM(total) AS total_spent
FROM orders
GROUP BY user_id
HAVING SUM(total) > 1000
ORDER BY total_spent DESC;

5. Subqueries & CTEs

Subqueries in WHERE

-- Users who have placed orders
SELECT * FROM users
WHERE id IN (SELECT DISTINCT user_id FROM orders);

-- Products priced above average
SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);

Subqueries in FROM

-- Average of order totals per user
SELECT AVG(user_totals.total_spent) AS avg_customer_value
FROM (
    SELECT user_id, SUM(total) AS total_spent
    FROM orders
    GROUP BY user_id
) AS user_totals;

Correlated Subqueries

-- Users with their most recent order date
SELECT
    u.first_name,
    u.last_name,
    (SELECT MAX(order_date)
     FROM orders o
     WHERE o.user_id = u.id) AS last_order_date
FROM users u;

Common Table Expressions (CTEs)

CTEs make complex queries more readable by defining temporary named result sets.

-- Basic CTE
WITH active_users AS (
    SELECT * FROM users WHERE status = 'active'
)
SELECT * FROM active_users WHERE country = 'USA';

-- Multiple CTEs
WITH
monthly_sales AS (
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        SUM(total) AS revenue
    FROM orders
    GROUP BY DATE_TRUNC('month', order_date)
),
avg_monthly AS (
    SELECT AVG(revenue) AS avg_revenue FROM monthly_sales
)
SELECT
    ms.month,
    ms.revenue,
    am.avg_revenue,
    ms.revenue - am.avg_revenue AS variance
FROM monthly_sales ms
CROSS JOIN avg_monthly am
ORDER BY ms.month;

Recursive CTEs

Useful for hierarchical or tree-structured data.

-- Organization hierarchy
WITH RECURSIVE org_tree AS (
    -- Base case: top-level employees
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive case: employees with managers
    SELECT e.id, e.name, e.manager_id, ot.level + 1
    FROM employees e
    INNER JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT * FROM org_tree ORDER BY level, name;

6. Window Functions

Window functions perform calculations across related rows without collapsing them into groups.

ROW_NUMBER, RANK, DENSE_RANK

SELECT
    first_name,
    last_name,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
    RANK() OVER (ORDER BY salary DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

• ROW_NUMBER(): Unique sequential numbers (1, 2, 3, 4...)
• RANK(): Same rank for ties, gaps after (1, 2, 2, 4...)
• DENSE_RANK(): Same rank for ties, no gaps (1, 2, 2, 3...)

PARTITION BY

Apply window functions within groups.

-- Rank employees within each department
SELECT
    department,
    first_name,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

Running Totals and Aggregates

SELECT
    order_date,
    total,
    SUM(total) OVER (ORDER BY order_date) AS running_total,
    AVG(total) OVER (ORDER BY order_date) AS running_avg
FROM orders;

LAG and LEAD

Access values from previous or next rows.

SELECT
    order_date,
    total,
    LAG(total, 1) OVER (ORDER BY order_date) AS previous_order,
    LEAD(total, 1) OVER (ORDER BY order_date) AS next_order,
    total - LAG(total, 1) OVER (ORDER BY order_date) AS change
FROM orders;

FIRST_VALUE and LAST_VALUE

SELECT
    product_name,
    category,
    price,
    FIRST_VALUE(price) OVER (
        PARTITION BY category ORDER BY price
    ) AS cheapest_in_category,
    LAST_VALUE(price) OVER (
        PARTITION BY category ORDER BY price
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS most_expensive_in_category
FROM products;

NTILE

Divide rows into equal groups.

-- Divide customers into quartiles by spending
SELECT
    user_id,
    total_spent,
    NTILE(4) OVER (ORDER BY total_spent DESC) AS spending_quartile
FROM (
    SELECT user_id, SUM(total) AS total_spent
    FROM orders
    GROUP BY user_id
) AS customer_totals;

7. INSERT, UPDATE, DELETE

INSERT

-- Insert single row
INSERT INTO users (first_name, last_name, email)
VALUES ('John', 'Doe', 'john@example.com');

-- Insert multiple rows
INSERT INTO users (first_name, last_name, email)
VALUES
    ('Jane', 'Smith', 'jane@example.com'),
    ('Bob', 'Johnson', 'bob@example.com'),
    ('Alice', 'Williams', 'alice@example.com');

-- Insert from SELECT
INSERT INTO archived_users (id, first_name, last_name, email)
SELECT id, first_name, last_name, email
FROM users
WHERE status = 'inactive';

INSERT with RETURNING (PostgreSQL)

INSERT INTO users (first_name, last_name, email)
VALUES ('John', 'Doe', 'john@example.com')
RETURNING id, created_at;

UPDATE

-- Update single column
UPDATE users SET status = 'active' WHERE id = 1;

-- Update multiple columns
UPDATE users
SET
    status = 'active',
    updated_at = NOW()
WHERE id = 1;

-- Update with subquery
UPDATE products
SET price = price * 1.10
WHERE category_id IN (
    SELECT id FROM categories WHERE name = 'Electronics'
);

UPDATE with RETURNING (PostgreSQL)

UPDATE users
SET status = 'inactive'
WHERE last_login < NOW() - INTERVAL '1 year'
RETURNING id, email;

DELETE

-- Delete specific rows
DELETE FROM users WHERE status = 'deleted';

-- Delete with subquery
DELETE FROM order_items
WHERE order_id IN (
    SELECT id FROM orders WHERE status = 'cancelled'
);

-- Delete all rows (use with caution!)
DELETE FROM temp_data;

-- Faster way to delete all rows
TRUNCATE TABLE temp_data;

UPSERT (INSERT ON CONFLICT)

Insert or update if the row already exists (PostgreSQL).

INSERT INTO user_preferences (user_id, theme, notifications)
VALUES (1, 'dark', true)
ON CONFLICT (user_id)
DO UPDATE SET
    theme = EXCLUDED.theme,
    notifications = EXCLUDED.notifications;

Quick Reference Tables

Comparison Operators

Operator	Description
=	Equal to
!= or <>	Not equal to
>	Greater than
<	Less than
>=	Greater than or equal
<=	Less than or equal
BETWEEN	Within a range
LIKE	Pattern matching
IN	Match any in a list
IS NULL	Is a null value

Common Aggregate Functions

Function	Description
COUNT()	Number of rows
SUM()	Total of values
AVG()	Average of values
MIN()	Minimum value
MAX()	Maximum value
STRING_AGG()	Concatenate strings (PostgreSQL)
ARRAY_AGG()	Aggregate into array (PostgreSQL)

Common Window Functions

Function	Description
ROW_NUMBER()	Unique row numbers
RANK()	Rank with gaps
DENSE_RANK()	Rank without gaps
NTILE(n)	Divide into n groups
LAG()	Previous row value
LEAD()	Next row value
FIRST_VALUE()	First value in window
LAST_VALUE()	Last value in window
SUM() OVER()	Running sum
AVG() OVER()	Running average

Continue Learning

This cheat sheet covers the commands you'll use most often, but SQL has much more depth to explore. Here are some paths for continued learning:

• New to SQL? Start with our SQL Basics course to build a solid foundation with PostgreSQL.
• Want hands-on practice? Our Interactive SQL Practice course lets you write queries against a live database in your browser.
• Building AI applications? Check out SQL Architecture in the AI Era for advanced topics like vector databases and performance optimization.

Keep this page bookmarked, and remember: the best way to learn SQL is to write SQL. Open up a database and start querying!