SQL Cheat Sheet: Queries, Joins, Functions

Most SQL tutorials bury the useful stuff under paragraphs of theory you don’t need. This is the opposite: a flat, scannable reference of the queries you’ll actually write. Every example uses the same two tables (employees and departments), shows the query, and shows the result. According to the 2025 Stack Overflow Developer Survey, SQL remains the third most-used language among professional developers, with over 50% using it regularly. If you write code that touches a database, this page is your bookmark.

developer tools

The Sample Tables

Every example in this guide uses these two tables. Copy them into any SQL sandbox to follow along.

CREATE TABLE departments (
    id    INT PRIMARY KEY,
    name  VARCHAR(50)
);

INSERT INTO departments VALUES
(1, 'Engineering'), (2, 'Marketing'), (3, 'Finance'), (4, 'HR');

CREATE TABLE employees (
    id         INT PRIMARY KEY,
    name       VARCHAR(100),
    dept_id    INT REFERENCES departments(id),
    salary     DECIMAL(10,2),
    hire_date  DATE
);

INSERT INTO employees VALUES
(1, 'Alice',   1, 95000,  '2022-03-15'),
(2, 'Bob',     1, 88000,  '2023-01-10'),
(3, 'Carol',   2, 72000,  '2021-07-22'),
(4, 'Dave',    2, 68000,  '2024-02-01'),
(5, 'Eve',     3, 105000, '2020-11-30'),
(6, 'Frank',   NULL, 60000, '2025-06-01');

Notice Frank has a NULL dept_id. He’s unassigned. This matters when we get to JOINs.

How Do You Write a SELECT Query?

The SELECT statement retrieves data from one or more tables. According to JetBrains’ State of Developer Ecosystem 2025, SELECT queries account for over 80% of all SQL statements executed in typical applications. Here’s the basic anatomy.

SELECT column1, column2
FROM table_name
WHERE condition
ORDER BY column1 ASC
LIMIT 10;

Select specific columns

SELECT name, salary FROM employees;

| name  | salary    |
|-------|-----------|
| Alice | 95000.00  |
| Bob   | 88000.00  |
| Carol | 72000.00  |
| Dave  | 68000.00  |
| Eve   | 105000.00 |
| Frank | 60000.00  |

Select all columns

SELECT * FROM employees;

Use SELECT * for exploration only. In production code, always name your columns explicitly. It prevents breakage when someone adds a column to the table.

Aliases

SELECT name AS employee_name,
       salary / 12 AS monthly_salary
FROM employees;

Aliases rename columns in the result set. They don’t change the actual table.

DISTINCT

SELECT DISTINCT dept_id FROM employees;

Returns unique values only: 1, 2, 3, NULL. Four rows, not six.

How Does WHERE Filtering Work?

WHERE clauses filter rows before any grouping or aggregation happens. A 2024 analysis by Percona found that poorly written WHERE clauses are the number one cause of slow queries in MySQL and PostgreSQL production databases. Getting filters right matters for both correctness and speed.

Comparison operators

Combining conditions

-- AND: both must be true
SELECT name, salary FROM employees
WHERE dept_id = 1 AND salary > 90000;

-- Result: Alice | 95000.00

-- OR: either can be true
SELECT name FROM employees
WHERE dept_id = 1 OR dept_id = 2;

-- Result: Alice, Bob, Carol, Dave

-- NOT: negate a condition
SELECT name FROM employees
WHERE NOT dept_id = 1;

-- Result: Carol, Dave, Eve (Frank excluded, NULL <> comparison returns NULL)

LIKE patterns

-- % matches zero or more characters
SELECT name FROM employees WHERE name LIKE 'A%';    -- Alice

-- _ matches exactly one character
SELECT name FROM employees WHERE name LIKE '_a%';   -- Dave (second char is 'a')

What Are SQL JOINs and When Do You Use Each One?

JOINs combine rows from two or more tables based on a related column. According to DB-Engines research, JOIN operations make up roughly 60% of multi-table query patterns in relational databases. Understanding the four core JOIN types covers virtually every scenario you’ll face. The ASCII diagrams below show which rows survive each join type. They’re worth studying until the behavior becomes intuitive.

