SQL Cheat Sheet

40 essentials with syntax and working examples. Search or filter by group.

SELECT / FROM

Choose the columns to return and the table to read. SELECT * returns all columns.

SELECT col1, col2 FROM table;

SELECT store_name, city, region
FROM stores;

WHERE

Filters rows before grouping. Combine conditions with AND / OR / NOT.

SELECT ... FROM t WHERE condition;

SELECT store_name, square_feet
FROM stores
WHERE region = 'South' AND store_type = 'Supermarket';

ORDER BY

Sorts the result. ASC is the default.

ORDER BY col [ASC | DESC], ...

SELECT product_name, unit_price
FROM products
ORDER BY unit_price DESC, product_name;

LIMIT

Returns only the first n rows. SQL Server uses TOP n or OFFSET/FETCH; Oracle uses FETCH FIRST n ROWS ONLY.

LIMIT n [OFFSET m]

SELECT product_name, unit_price
FROM products
ORDER BY unit_price DESC
LIMIT 5;

DISTINCT

Removes duplicate rows from the result.

SELECT DISTINCT col FROM t;

SELECT DISTINCT category
FROM products
ORDER BY category;

AS (alias)

Renames a column or table in the query. Table aliases make joins shorter.

SELECT expr AS name FROM table AS t

SELECT p.product_name AS product,
       p.unit_price - p.unit_cost AS unit_margin
FROM products AS p;

IN / BETWEEN

IN tests against a list; BETWEEN tests an inclusive range (works with ISO text dates).

col IN (v1, v2) | col BETWEEN a AND b

SELECT order_id, order_date, payment_method
FROM orders
WHERE payment_method IN ('Cash', 'EBT')
  AND order_date BETWEEN '2025-01-01' AND '2025-01-31';

LIKE

Text pattern match: % = any characters, _ = one character. In SQLite LIKE is case-insensitive for ASCII; in PostgreSQL it is case-sensitive (use ILIKE).

col LIKE 'pattern'

SELECT product_name, category
FROM products
WHERE product_name LIKE '%chips%';

IS NULL

Tests for missing values. Never use = NULL: it is never true.

col IS NULL | col IS NOT NULL

SELECT employee_id, first_name, last_name, role
FROM employees
WHERE manager_id IS NULL;

GROUP BY

Groups rows so aggregate functions return one value per group. Every non-aggregated column in SELECT should be in GROUP BY.

SELECT col, AGG(x) FROM t GROUP BY col;

SELECT channel, COUNT(*) AS orders
FROM orders
GROUP BY channel;

HAVING

Filters groups after aggregation (WHERE filters rows before).

GROUP BY col HAVING AGG(x) condition

SELECT customer_id, COUNT(*) AS orders
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 15
ORDER BY orders DESC;

INNER JOIN

Returns only rows with a match in both tables.

FROM a JOIN b ON a.key = b.key

SELECT s.region,
       ROUND(SUM(oi.quantity * oi.unit_price * (1 - oi.discount)), 2) AS net_sales
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id
JOIN stores s ON s.store_id = o.store_id
GROUP BY s.region
ORDER BY net_sales DESC;

LEFT JOIN

Keeps every row of the left table; columns from the right table are NULL when there is no match. Useful to find records with no activity. Put conditions on the right table in ON, not WHERE, or the LEFT JOIN acts like an INNER JOIN.

FROM a LEFT JOIN b ON a.key = b.key

-- customers with no orders since 2025-10-01
SELECT c.customer_id, c.first_name, c.last_name
FROM customers c
LEFT JOIN orders o
       ON o.customer_id = c.customer_id
      AND o.order_date >= '2025-10-01'
WHERE o.order_id IS NULL;

Self join

Joins a table to itself, e.g. employees with their managers.

FROM t AS a JOIN t AS b ON a.parent_id = b.id

SELECT e.first_name || ' ' || e.last_name AS employee,
       m.first_name || ' ' || m.last_name AS manager
FROM employees e
JOIN employees m ON m.employee_id = e.manager_id
LIMIT 10;

CROSS JOIN

Every combination of rows from both tables. Useful to build a full grid (e.g. store x category) before a LEFT JOIN.

FROM a CROSS JOIN b

SELECT s.store_name, c.category
FROM stores s
CROSS JOIN (SELECT DISTINCT category FROM products) c
ORDER BY s.store_name, c.category;

COUNT

COUNT(*) counts rows, COUNT(col) counts non-NULL values, COUNT(DISTINCT col) counts unique values.

COUNT(*) | COUNT(col) | COUNT(DISTINCT col)

SELECT COUNT(*) AS orders,
       COUNT(DISTINCT customer_id) AS customers
FROM orders;

