SQL Cheat Sheet | QueryCase

Retrieving Data

SELECT

Choose which columns to return.

SELECT column1, column2 FROM table_name;

Example

SELECT name, department FROM employees;

SELECT *

Return all columns.

SELECT * FROM table_name;

AS (Alias)

Rename a column in the result.

SELECT column AS alias_name FROM table_name;

Example

SELECT first_name AS "First Name" FROM employees;

DISTINCT

Remove duplicate rows from results.

SELECT DISTINCT column FROM table_name;

Example

SELECT DISTINCT department FROM employees;

Filtering Rows

WHERE

Filter rows based on a condition. Operators: = != < > <= >=

SELECT * FROM table_name WHERE condition;

Example

SELECT * FROM employees WHERE department = 'Sales';

AND

Both conditions must be true.

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

OR

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

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

IN

Match any value in a list.

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

BETWEEN

Match a range of values (inclusive).

SELECT * FROM employees
  WHERE salary BETWEEN 40000 AND 80000;

LIKE

Pattern matching. % = any characters, _ = one character.

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

IS NULL / IS NOT NULL

Check for missing values.

SELECT * FROM employees WHERE manager_id IS NULL;

Sorting & Limiting

ORDER BY

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

SELECT * FROM employees ORDER BY salary DESC;

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

LIMIT

Return only the first N rows.

SELECT * FROM employees
  ORDER BY salary DESC
  LIMIT 5;

OFFSET

Skip rows before returning. Useful for pagination.

SELECT * FROM employees LIMIT 10 OFFSET 20;

Aggregate Functions

COUNT / SUM / AVG / MIN / MAX

Compute a single value from many rows.

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.

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

HAVING

Filter groups after aggregation (like WHERE, but for groups).

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

JOINs - Combining Tables

Visual reference

INNER JOINMatches only

LEFT JOINAll of A + matches

RIGHT JOINAll of B + matches

FULL OUTERAll rows from both

Anti-join: LEFT JOIN + WHERE b.id IS NULL - returns A rows with no match in B

INNER JOIN

Only rows that match in both tables.

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

LEFT JOIN

All rows from left table + matching from right (NULL if no match).

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

RIGHT JOIN

All rows from right table + matching from left.

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

FULL OUTER JOIN

All rows from both tables, NULLs where no match.

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

String Functions

Common String Functions

Transform and inspect text values.

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 syntax varies by database.

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.

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

Subquery in FROM

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

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

Query Execution Order

Execution order

SQL clauses execute 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

You write SELECT first - SQL runs FROM first