INNER JOIN

Returns only rows where both tables have a match.

  employees       departments
  ┌───────┐       ┌───────┐
  │       ┌┼──────┼┐      │
  │       │  MATCH │      │
  │       └┼──────┼┘      │
  └───────┘       └───────┘
  Frank             HR
  (dropped)         (dropped)

SELECT e.name, d.name AS department
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

| name  | department  |
|-------|-------------|
| Alice | Engineering |
| Bob   | Engineering |
| Carol | Marketing   |
| Dave  | Marketing   |
| Eve   | Finance     |

Frank is gone (NULL dept_id). HR is gone (no employees in it). Only matches survive.

LEFT JOIN (LEFT OUTER JOIN)

All rows from the left table, matched rows from the right. NULLs fill where there’s no match.

  employees       departments
  ┌───────────┐   ┌───────┐
  │  ALL LEFT ┌┼──┼┐      │
  │  rows     │MATCH      │
  │  kept     └┼──┼┘      │
  └───────────┘   └───────┘
  Frank kept        HR dropped
  (dept = NULL)

SELECT e.name, d.name AS department
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;

| name  | department  |
|-------|-------------|
| Alice | Engineering |
| Bob   | Engineering |
| Carol | Marketing   |
| Dave  | Marketing   |
| Eve   | Finance     |
| Frank | NULL        |

Frank appears with a NULL department. HR still doesn’t show up because employees is the left table.

RIGHT JOIN (RIGHT OUTER JOIN)

The mirror image. All rows from the right table, NULLs for non-matching left rows.

  employees       departments
  ┌───────┐   ┌───────────┐
  │       ┌┼──┼┐ ALL RIGHT│
  │       │MATCH  rows    │
  │       └┼──┼┘ kept     │
  └───────┘   └───────────┘
  Frank          HR kept
  dropped        (emp = NULL)

SELECT e.name, d.name AS department
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.id;

| name  | department  |
|-------|-------------|
| Alice | Engineering |
| Bob   | Engineering |
| Carol | Marketing   |
| Dave  | Marketing   |
| Eve   | Finance     |
| NULL  | HR          |

Now HR appears. Frank doesn’t. In practice, most people rewrite RIGHT JOINs as LEFT JOINs by swapping the table order. It’s easier to read.

FULL OUTER JOIN

Every row from both tables. NULLs on either side where there’s no match.

  employees       departments
  ┌───────────────────────┐
  │ ALL LEFT  MATCH  ALL  │
  │ rows      rows   RIGHT│
  │ kept      kept   kept │
  └───────────────────────┘
  Frank AND HR both appear

SELECT e.name, d.name AS department
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.id;

| name  | department  |
|-------|-------------|
| Alice | Engineering |
| Bob   | Engineering |
| Carol | Marketing   |
| Dave  | Marketing   |
| Eve   | Finance     |
| Frank | NULL        |
| NULL  | HR          |

Quick JOIN reference

SQL and data formatting

How Do GROUP BY and Aggregation Work?

GROUP BY collapses rows into groups and lets aggregate functions summarize each group. PostgreSQL’s documentation notes that aggregate queries are the primary tool for reporting and analytics in SQL. Without GROUP BY, functions like COUNT and AVG operate on the entire result set as one group.

Core aggregate functions

GROUP BY example

SELECT d.name AS department,
       COUNT(*) AS headcount,
       ROUND(AVG(e.salary), 0) AS avg_salary
FROM employees e
JOIN departments d ON e.dept_id = d.id
GROUP BY d.name;

| department  | headcount | avg_salary |
|-------------|-----------|------------|
| Engineering | 2         | 91500      |
| Marketing   | 2         | 70000      |
| Finance     | 1         | 105000     |

Frank isn’t counted because the INNER JOIN excluded him. If you want him, use LEFT JOIN and count carefully.

HAVING: filter after aggregation

WHERE filters rows before grouping. HAVING filters groups after aggregation.