SUM

Adds numeric values. Can sum an expression, such as net sales per line.

SUM(expr)

SELECT ROUND(SUM(quantity * unit_price * (1 - discount)), 2) AS net_sales
FROM order_items;

AVG

Arithmetic mean, ignoring NULLs. In SQLite AVG always returns a real number.

AVG(expr)

SELECT role, ROUND(AVG(hourly_rate), 2) AS avg_rate
FROM employees
GROUP BY role
ORDER BY avg_rate DESC;

MIN / MAX

Smallest and largest value; works on numbers, text and ISO dates.

MIN(expr) | MAX(expr)

SELECT MIN(order_date) AS first_order,
       MAX(order_date) AS last_order
FROM orders;

CASE

Conditional logic inside a query. Combine with SUM for conditional totals.

CASE WHEN cond THEN v1 [WHEN ...] ELSE v2 END

SELECT store_id,
       SUM(CASE WHEN channel = 'Online' THEN 1 ELSE 0 END) AS online_orders,
       SUM(CASE WHEN channel = 'In-store' THEN 1 ELSE 0 END) AS instore_orders
FROM orders
GROUP BY store_id;

COALESCE

Returns the first non-NULL value. Common for replacing NULLs with a default.

COALESCE(v1, v2, ...)

SELECT employee_id, first_name,
       COALESCE(manager_id, 0) AS manager_id
FROM employees
LIMIT 10;

CAST

Converts a value to another type (INTEGER, REAL, TEXT...). Avoids integer division: in SQLite 7/2 = 3.

CAST(expr AS type)

SELECT campaign_name,
       ROUND(CAST(clicks AS REAL) / impressions * 100, 2) AS ctr_pct
FROM campaigns
ORDER BY ctr_pct DESC
LIMIT 5;

ROUND

Rounds a number to the given decimals (0 by default).

ROUND(x [, decimals])

SELECT product_name,
       ROUND((unit_price - unit_cost) / unit_price * 100, 1) AS margin_pct
FROM products
ORDER BY margin_pct DESC;

Subquery

A query inside another query, used as a value, a list or a derived table.

WHERE col > (SELECT ...) | FROM (SELECT ...) AS t

SELECT product_name, unit_price
FROM products
WHERE unit_price > (SELECT AVG(unit_price) FROM products)
ORDER BY unit_price DESC;

EXISTS

TRUE if the subquery returns at least one row. Efficient for 'has any...' checks.

WHERE [NOT] EXISTS (SELECT 1 FROM ... WHERE ...)

SELECT c.customer_id, c.first_name, c.last_name
FROM customers c
WHERE c.loyalty_member = 1
  AND NOT EXISTS (
      SELECT 1 FROM orders o
      WHERE o.customer_id = c.customer_id AND o.channel = 'Online'
  )
LIMIT 10;

WITH (CTE)

Common Table Expression: a named temporary result you can reference later in the query. Makes long queries readable.

WITH name AS (SELECT ...) SELECT ... FROM name;

WITH order_totals AS (
    SELECT order_id,
           SUM(quantity * unit_price * (1 - discount)) AS order_total
    FROM order_items
    GROUP BY order_id
)
SELECT o.channel,
       ROUND(AVG(t.order_total), 2) AS avg_order_value
FROM order_totals t
JOIN orders o ON o.order_id = t.order_id
GROUP BY o.channel;

WITH RECURSIVE

A CTE that references itself. Typical uses: generating a date series or walking a hierarchy.

WITH RECURSIVE r AS (anchor UNION ALL recursive_part) SELECT ...

WITH RECURSIVE months(m) AS (
    SELECT '2025-01-01'
    UNION ALL
    SELECT date(m, '+1 month') FROM months WHERE m < '2025-12-01'
)
SELECT m FROM months;

ROW_NUMBER

Numbers rows 1, 2, 3... inside each partition. Classic use: pick the first or latest row per group.

ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)

WITH ranked AS (
    SELECT customer_id, order_id, order_date,
           ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date, order_id) AS rn
    FROM orders
)
SELECT customer_id, order_id, order_date AS first_order
FROM ranked
WHERE rn = 1
LIMIT 10;

RANK / DENSE_RANK

Ranking with ties. RANK leaves gaps after ties (1,1,3); DENSE_RANK does not (1,1,2).

RANK() OVER (ORDER BY ...) | DENSE_RANK() OVER (...)

SELECT category, product_name, unit_price,
       DENSE_RANK() OVER (PARTITION BY category ORDER BY unit_price DESC) AS price_rank
FROM products;

SUM() OVER (running total)

An aggregate used as a window: with ORDER BY it becomes a running (cumulative) total.

