How to read this sheet

Each card gives a pattern (the template) and usually an example (a real query you could run). Words like table_name, column and employees are placeholders, swap in your own table and column names.

SELECTkeywordsCOUNTfunctions'text'text values123numbers-- notecomments

A real query stacks many of these together

SELECT department, COUNT(*) AS headcount  -- columns to show
FROM employees                            -- the source table
WHERE salary > 50000                      -- filter rows first
GROUP BY department                       -- collapse into groups
HAVING COUNT(*) > 3                        -- filter the groups
ORDER BY headcount DESC                   -- sort the result
LIMIT 10;                                 -- cap the rows

You write the clauses in this order, but the database runs them in a different one. See Query Execution Order at the end.

Retrieving Data

SELECT

Choose which columns to return.

Pattern

SELECT column1, column2 FROM table_name;

Example

SELECT name, department FROM employees;

SELECT *

Return every column. Handy while exploring, but name the columns you need in real queries.

Pattern

SELECT * FROM table_name;

AS (Alias)

Rename a column in the result.

Pattern

SELECT column AS alias_name FROM table_name;

Example

SELECT first_name AS "First Name" FROM employees;

DISTINCT

Remove duplicate rows from results.

Pattern

SELECT DISTINCT column FROM table_name;

Example

SELECT DISTINCT department FROM employees;

Filtering Rows

WHERE

Keep only the rows that match a condition. Operators: = != < > <= >=

Pattern

SELECT * FROM table_name WHERE condition;

Example

SELECT * FROM employees WHERE department = 'Sales';

TipWrap text in single quotes ('Sales'), but leave numbers bare (50000).

AND

Both conditions must be true.

Example

SELECT * FROM employees
  WHERE department = 'Sales'
    AND salary > 50000;

OR

Either condition can be true. Use parentheses to control precedence.

Example

SELECT * FROM employees
  WHERE city = 'NYC' OR city = 'LA';

IN

Match any value in a list. Shorter than chaining lots of ORs.

Example

SELECT * FROM employees
  WHERE department IN ('Sales', 'Marketing', 'HR');

BETWEEN

Match a range of values (inclusive of both ends).

Example

SELECT * FROM employees
  WHERE salary BETWEEN 40000 AND 80000;

LIKE

Pattern matching for text. % = any number of characters, _ = exactly one.

Example

SELECT * FROM employees WHERE name LIKE 'J%';
SELECT * FROM employees WHERE name LIKE '_ohn';

IS NULL / IS NOT NULL

Check for missing values.

Example

SELECT * FROM employees WHERE manager_id IS NULL;

TipAlways IS NULL, never = NULL. NULL is never equal to anything, not even another NULL.

Sorting & Limiting

ORDER BY

Sort results. ASC (default) = ascending, DESC = descending.

Example

SELECT * FROM employees ORDER BY salary DESC;

-- Multiple columns
SELECT * FROM employees
  ORDER BY department ASC, salary DESC;

TipSorting is the last step, so you can ORDER BY an alias you created in SELECT.

LIMIT

Return only the first N rows. Pair with ORDER BY to get a real top-N.

Example

SELECT * FROM employees
  ORDER BY salary DESC
  LIMIT 5;

OFFSET

Skip rows before returning. Useful for pagination.

Example

SELECT * FROM employees LIMIT 10 OFFSET 20;

Aggregate Functions

COUNT / SUM / AVG / MIN / MAX

Collapse many rows into a single value.

Example

SELECT
  COUNT(*) AS total_employees,
  AVG(salary) AS avg_salary,
  MAX(salary) AS top_salary,
  MIN(salary) AS lowest_salary,
  SUM(salary) AS payroll
FROM employees;

GROUP BY

Group rows that share a value, then aggregate each group separately.

Example

SELECT department, COUNT(*) AS headcount
  FROM employees
  GROUP BY department;

TipEvery column in SELECT that is not inside an aggregate must appear in GROUP BY.

HAVING

Filter groups after aggregation, like WHERE but for grouped results.

Example

SELECT department, AVG(salary) AS avg_sal
  FROM employees
  GROUP BY department
  HAVING AVG(salary) > 60000;

TipWHERE filters rows before grouping. HAVING filters the groups after.

JOINs - Combining Tables

Visual reference

Orange shows which rows each JOIN keeps. A = left table, B = right table.

INNER JOINMatches only

LEFT JOINAll of A + matches

RIGHT JOINAll of B + matches

FULL OUTERAll rows from both

Need the rows with no match? LEFT JOIN ... WHERE b.id IS NULL

INNER JOIN

Only rows that have a match in both tables.

Example

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

TipON is the match rule. It is what links a row in one table to a row in the other.

LEFT JOIN

All rows from the left table, plus matches from the right (NULL where there is none).

Example

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

RIGHT JOIN

All rows from the right table, plus matches from the left.

Pattern

SELECT * FROM a RIGHT JOIN b ON a.id = b.a_id;

FULL OUTER JOIN

All rows from both tables, with NULLs wherever there is no match.

Pattern

SELECT * FROM a FULL OUTER JOIN b ON a.id = b.a_id;

String Functions

Common String Functions

Transform and inspect text values.

Example

UPPER('hello')              → 'HELLO'
LOWER('HELLO')              → 'hello'
LENGTH('hello')             → 5
TRIM('  hello  ')           → 'hello'
CONCAT('hello', ' world')   → 'hello world'
SUBSTRING('hello', 1, 3)    → 'hel'
REPLACE('hello', 'l', 'r')  → 'herro'

Date Functions

Common Date Functions

Work with dates and times. Exact names vary by database.

Example

CURRENT_DATE              -- today's date
CURRENT_TIMESTAMP         -- current date + time
EXTRACT(YEAR FROM date)   -- pull part of a date
DATE_DIFF(date1, date2)   -- days between dates

Subqueries

Subquery in WHERE

Use a query inside another query to filter results.

Example

SELECT * FROM employees
  WHERE dept_id IN (
    SELECT id FROM departments
      WHERE location = 'NYC'
  );

Subquery in FROM

Use a query as a temporary table (a derived table).

Example

SELECT * FROM (
  SELECT department, COUNT(*) AS cnt
    FROM employees
    GROUP BY department
) sub
WHERE sub.cnt > 5;

Query Execution Order

Execution order

SQL clauses run in this order, not the order you write them

1FROMpick the table(s)

2WHEREfilter rows

3GROUP BYgroup rows

4HAVINGfilter groups

5SELECTchoose columns

6DISTINCTremove duplicates

7ORDER BYsort results

8LIMITrestrict row count

This is why WHERE cannot use a column alias from SELECT (SELECT runs later), and why HAVING can filter on a COUNT(*) that WHERE cannot.