SELECT d.name AS department, COUNT(*) AS headcount
FROM employees e
JOIN departments d ON e.dept_id = d.id
GROUP BY d.name
HAVING COUNT(*) > 1;

| department  | headcount |
|-------------|-----------|
| Engineering | 2         |
| Marketing   | 2         |

Finance is excluded because it has only one employee.

What Are SQL Window Functions?

Window functions perform calculations across related rows without collapsing them into groups. The 2025 Stack Overflow Survey ranks window functions as the SQL feature developers most want to learn, with 34% of respondents listing them as a knowledge gap. They’re not hard once you see the pattern. Window functions click when you realize they’re just “GROUP BY that doesn’t collapse.” Every row keeps its identity. The function just peeks at neighboring rows.

Syntax

FUNCTION(args) OVER (
    PARTITION BY column    -- optional: defines groups
    ORDER BY column        -- optional: defines row order within groups
    ROWS BETWEEN ...       -- optional: defines the frame
)

ROW_NUMBER, RANK, DENSE_RANK

SELECT name, dept_id, 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;

| name  | dept_id | salary    | row_num | rank | dense_rank |
|-------|---------|-----------|---------|------|------------|
| Eve   | 3       | 105000.00 | 1       | 1    | 1          |
| Alice | 1       | 95000.00  | 2       | 2    | 2          |
| Bob   | 1       | 88000.00  | 3       | 3    | 3          |
| Carol | 2       | 72000.00  | 4       | 4    | 4          |
| Dave  | 2       | 68000.00  | 5       | 5    | 5          |
| Frank | NULL    | 60000.00  | 6       | 6    | 6          |

With no ties, all three produce the same result. When there are ties, RANK skips numbers, DENSE_RANK doesn’t, and ROW_NUMBER assigns arbitrarily.

PARTITION BY: window functions per group

SELECT name, d.name AS department, salary,
       RANK() OVER (PARTITION BY e.dept_id ORDER BY salary DESC) AS dept_rank
FROM employees e
JOIN departments d ON e.dept_id = d.id;

| name  | department  | salary    | dept_rank |
|-------|-------------|-----------|-----------|
| Alice | Engineering | 95000.00  | 1         |
| Bob   | Engineering | 88000.00  | 2         |
| Carol | Marketing   | 72000.00  | 1         |
| Dave  | Marketing   | 68000.00  | 2         |
| Eve   | Finance     | 105000.00 | 1         |

Each department gets its own ranking. This is the bread and butter of “top N per group” queries.

LAG and LEAD

SELECT name, salary,
       LAG(salary, 1)  OVER (ORDER BY hire_date) AS prev_hire_salary,
       LEAD(salary, 1) OVER (ORDER BY hire_date) AS next_hire_salary
FROM employees;

LAG looks at the previous row, LEAD at the next. Perfect for comparing sequential values like month-over-month metrics.

Running totals

SELECT name, salary,
       SUM(salary) OVER (ORDER BY hire_date) AS running_total
FROM employees;

| name  | salary    | running_total |
|-------|-----------|---------------|
| Eve   | 105000.00 | 105000.00     |
| Carol | 72000.00  | 177000.00     |
| Alice | 95000.00  | 272000.00     |
| Bob   | 88000.00  | 360000.00     |
| Dave  | 68000.00  | 428000.00     |
| Frank | 60000.00  | 488000.00     |

developer productivity tools

How Do Subqueries and CTEs Compare?

Subqueries and CTEs (Common Table Expressions) both let you build queries in stages. According to Microsoft’s SQL Server documentation, CTEs improve readability without any performance penalty in most query planners. Use whichever reads more clearly, but CTEs tend to win for anything beyond a simple one-liner.

Subquery in WHERE

-- Employees earning above average
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

| name  | salary    |
|-------|-----------|
| Alice | 95000.00  |
| Bob   | 88000.00  |
| Eve   | 105000.00 |

Subquery in FROM (derived table)

SELECT department, avg_salary
FROM (
    SELECT d.name AS department, AVG(e.salary) AS avg_salary
    FROM employees e
    JOIN departments d ON e.dept_id = d.id
    GROUP BY d.name
) AS dept_stats
WHERE avg_salary > 80000;