SUM(x) OVER (ORDER BY ... [ROWS BETWEEN ...])

WITH monthly AS (
    SELECT strftime('%Y-%m', o.order_date) AS month,
           SUM(oi.quantity * oi.unit_price * (1 - oi.discount)) AS sales
    FROM orders o
    JOIN order_items oi ON oi.order_id = o.order_id
    WHERE o.order_date LIKE '2025%'
    GROUP BY month
)
SELECT month,
       ROUND(sales, 2) AS sales,
       ROUND(SUM(sales) OVER (ORDER BY month), 2) AS sales_ytd
FROM monthly;

Share of total (PARTITION BY)

A window aggregate without ORDER BY returns the group total on every row, so you can compute percentages without a second query.

x / SUM(x) OVER (PARTITION BY ...)

SELECT region, store_name, square_feet,
       ROUND(100.0 * square_feet / SUM(square_feet) OVER (PARTITION BY region), 1) AS pct_of_region
FROM stores
ORDER BY region, pct_of_region DESC;

LAG / LEAD

Reads a value from a previous (LAG) or next (LEAD) row. Ideal for month-over-month change.

LAG(x [, n, default]) OVER (ORDER BY ...)

WITH monthly AS (
    SELECT strftime('%Y-%m', o.order_date) AS month,
           SUM(oi.quantity * oi.unit_price * (1 - oi.discount)) AS sales
    FROM orders o
    JOIN order_items oi ON oi.order_id = o.order_id
    GROUP BY month
)
SELECT month,
       ROUND(sales, 2) AS sales,
       ROUND(sales - LAG(sales) OVER (ORDER BY month), 2) AS change_vs_prev
FROM monthly;

Moving average (ROWS frame)

A window frame limits which rows are included. Here: the current month plus the two before it.

AVG(x) OVER (ORDER BY ... ROWS BETWEEN n PRECEDING AND CURRENT ROW)

WITH monthly AS (
    SELECT strftime('%Y-%m', o.order_date) AS month,
           SUM(oi.quantity * oi.unit_price * (1 - oi.discount)) AS sales
    FROM orders o
    JOIN order_items oi ON oi.order_id = o.order_id
    GROUP BY month
)
SELECT month,
       ROUND(AVG(sales) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS ma_3m
FROM monthly;

NTILE

Splits ordered rows into n roughly equal buckets (e.g. quartiles).

NTILE(n) OVER (ORDER BY ...)

SELECT customer_id, COUNT(*) AS orders,
       NTILE(4) OVER (ORDER BY COUNT(*) DESC) AS quartile
FROM orders
GROUP BY customer_id
LIMIT 10;

strftime

SQLite: formats or extracts date parts (%Y year, %m month, %d day, %w weekday 0=Sunday). PostgreSQL uses EXTRACT/TO_CHAR/DATE_TRUNC; SQL Server uses YEAR(), MONTH(), FORMAT().

strftime(format, date)

SELECT strftime('%Y', order_date) AS year,
       COUNT(*) AS orders
FROM orders
GROUP BY year;

date() modifiers

SQLite: date arithmetic with text modifiers. PostgreSQL uses date + INTERVAL '1 month'; SQL Server uses DATEADD(month, 1, d).

date(value, '+n days' | '-n months' | 'start of month' ...)

SELECT campaign_name, start_date,
       date(start_date, 'start of month') AS month_start,
       date(start_date, '+30 days') AS plus_30
FROM campaigns
LIMIT 5;

julianday

SQLite: difference between dates in days. PostgreSQL: d2 - d1; SQL Server: DATEDIFF(day, d1, d2).

julianday(d2) - julianday(d1)

SELECT campaign_name,
       julianday(end_date) - julianday(start_date) + 1 AS days_running
FROM campaigns
ORDER BY days_running DESC
LIMIT 5;

UNION / UNION ALL

Stacks the results of two queries with the same columns. UNION removes duplicates; UNION ALL keeps them (and is faster).

SELECT ... UNION [ALL] SELECT ...

SELECT city, 'Store' AS source FROM stores
UNION
SELECT city, 'Customer' FROM customers
ORDER BY city
LIMIT 10;

INTERSECT

Rows that appear in both results.

SELECT ... INTERSECT SELECT ...

SELECT customer_id FROM orders WHERE channel = 'Online'
INTERSECT
SELECT customer_id FROM orders WHERE channel = 'In-store'
LIMIT 10;

EXCEPT

Rows in the first result that are not in the second. Oracle calls it MINUS.

SELECT ... EXCEPT SELECT ...

-- customers with no orders in the second half of 2025
SELECT customer_id FROM customers
EXCEPT
SELECT customer_id FROM orders WHERE order_date >= '2025-07-01';