Skip to content
Tutorials Center
My learning

Topics

Lesson 120 of 125 ยท Practice and Review

SQL Cheat Sheet

A one-page SQL cheat sheet covering SELECT, filters, joins, aggregates, subqueries, CTEs, window functions, INSERT/UPDATE/DELETE, DDL and dialect differences.

This page squeezes the whole SQL course into one scrollable reference: the clauses, functions and statements you reach for every day, in compact snippets you can scan in seconds. Keep it open while you work through the practice exercises and the 50 classic query problems, while you write reports at work, or the night before an interview.

Every section links to the lesson that explains the topic properly, with the reasoning and pitfalls that a cheat sheet has no room for. The snippets use standard SQL and run in SQLite unless a comment names another database. The handful of rules that surprise people most often come with live editors, so you can run them and see the behavior for yourself.

The examples use the course's sample databases: shop (categories, products, customers, orders, order_items), company (departments, employees, projects, assignments), school (students, courses, teachers, enrollments, terms) and bookstore.

The shape of a query

Clause order: written vs executed

SQL
SELECT   [DISTINCT] columns, expressions, aggregates   -- 5. pick and compute columns
FROM     table [JOIN other ON ...]                      -- 1. build the source rows
WHERE    row_condition                                  -- 2. filter rows
GROUP BY grouping_columns                               -- 3. form groups
HAVING   group_condition                                -- 4. filter groups
ORDER BY sort_columns [ASC | DESC]                      -- 6. sort
LIMIT    n [OFFSET m];                                  -- 7. cut

You write the clauses in this order, but the database evaluates them logically as numbered. That explains two classic errors: WHERE can't use aggregates (groups don't exist yet), and WHERE can't use a column alias from SELECT (it isn't computed yet), while ORDER BY can. Window functions run after HAVING and before ORDER BY. See HAVING and Aliases.

Syntax rules

SQL
-- Keywords are case-insensitive; UPPERCASE keywords and snake_case names are the convention.
SELECT first_name FROM customers;           -- statements end with a semicolon
SELECT 'O''Brien';                          -- 'single quotes' for strings, '' escapes a quote
SELECT "order" FROM "group";                -- "double quotes" for identifiers (MySQL: `backticks`, SQL Server: [brackets])
/* block comment */  -- line comment

See SQL Syntax and Style and SQL Command Types.

Selecting and filtering

SELECT

SQL
SELECT * FROM products;                              -- every column (fine for exploring, not for apps)
SELECT name, price * 1.2 AS gross FROM products;     -- expressions and aliases
SELECT DISTINCT country FROM customers;              -- unique values (or unique combinations)
SELECT COUNT(DISTINCT country) FROM customers;       -- count unique values
SELECT 2 + 3 AS five;                                -- SELECT without FROM (Oracle: FROM dual)

See SELECT in Depth, DISTINCT and Calculated Columns.

WHERE conditions

Goal Condition
Compare price = 10, <> or !=, <, <=, >, >=
Combine a AND b, a OR b, NOT a (AND binds tighter than OR: use parentheses)
List country IN ('France', 'Poland'), NOT IN (...)
Range (inclusive) price BETWEEN 10 AND 20
Pattern name LIKE 'Ca%' (% = any run, _ = one character)
Missing value email IS NULL, email IS NOT NULL (never = NULL)
Null-safe compare a IS b (SQLite), a IS NOT DISTINCT FROM b (standard)
Dates as text ordered_at >= '2025-03-01' AND ordered_at < '2025-04-01'

See WHERE, AND, OR and NOT, IN and BETWEEN, LIKE and NULL.

NULL and three-valued logic

Any comparison with NULL is unknown, and WHERE keeps only rows where the condition is true. The most famous trap is NOT IN with a NULL in the list:

SQL NULL traps ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

2 NOT IN (1, NULL) is NULL, not true, so a WHERE ... NOT IN (subquery) returns no rows at all if the subquery yields a single NULL. Use NOT EXISTS instead. See Working with NULL and EXISTS.

Sorting and paging

SQL
SELECT name, price FROM products
ORDER BY price DESC, name ASC          -- several keys; ASC is the default
LIMIT 10 OFFSET 20;                    -- rows 21-30 (page 3 of 10)