CTE: the readable alternative

WITH dept_stats AS (
    SELECT d.name AS department, AVG(e.salary) AS avg_salary
    FROM employees e
    JOIN departments d ON e.dept_id = d.id
    GROUP BY d.name
)
SELECT department, avg_salary
FROM dept_stats
WHERE avg_salary > 80000;

Same result, much easier to follow. You can chain multiple CTEs with commas:

WITH
    active_emps AS (
        SELECT * FROM employees WHERE hire_date > '2022-01-01'
    ),
    dept_counts AS (
        SELECT dept_id, COUNT(*) AS cnt
        FROM active_emps
        GROUP BY dept_id
    )
SELECT d.name, dc.cnt
FROM dept_counts dc
JOIN departments d ON dc.dept_id = d.id;

How Do INSERT, UPDATE, and DELETE Work?

Data modification statements change rows in your tables. OWASP’s SQL Injection Prevention Cheat Sheet reports that SQL injection remains a top-10 web vulnerability, and write operations are the primary attack vector. Always use parameterized queries in application code.

INSERT

-- Single row
INSERT INTO employees (id, name, dept_id, salary, hire_date)
VALUES (7, 'Grace', 4, 78000, '2026-01-15');

-- Multiple rows
INSERT INTO employees (id, name, dept_id, salary, hire_date)
VALUES (8, 'Hank', 1, 92000, '2026-02-01'),
       (9, 'Iris', 3, 87000, '2026-03-10');

-- Insert from SELECT
INSERT INTO employees (id, name, dept_id, salary, hire_date)
SELECT id + 100, name, dept_id, salary * 1.1, CURRENT_DATE
FROM employees
WHERE dept_id = 1;

UPDATE

-- Update specific rows
UPDATE employees
SET salary = salary * 1.05
WHERE dept_id = 1;

-- Update with a JOIN (syntax varies by database)
-- PostgreSQL / MySQL:
UPDATE employees e
SET salary = salary * 1.10
FROM departments d
WHERE e.dept_id = d.id AND d.name = 'Engineering';

DELETE

-- Delete specific rows
DELETE FROM employees WHERE id = 7;

-- Delete with a subquery
DELETE FROM employees
WHERE dept_id IN (
    SELECT id FROM departments WHERE name = 'HR'
);

UPSERT (INSERT … ON CONFLICT)

-- PostgreSQL
INSERT INTO employees (id, name, dept_id, salary, hire_date)
VALUES (1, 'Alice', 1, 98000, '2022-03-15')
ON CONFLICT (id) DO UPDATE
SET salary = EXCLUDED.salary;

-- MySQL equivalent
INSERT INTO employees (id, name, dept_id, salary, hire_date)
VALUES (1, 'Alice', 1, 98000, '2022-03-15')
ON DUPLICATE KEY UPDATE salary = VALUES(salary);

How Do Indexes Improve SQL Performance?

Indexes speed up reads at the cost of slower writes and additional storage. Percona’s performance benchmarks show that a proper index on a filtered column can reduce query time by 99% on tables with millions of rows. Knowing which columns to index is one of the highest-value skills in database work. We’ve seen production queries go from 30 seconds to 3 milliseconds after adding a single composite index on the right columns. The difference between indexed and unindexed lookups is not incremental; it’s orders of magnitude.

Create an index

-- Single column
CREATE INDEX idx_employees_dept ON employees(dept_id);

-- Composite (multi-column)
CREATE INDEX idx_emp_dept_salary ON employees(dept_id, salary);

-- Unique index
CREATE UNIQUE INDEX idx_emp_email ON employees(email);

When to index

EXPLAIN: see the query plan

EXPLAIN ANALYZE
SELECT * FROM employees WHERE dept_id = 1;

Look for Seq Scan (full table scan, usually slow on large tables) vs Index Scan (fast). If you see a Seq Scan on a large table with a WHERE clause, you probably need an index.

What Common SQL Patterns Should Every Developer Know?

