Question 1

Shape a simple read from one table

Accepted Answer

Depending on your case, the options are: SELECT named columns; SELECT *; WHERE; DISTINCT; ORDER BY with LIMIT. The animated Reading rows decision tree on this page walks every route and shows which one fits, with a SQL example for each.

Question 2

Bring two tables together the right way

Accepted Answer

Depending on your case, the options are: UNION ALL; UNION; INNER JOIN; LEFT JOIN; Anti join; FULL OUTER JOIN. The animated Combining tables decision tree on this page walks every route and shows which one fits, with a SQL example for each.

Question 3

Collapse rows into the numbers that matter

Accepted Answer

Depending on your case, the options are: Window functions; Aggregate functions; WHERE, then GROUP BY; HAVING; WITH ROLLUP. The animated Summarising decision tree on this page walks every route and shows which one fits, with a SQL example for each.

Question 4

Compute across rows without collapsing them

Accepted Answer

Depending on your case, the options are: ROW_NUMBER(); RANK(); DENSE_RANK(); LAG() and LEAD(); SUM() OVER (ORDER BY); PARTITION BY. The animated Window functions decision tree on this page walks every route and shows which one fits, with a SQL example for each.

Question 5

Keep a growing query readable and reusable

Accepted Answer

Depending on your case, the options are: VIEW; Materialized view; WITH RECURSIVE; CTE with WITH; Subquery. The animated Query structure decision tree on this page walks every route and shows which one fits, with a SQL example for each.

Question 6

Insert, update, upsert or remove rows safely

Accepted Answer

Depending on your case, the options are: INSERT; UPSERT; UPDATE with WHERE; DELETE with WHERE; TRUNCATE. The animated Changing data decision tree on this page walks every route and shows which one fits, with a SQL example for each.

Question 7

Reshape a single value inside a query

Accepted Answer

Depending on your case, the options are: COALESCE; IS NULL and NULLIF; CASE; String functions; Numeric and date functions. The animated Value functions decision tree on this page walks every route and shows which one fits, with a SQL example for each.

Question 8

Make lookups fast without slowing writes

Accepted Answer

Depending on your case, the options are: FULLTEXT index; No index; Covering index; Composite index; Single column index. The animated Indexing decision tree on this page walks every route and shows which one fits, with a SQL example for each.

Question 9

Follow EXPLAIN to the real bottleneck

Accepted Answer

Depending on your case, the options are: Add the missing index; Unhide the column; Let an index do the sorting; Fetch less, paginate by key; Batch the N plus one loop. The animated Slow queries decision tree on this page walks every route and shows which one fits, with a SQL example for each.

Question 10

Keep multi step changes safe and automatic

Accepted Answer

Depending on your case, the options are: Transaction; Trigger; Stored function; Stored procedure. The animated Transactions decision tree on this page walks every route and shows which one fits, with a SQL example for each.

Question 11

Pick the column type that will not bite later

Accepted Answer

Depending on your case, the options are: DECIMAL; INT or BIGINT; DOUBLE; TIMESTAMP; DATETIME or DATE; VARCHAR; TEXT; JSON. The animated Data types decision tree on this page walks every route and shows which one fits, with a SQL example for each.

Question 12

Almost always InnoDB, except when it is not

Accepted Answer

Depending on your case, the options are: InnoDB; MEMORY; ARCHIVE; InnoDB, still. The animated Storage engine decision tree on this page walks every route and shows which one fits, with a SQL example for each.

MySQL decision trees.

Shape a simple read from one table

SELECT named columns

Want the skills behind the maps?