1Z0-909 Cheatsheet — MySQL 8.0 Developer (SQL, DDL, Indexes, Stored Programs)

Use this for last‑mile review. The exam rewards precise SQL semantics and the ability to predict results.

Query semantics

NULL is not “a value”

• col = NULL is never true; use IS NULL.
• NOT IN + NULLs can surprise you; prefer NOT EXISTS when NULLs are possible.

1SELECT * FROM t WHERE col IS NULL;

JOIN vs WHERE filtering (classic trap)

If you filter the right side of a LEFT JOIN in the WHERE clause, you often turn it into an INNER JOIN.

1SELECT a.id, b.status
2FROM a
3LEFT JOIN b ON b.a_id = a.id AND b.status = 'ACTIVE';

DDL essentials

Keys and constraints (concept-level)

• Primary key: uniqueness + not null.
• Foreign keys enforce referential integrity (and can affect delete/update behavior).
• Unique constraints + indexes work together for both correctness and performance.

Generated columns + indexing JSON (concept-level)

For JSON fields, you often index a generated column that extracts the attribute you query frequently.

Indexing and EXPLAIN

When indexes help

• Highly selective predicates (WHERE customer_id = ?)
• Join keys
• ORDER BY with LIMIT (when it can use an index)

When indexes don’t help much

• Low-cardinality columns (depends on workload)
• Predicates with functions on the column (WHERE YEAR(created_at)=2025 is often non-sargable)

EXPLAIN quick read

1EXPLAIN SELECT * FROM orders WHERE customer_id = 123;
2SHOW INDEX FROM orders;

High-yield reminder: if you see full table scans unexpectedly, check predicate sargability, data types, and missing indexes.

Transactions and locking (concept-level)

• Use transactions to group changes.
• Isolation impacts visibility; locking impacts concurrency.
• Keep transactions short to reduce lock contention.

1START TRANSACTION;
2UPDATE accounts SET balance = balance - 10 WHERE id = 1;
3UPDATE accounts SET balance = balance + 10 WHERE id = 2;
4COMMIT;

Stored programs

Views

1CREATE VIEW active_users AS
2SELECT id, email FROM users WHERE active = 1;

Stored procedures (concept-level)

• Know how parameters and control flow work, and the operational trade-offs (debugging, deployment, permissions).

Triggers (concept-level)

• Triggers can enforce invariants but can also hide work and complicate debugging and performance.

Events (concept-level)

• Scheduler-driven tasks inside MySQL (be able to recognize use cases and risks).

JSON essentials (MySQL 8.0)

1SELECT JSON_EXTRACT(payload, '$.customer.id') AS customer_id
2FROM events;

Know the difference between JSON extraction for query and for indexing (often via generated columns).

Quick glossary (developer)