These patterns come up constantly in real applications. Each one solves a specific, recurring problem. According to GitHub’s analysis of public repositories, SQL files are among the top 15 most common file types in open-source projects, showing just how pervasive these patterns are.

Top N per group

-- Top earner per department
WITH ranked AS (
    SELECT e.name, d.name AS department, e.salary,
           ROW_NUMBER() OVER (PARTITION BY e.dept_id ORDER BY e.salary DESC) AS rn
    FROM employees e
    JOIN departments d ON e.dept_id = d.id
)
SELECT name, department, salary
FROM ranked
WHERE rn = 1;

| name  | department  | salary    |
|-------|-------------|-----------|
| Alice | Engineering | 95000.00  |
| Carol | Marketing   | 72000.00  |
| Eve   | Finance     | 105000.00 |

Find duplicates

SELECT name, COUNT(*) AS occurrences
FROM employees
GROUP BY name
HAVING COUNT(*) > 1;

Conditional aggregation (pivot)

SELECT
    d.name AS department,
    COUNT(CASE WHEN e.salary >= 80000 THEN 1 END) AS high_earners,
    COUNT(CASE WHEN e.salary < 80000 THEN 1 END) AS standard_earners
FROM employees e
JOIN departments d ON e.dept_id = d.id
GROUP BY d.name;

Gaps and islands

-- Find missing IDs in a sequence
SELECT a.id + 1 AS gap_start,
       MIN(b.id) - 1 AS gap_end
FROM employees a
LEFT JOIN employees b ON b.id > a.id
GROUP BY a.id
HAVING a.id + 1 < MIN(b.id);

Self JOIN: compare rows in the same table

-- Find employees earning more than their department peers
SELECT e1.name, e1.salary, e2.name AS peer, e2.salary AS peer_salary
FROM employees e1
JOIN employees e2 ON e1.dept_id = e2.dept_id AND e1.id <> e2.id
WHERE e1.salary > e2.salary;

COALESCE: handle NULLs gracefully

SELECT name, COALESCE(dept_id, 0) AS dept_id_safe
FROM employees;
-- Frank's NULL becomes 0

regex patterns for data validation

Frequently Asked Questions

What’s the difference between WHERE and HAVING?

WHERE filters individual rows before grouping happens. HAVING filters groups after aggregation. You can’t use aggregate functions like COUNT() or AVG() in WHERE because the groups don’t exist yet at that stage. Use WHERE for row-level conditions, HAVING for group-level conditions.

Should I use a subquery or a JOIN?

JOINs typically perform better for combining data from multiple tables. Subqueries work well for single-value comparisons (like checking against an average). Modern query planners optimize both patterns similarly in many cases, but JOINs are more readable for multi-table operations. Use CTEs when the logic gets complex.

Do window functions slow down queries?

Window functions add computation but avoid the need for self-JOINs or correlated subqueries, which are often slower. According to PostgreSQL documentation, window functions process after WHERE, GROUP BY, and HAVING, so filtering rows early reduces the window function’s workload. Index the ORDER BY column in your OVER clause for best performance.

Is SQL the same across all databases?

Core SQL (SELECT, JOIN, GROUP BY, WHERE) is standardized by ANSI/ISO and works identically across PostgreSQL, MySQL, SQL Server, SQLite, and Oracle. Differences appear in advanced features: window function syntax, upsert syntax, date functions, and string functions vary by vendor. This cheat sheet notes vendor-specific differences where they matter.

How do I learn SQL faster?

Practice on real data. Set up a local PostgreSQL or SQLite database, import a public dataset, and write queries against it. The Stack Overflow Developer Survey consistently shows that developers who use SQL daily reach proficiency within 3 to 6 months. Focus on SELECT, JOIN, GROUP BY, and window functions first.

---

SQL has been around since the 1970s and it isn’t going anywhere. The syntax is stable, the patterns are well-established, and the skills transfer across every database you’ll ever use. Bookmark this page, come back when you need a quick reference, and don’t try to memorize everything at once. The best way to internalize SQL is to write queries against real data, break things, and figure out why.

more developer references