ORDER BY email NULLS LAST              -- NULL placement (SQLite 3.30+, PostgreSQL, Oracle)
ORDER BY 2                             -- by position in the SELECT list (avoid in real code)
ORDER BY CASE status WHEN 'pending' THEN 1 ELSE 2 END   -- custom order

-- Keyset pagination: faster than big OFFSETs
SELECT id, name FROM products WHERE id > 120 ORDER BY id LIMIT 10;
Database Top 10 rows
SQLite, PostgreSQL, MySQL LIMIT 10
SQL Server SELECT TOP 10 ... or OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY
Standard, Oracle 12c+, PostgreSQL FETCH FIRST 10 ROWS ONLY

See ORDER BY and LIMIT, OFFSET and Pagination.

Functions and expressions

Operators

SQL
7 / 2         -- 3   (integer division when both sides are integers: SQLite, PostgreSQL, SQL Server)
7 / 2.0       -- 3.5 (make one side real; MySQL's / always returns a decimal)
7 % 2         -- 1   (remainder)
'a' || 'b'    -- 'ab' (standard concatenation; SQL Server uses +, MySQL uses CONCAT())
5 & 3, 5 | 3  -- 1, 7 (bitwise)

Precedence, high to low: unary - and ~, ||, * / %, + -, bitwise, comparisons, IS/IN/LIKE/BETWEEN, NOT, AND, OR. See SQL Operators.

Strings

Task SQLite Elsewhere
Length length(s) LENGTH (PG, MySQL), LEN (SQL Server)
Case upper(s), lower(s) same
Part of a string substr(s, start, len) (1-based) SUBSTRING(s FROM 2 FOR 3) (standard)
Trim trim(s), ltrim, rtrim same; TRIM(BOTH 'x' FROM s) (standard)
Replace replace(s, 'a', 'b') same
Find position instr(s, 'x') POSITION('x' IN s), STRPOS (PG), CHARINDEX (SQL Server)
Join s1 || s2, concat(...), concat_ws(', ', ...) CONCAT everywhere
Format printf('%.2f', x) / format(...) TO_CHAR (PG, Oracle), FORMAT (MySQL, SQL Server)

See String Functions.

Numbers

SQL
ROUND(3.14159, 2)  -- 3.14     ABS(-4)       -- 4
CEIL(2.1)          -- 3        FLOOR(2.9)    -- 2
SQRT(16), POWER(2, 10), LOG10(1000), EXP(1) -- math functions (log(x) is natural here and in MySQL, base 10 in PostgreSQL)
random()           -- random 64-bit integer (RANDOM() in PG, RAND() in MySQL)
CAST(price AS INTEGER), CAST('42' AS REAL)   -- conversions; typeof(x) shows SQLite's storage class

See Numeric Functions and Types and CAST.

Dates and times (SQLite)

SQL
date('now')                                  -- '2026-10-17' (UTC)
datetime('now', 'localtime')
date('2025-01-31', '+1 month')               -- modifiers: '+7 days', '-1 year', 'start of month'
strftime('%Y-%m', ordered_at)                -- '2025-03' (group by month)
julianday('2025-03-01') - julianday('2025-01-01')   -- 59 (days)
unixepoch('2025-01-01')                      -- seconds since 1970

Other databases use real date types: CURRENT_DATE, date + INTERVAL '1 month' and EXTRACT(YEAR FROM d) (PostgreSQL), DATE_ADD and DATEDIFF (MySQL), DATEADD and DATEDIFF (SQL Server). See Date and Time Functions.

CASE, COALESCE and NULLIF

SQL
CASE WHEN price < 20 THEN 'cheap'
     WHEN price < 100 THEN 'mid'
     ELSE 'premium' END                      -- searched CASE (first true branch wins)
CASE status WHEN 'pending' THEN 1 ELSE 0 END -- simple CASE
iif(stock = 0, 'sold out', 'in stock')       -- SQLite/SQL Server shortcut
COALESCE(email, phone, 'n/a')                -- first non-NULL value
NULLIF(x, 0)                                 -- NULL when x = 0: total / NULLIF(count, 0) avoids division by zero

See CASE Expressions and COALESCE, IFNULL and NULLIF.

JSON (SQLite)

SQL
json_extract('{"a": {"b": 5}}', '$.a.b')     -- 5
'{"tags": ["x", "y"]}' ->> '$.tags[1]'       -- 'y' (-> returns JSON, ->> returns an SQL value)
SELECT value FROM json_each('[10, 20, 30]'); -- one row per element
json_group_array(name), json_object('id', id, 'name', name)   -- build JSON results

See Working with JSON.

Aggregation

Aggregate functions and GROUP BY

SQL
SELECT
  category_id,
  COUNT(*)            AS products,     -- rows in the group
  COUNT(stock)        AS with_stock,   -- non-NULL values only
  SUM(stock)          AS units,
  ROUND(AVG(price), 2) AS avg_price,
  MIN(price), MAX(price),
  group_concat(name, ', ') AS names    -- string_agg in PostgreSQL/SQL Server (also SQLite 3.44+)
FROM products
WHERE price > 10                       -- filters rows before grouping
GROUP BY category_id
HAVING COUNT(*) >= 3                   -- filters groups after aggregating
ORDER BY units DESC;

Every selected column must be grouped or aggregated (SQLite and MySQL without ONLY_FULL_GROUP_BY let you break this rule and return an arbitrary value). SUM of no rows is NULL; SQLite's total() returns 0.0. See Aggregate Functions, GROUP BY, HAVING and Grouping in Practice.

Pivots and subtotals

SQL
-- Conditional aggregation: one column per value
SELECT
  strftime('%Y-%m', ordered_at) AS month,
  COUNT(*) FILTER (WHERE status = 'delivered') AS delivered,      -- standard FILTER (SQLite, PG)
  SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled  -- works everywhere
FROM orders
GROUP BY month;

-- Subtotals (PostgreSQL, SQL Server, Oracle; MySQL: WITH ROLLUP). SQLite: UNION ALL a totals query.
GROUP BY ROLLUP (country, city)
GROUP BY CUBE (country, status)
GROUP BY GROUPING SETS ((country), (status), ())

See Pivoting Rows into Columns and Subtotals.

Joins

Join types

Join Keeps
INNER JOIN ... ON only rows that match on both sides
LEFT JOIN all left rows; NULLs where the right side has no match
RIGHT JOIN all right rows (SQLite 3.39+)
FULL OUTER JOIN all rows from both sides (SQLite 3.39+; not in MySQL)
CROSS JOIN every combination (rows ร— rows)
Self join a table joined to itself under two aliases
SQL
SELECT o.id, c.first_name, p.name, oi.quantity
FROM orders AS o
JOIN customers   AS c  ON c.id = o.customer_id
JOIN order_items AS oi ON oi.order_id = o.id
JOIN products    AS p  ON p.id = oi.product_id;

-- Anti-join: rows with no match
SELECT c.* FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE o.id IS NULL;

JOIN t USING (customer_id)   -- when both sides share the column name

See Why Joins?, INNER JOIN, LEFT JOIN, RIGHT and FULL OUTER JOIN, Self Joins, CROSS JOIN and Joining Many Tables.

ON vs WHERE in a LEFT JOIN

A condition on the right table in WHERE throws away the unmatched rows and silently turns the LEFT JOIN into an inner join. Put it in ON to keep them:

SQL LEFT JOIN: ON vs WHERE ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The first query lists all six categories (Outdoor, Audio and Home Office with 0); the second returns only the three that have a product under 15.

Subqueries, sets and CTEs

Subqueries

SQL
-- Scalar: one value
SELECT name, price FROM products WHERE price > (SELECT AVG(price) FROM products);

-- List: IN
SELECT * FROM customers WHERE id IN (SELECT customer_id FROM orders WHERE status = 'cancelled');

-- Table (derived table) in FROM
SELECT AVG(n) FROM (SELECT customer_id, COUNT(*) AS n FROM orders GROUP BY customer_id) AS t;

-- Correlated: runs per outer row
SELECT e.* FROM employees AS e
WHERE e.salary = (SELECT MAX(salary) FROM employees WHERE department_id = e.department_id);

-- EXISTS / NOT EXISTS: is there at least one matching row?
SELECT * FROM customers AS c
WHERE NOT EXISTS (SELECT 1 FROM orders AS o WHERE o.customer_id = c.id);

See Subqueries, Correlated Subqueries and EXISTS.

Set operations

SQL
SELECT city FROM customers UNION     SELECT city FROM suppliers;  -- combined, duplicates removed
SELECT city FROM customers UNION ALL SELECT city FROM suppliers;  -- combined, keeps duplicates (faster)
SELECT city FROM customers INTERSECT SELECT city FROM suppliers;  -- in both
SELECT city FROM customers EXCEPT    SELECT city FROM suppliers;  -- in the first only (Oracle: MINUS)

Both sides need the same number of columns with compatible types; names come from the first query, and one ORDER BY at the end sorts the whole result. See UNION, INTERSECT and EXCEPT.

Common table expressions

SQL
WITH customer_totals AS (
  SELECT customer_id, SUM(quantity * unit_price) AS spent
  FROM orders JOIN order_items ON order_items.order_id = orders.id
  GROUP BY customer_id
),
big_spenders AS (
  SELECT * FROM customer_totals WHERE spent > 1000
)
SELECT COUNT(*) FROM big_spenders;

A recursive CTE has an anchor query, UNION ALL, and a recursive part that refers to the CTE itself. It's the standard way to walk hierarchies and generate series:

SQL Recursive CTE: org chart levels ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

See Common Table Expressions and Recursive CTEs.

Window functions

Syntax

SQL
function(...) OVER (
  PARTITION BY dept_id            -- separate window per group (optional)
  ORDER BY salary DESC            -- order inside the window (optional)
  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW   -- frame (optional)
)
Function Returns
ROW_NUMBER() 1, 2, 3, 4 (unique, ties broken arbitrarily)
RANK() 1, 2, 2, 4 (gaps after ties)
DENSE_RANK() 1, 2, 2, 3 (no gaps)
NTILE(4) bucket number 1โ€“4
LAG(x, 1), LEAD(x, 1) value from the previous / next row
FIRST_VALUE(x), LAST_VALUE(x), NTH_VALUE(x, n) value from a position in the frame
SUM(x) OVER (ORDER BY ...) running total
AVG(x) OVER (... ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) 3-row moving average
PERCENT_RANK(), CUME_DIST() relative position from 0 to 1

You can't filter on a window function in WHERE (it runs later): wrap the query in a CTE or subquery and filter outside. Top-N per group is the classic case:

SQL Top 2 salaries per department ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

With ORDER BY inside OVER and no frame, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, so ties share the same running total, and LAST_VALUE returns the current row rather than the last one. Spell out ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING when you need the whole partition. See Window Functions, Ranking, Running Totals, LAG and LEAD, Window Frames and Percentiles.

Changing data

INSERT, UPDATE, DELETE

SQL
INSERT INTO categories (name) VALUES ('Garden');
INSERT INTO categories (name) VALUES ('Toys'), ('Books');           -- several rows
INSERT INTO archive (id, total) SELECT id, total FROM orders WHERE ...;
INSERT INTO categories (name) VALUES ('Pets') RETURNING id;         -- SQLite 3.35+, PostgreSQL

UPDATE products SET price = price * 1.1, stock = stock - 1 WHERE id = 7;
UPDATE products SET stock = s.qty FROM stock_counts AS s WHERE s.product_id = products.id;  -- UPDATE ... FROM

DELETE FROM orders WHERE status = 'cancelled';
DELETE FROM sessions;          -- every row! TRUNCATE TABLE sessions; in other databases

Always run the WHERE as a SELECT first, and wrap risky changes in a transaction. See INSERT, UPDATE and DELETE.

Upserts

SQL
INSERT INTO page_views (page, views) VALUES ('/home', 1)
ON CONFLICT (page) DO UPDATE SET views = views + 1;     -- SQLite, PostgreSQL
ON CONFLICT (page) DO NOTHING;                          -- skip duplicates
-- MySQL: ON DUPLICATE KEY UPDATE views = views + 1
-- SQL Server, Oracle, PostgreSQL 15+: MERGE INTO ... WHEN MATCHED THEN UPDATE WHEN NOT MATCHED THEN INSERT
SQL Upsert a counter

Press Run (or Ctrl + Enter) to see the result here.

See Upserts with ON CONFLICT.

Transactions

SQL
BEGIN;                                   -- START TRANSACTION in MySQL, BEGIN TRANSACTION in SQL Server
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
UPDATE accounts SET balance = balance + 50 WHERE id = 2;
COMMIT;                                  -- or ROLLBACK; to undo everything since BEGIN

SAVEPOINT before_fees;  ...  ROLLBACK TO before_fees;  RELEASE before_fees;

ACID = Atomicity, Consistency, Isolation, Durability. Isolation levels from weakest to strongest: Read Uncommitted, Read Committed (PostgreSQL/SQL Server/Oracle default), Repeatable Read (MySQL InnoDB default), Serializable (SQLite's behavior). See Transactions and Isolation Levels and Locking.

Designing tables

CREATE TABLE and constraints

SQL
CREATE TABLE IF NOT EXISTS orders (
  id          INTEGER PRIMARY KEY,                     -- auto-assigned rowid in SQLite
  customer_id INTEGER NOT NULL REFERENCES customers (id) ON DELETE CASCADE,
  code        TEXT NOT NULL UNIQUE,
  status      TEXT NOT NULL DEFAULT 'pending'
              CHECK (status IN ('pending', 'shipped', 'delivered', 'cancelled')),
  quantity    INTEGER NOT NULL CHECK (quantity > 0),
  unit_price  REAL NOT NULL,
  total       REAL GENERATED ALWAYS AS (quantity * unit_price),   -- generated column
  created_at  TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
) STRICT;                                              -- SQLite 3.37+: enforce column types

CREATE TABLE enrollments (
  student_id INTEGER REFERENCES students (id),
  course_id  INTEGER REFERENCES courses (id),
  PRIMARY KEY (student_id, course_id)                  -- composite key
);

CREATE TABLE cheap_products AS SELECT * FROM products WHERE price < 20;
PRAGMA foreign_keys = ON;                              -- SQLite enforces FKs only when enabled
Auto-numbering Syntax
SQLite id INTEGER PRIMARY KEY (add AUTOINCREMENT to never reuse ids)
PostgreSQL id INTEGER GENERATED ALWAYS AS IDENTITY (older: SERIAL)
MySQL id INT AUTO_INCREMENT PRIMARY KEY
SQL Server id INT IDENTITY(1, 1) PRIMARY KEY

See CREATE TABLE, Keys, Data Types, Auto-Increment, Constraints and Foreign Keys.

ALTER, DROP, indexes, views, triggers

SQL
ALTER TABLE customers ADD COLUMN phone TEXT;
ALTER TABLE customers RENAME COLUMN phone TO mobile;
ALTER TABLE customers DROP COLUMN mobile;               -- SQLite 3.35+
ALTER TABLE customers RENAME TO clients;
DROP TABLE IF EXISTS temp_import;

CREATE INDEX idx_orders_customer ON orders (customer_id);
CREATE UNIQUE INDEX idx_users_email ON users (email);
CREATE INDEX idx_orders_cust_date ON orders (customer_id, ordered_at);  -- leftmost columns first
CREATE INDEX idx_pending ON orders (ordered_at) WHERE status = 'pending'; -- partial index
CREATE INDEX idx_lower_email ON users (lower(email));                     -- expression index

CREATE VIEW delivered_orders AS SELECT * FROM orders WHERE status = 'delivered';

CREATE TRIGGER log_price AFTER UPDATE OF price ON products
BEGIN
  INSERT INTO price_log (product_id, old_price, new_price) VALUES (OLD.id, OLD.price, NEW.price);
END;

See ALTER TABLE and DROP TABLE, Indexes, Views and Triggers.

Modeling in one minute

  • One-to-many: the "many" side holds a foreign key (orders.customer_id).
  • Many-to-many: a junction table with two foreign keys (order_items, enrollments).
  • 1NF: one value per cell, no repeating groups. 2NF: no column depends on part of a composite key. 3NF: no column depends on another non-key column. Denormalize only for measured read performance.

See ER Modeling, Normalization and Relationships.

Performance

SQL
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 5;   -- SQLite
-- SCAN orders                        โ†’ reads the whole table
-- SEARCH orders USING INDEX ...      โ†’ uses an index
-- USE TEMP B-TREE FOR ORDER BY       โ†’ sorts without an index
EXPLAIN ANALYZE SELECT ...;           -- PostgreSQL (runs it and shows real timings)
ANALYZE;                              -- refresh statistics for the planner
  • Keep conditions sargable: ordered_at >= '2025-01-01' can use an index; strftime('%Y', ordered_at) = '2025' can't.
  • Select only the columns you need; a covering index answers a query without touching the table.
  • Avoid N+1 queries from application code: one join beats a hundred single-row lookups.
  • Indexes speed up reads but slow down every insert, update and delete.

See Reading Query Plans, Writing Fast Queries and Advanced Indexing.

Dialect differences at a glance

Feature SQLite PostgreSQL MySQL SQL Server
Limit rows LIMIT n LIMIT n LIMIT n TOP n / FETCH NEXT
Concatenate || || CONCAT() + or CONCAT()
Current time datetime('now') now() NOW() GETDATE()
Auto id INTEGER PRIMARY KEY IDENTITY / SERIAL AUTO_INCREMENT IDENTITY
Upsert ON CONFLICT ON CONFLICT, MERGE ON DUPLICATE KEY MERGE
Return changed rows RETURNING RETURNING โ€” (MariaDB: RETURNING) OUTPUT
Case-insensitive match LIKE (ASCII) ILIKE LIKE (default collation) LIKE (default collation)
String aggregate group_concat string_agg GROUP_CONCAT STRING_AGG
Boolean type 0/1 integers BOOLEAN TINYINT(1) BIT
Quote identifiers "name" "name" `name` [name]
Stored procedures no yes yes yes

See SQL Dialects, PostgreSQL, MySQL and SQL Server and Oracle.

Security in one line

Never build SQL by gluing user input into a string. Use parameters (WHERE email = ?) from your application code, and give the app's database user only the permissions it needs. See SQL Injection and Users and Permissions.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which clause is evaluated first, logically?

    The database first builds the source rows in FROM (including joins), then filters them with WHERE, groups, filters groups, computes the SELECT list and finally sorts.
  2. 2. What does SELECT 2 NOT IN (1, NULL); return?

    2 NOT IN (1, NULL) means 2 <> 1 AND 2 <> NULL. The second part is unknown, so the whole expression is NULL, and a WHERE with it keeps no rows.
  3. 3. You LEFT JOIN categories to products and add WHERE p.price < 15. What happens to categories without a cheap product?

    For unmatched categories p.price is NULL, so the WHERE condition is unknown and the row is dropped. Move the condition into the ON clause to keep them.
  4. 4. Which function gives 1, 2, 2, 3 for the salaries 90, 80, 80, 70 sorted descending?

    DENSE_RANK gives ties the same rank and doesn't skip numbers. RANK would give 1, 2, 2, 4 and ROW_NUMBER 1, 2, 3, 4.
  5. 5. How do you keep only the top-ranked row per group from a window function?

    Window functions run after WHERE and HAVING, so they can't be filtered there. Calculate them in an inner query and filter the result outside.

Summary

  • A SELECT is written as SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT but evaluated as FROM, WHERE, GROUP BY, HAVING, SELECT (with windows), ORDER BY, LIMIT.
  • NULL means unknown: test it with IS NULL, replace it with COALESCE, and prefer NOT EXISTS over NOT IN with subqueries.
  • WHERE filters rows, HAVING filters groups, and conditions on the right table of a LEFT JOIN belong in ON.
  • CTEs make long queries readable; recursive CTEs walk hierarchies and generate series.
  • Window functions add rankings, running totals and row comparisons without collapsing rows; filter them in an outer query.
  • Change data carefully: test the WHERE first, use transactions, and use ON CONFLICT for upserts.
  • Dialects differ in limits, concatenation, auto ids, upserts and dates; the standard core is the same everywhere.

Next: SQL Queries: 50 Classic Problems, where you'll put this sheet to work on the questions interviewers and real projects ask most.