1Z0-909 key terms

A named temporary result set defined with WITH before a query and usable within that statement. It can be recursive, referencing itself until a termination condition is met, and improves readability over deeply nested subqueries.

A CTE containing an anchor SELECT combined with a recursive SELECT via UNION ALL or UNION DISTINCT. It repeats until no new rows are produced, and is capped by cte_max_recursion_depth to prevent runaway loops.

A function that computes a value across a set of rows related to the current row without collapsing them, declared with OVER. Examples include ROW_NUMBER, RANK, and SUM, each respecting an optional PARTITION BY and ORDER BY.

A window specification defined once with the WINDOW clause and referenced by name in multiple OVER clauses. It reduces duplication and can be extended or overridden by later window definitions in the same query.

The ROWS or RANGE portion of a window definition that limits which rows a window function sees relative to the current row, such as ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW for running totals.

A derived table in the FROM clause marked with LATERAL that may reference columns of preceding tables in the same FROM list. It enables per-row subqueries, similar to a correlated join, without a separate subquery in the select list.

A subquery used in the FROM clause that produces a temporary table for the outer query. In MySQL 8.0 it may be merged, materialized, or, when marked LATERAL, correlated with earlier FROM items.

A table function that shreds a JSON document into relational rows and columns using a path expression and column definitions. It lets SQL joins and filters operate directly on nested JSON arrays and objects.

A string such as $.items[*].price that addresses values inside a JSON document. Paths use $ for the root, dots for members, and brackets for array indexes, and appear in JSON_EXTRACT and related functions.

A native column type storing validated JSON documents in an optimized binary format. It supports path-based extraction, in-place updates with JSON_SET, and generated columns or functional indexes for efficient querying.

An index created on an expression rather than a plain column, for example an index on a JSON extraction or a lower-cased string. It is implemented as a hidden generated column and speeds queries that use the same expression.

A column whose value is computed from an expression over other columns. VIRTUAL columns are evaluated on read, while STORED columns are written to disk and may be indexed directly.

A column marked INVISIBLE that is excluded from SELECT * output and from INSERT statements that omit a column list. It still stores data and can be queried by explicit name, aiding schema changes without breaking applications.

A table or column constraint enforcing a boolean expression on inserted or updated rows. MySQL 8.0.16 and later enforce CHECK constraints; earlier versions parsed and ignored them, so version awareness matters.

A constraint requiring values in a child column to match values in a parent key, with optional ON DELETE and ON UPDATE actions such as CASCADE or SET NULL. It enforces referential integrity and requires a suitable parent index.

A column attribute forbidding NULL values. Because NULL is unknown rather than empty, it interacts specially with comparisons, aggregates, and unique indexes, where multiple NULLs are permitted unless the column is NOT NULL.

SQL's treatment of comparisons as TRUE, FALSE, or UNKNOWN when NULL is involved. UNKNOWN rows are excluded by WHERE, so tests must use IS NULL rather than equality to find missing values.

A function returning the first non-NULL argument, or NULL if all are NULL. It is commonly used to substitute defaults for missing values and is a standard alternative to vendor-specific IFNULL.

A conditional expression returning one of several results based on WHEN conditions, in simple or searched form. It works in SELECT, WHERE, ORDER BY, and updates, enabling inline branching without procedural code.

MySQL's default transactional engine, providing row-level locking, multi-version concurrency control, foreign keys, and crash recovery through redo and undo logs. Most developer-focused exam content assumes InnoDB behavior.

A setting controlling visibility of concurrent changes, such as READ COMMITTED or REPEATABLE READ. InnoDB defaults to REPEATABLE READ, which uses consistent snapshots and can produce different results than READ COMMITTED for repeated reads.

A cycle in which two or more transactions each hold a lock the other needs. InnoDB detects the cycle and rolls back one transaction with error 1213, so applications should catch the error and retry.

A named marker inside a transaction created with SAVEPOINT and used with ROLLBACK TO to undo part of the work while keeping earlier changes. It supports partial rollback without ending the whole transaction.

A session setting that commits each statement immediately when enabled, which is the default. Developers disable it or use explicit START TRANSACTION to group statements into an atomic unit.

A named routine stored in the database and invoked with CALL, containing SQL statements and procedural logic such as loops and handlers. It can accept IN, OUT, and INOUT parameters and runs with definer or invoker privileges.

A routine returning a single scalar value and callable inside SQL expressions such as SELECT or WHERE. Unlike procedures, functions require a declared return type and are subject to restrictions on statements that modify data.

A client command changing the statement terminator, commonly to //, so that semicolons inside a stored program body do not end the CREATE statement prematurely. It is a mysql client feature, not server SQL.

A stored function attribute declaring that the function returns the same result for the same inputs. It is required knowledge for binary logging and replication safety, and affects whether the function may run when binlog trust is strict.

A stored program fired automatically before or after INSERT, UPDATE, or DELETE on a table. Triggers can access OLD and NEW row values and are useful for auditing, validation, and derived-column maintenance.

A server component executing scheduled SQL tasks defined with CREATE EVENT, either once or on a recurring interval. It must be enabled via event_scheduler, and privileges and definer context affect execution.

A stored program construct declaring how to respond to conditions such as SQLSTATE or warnings, using CONTINUE, EXIT, or UNDO. Handlers let routines recover gracefully instead of aborting on the first error.

A statement raising a user-defined error or warning with a chosen SQLSTATE and message, typically inside a stored program. It pairs with RESIGNAL, which re-raises a condition inside a handler.

A statement parsed and planned once, then executed repeatedly with different parameter values using PREPARE, EXECUTE, and DEALLOCATE. It improves reuse and separates SQL text from data, helping prevent injection.

An attack inserting malicious SQL through unsanitized input. Mitigation includes parameterized statements, strict typing, least-privilege accounts, and avoiding dynamic SQL built by string concatenation.

A statement showing the optimizer's execution plan, including join order, access type, key used, and estimated rows. EXPLAIN ANALYZE additionally runs the query and reports actual timing and row counts.

An index containing every column a query needs, so the server answers from the index alone without reading table rows. EXPLAIN reports this as Using index, indicating reduced I/O.

An index on multiple columns in a defined order. It can serve queries filtering on a leftmost prefix of those columns, so column order in the index definition strongly influences which queries benefit.

A comment-style directive such as /*+ INDEX(t idx) */ influencing plan choice without changing query semantics. Hints are advisory and version-dependent, so they should be used sparingly and retested after upgrades.