SQL Fundamentals Interview Questions

CHAR pads to a declared width with spaces; VARCHAR stores a length prefix and only the bytes used. Short, truly fixed codes such as CHAR(2) ISO country codes waste almost nothing in the clustered row. utf8mb4 CHAR(n) reserves n characters (up to 4n bytes), so a wide CHAR in utf8mb4 bloats InnoDB pages. Prefer VARCHAR for anything whose length actually varies.

TIMESTAMP is stored as UTC and converted using session time_zone; DATETIME is a wall-clock value with no zone. DEFAULT CURRENT_TIMESTAMP / ON UPDATE CURRENT_TIMESTAMP apply to TIMESTAMP (and to DATETIME in 5.6+). Older TIMESTAMP storage still hits the 2038 limit; DATETIME does not. Pick DATETIME for business dates that must not shift when a replica session time_zone differs.

AUTO_INCREMENT is a table-level counter InnoDB persists with the clustered index; rolled-back values are not reused, so gaps appear. innodb_autoinc_lock_mode=2 (interleaved) is required for row-based replication throughput but makes consecutive values non-monotonic under concurrent INSERT. On a replica, mixing statement-based INSERT with auto-inc is a classic desync.

Implicit CAST in WHERE turns '10abc' into 10, so a VARCHAR primary-key lookup can become a type-mismatch scan. Comparing an INT column to a quoted string (or the reverse) likewise blocks B-tree ref access. EXPLAIN type=ALL on a tiny table is the giveaway. Bind parameters with the same type as the column so InnoDB can use the clustered or secondary lookup.

STRICT_TRANS_TABLES plus ERROR_FOR_DIVISION_BY_ZERO make MySQL reject truncated INSERTs and invalid dates instead of warning and storing garbage. Legacy apps that relied on silent truncation break overnight. TRADITIONAL is a bundle of these sql_mode flags. Check @@sql_mode on every replica—statement-based events can behave differently if modes diverge.

OFFSET pagination re-scans and discards skipped rows, so page 10000 of LIMIT 20 OFFSET 200000 still walks 200020 index entries. Keyset pagination (WHERE id > :last ORDER BY id LIMIT 20) uses the clustered primary key as a range. Mixing OFFSET with an unordered SELECT makes pages unstable under concurrent inserts. A covering index on the ORDER BY columns still cannot skip the discard work.

LEFT JOIN preserves left-side rows even when the right side is all NULL; putting a right-table filter in WHERE turns it into an inner join. Predicates such as t2.status = 'A' belong in ON if you meant outer. InnoDB nested-loop still uses an index on the inner table when ON columns are indexed. Join keys should be NOT NULL unless the domain allows missing matches.

utf8mb4 plus a collation such as utf8mb4_0900_ai_ci decides equality for JOIN and UNIQUE indexes. Mixing utf8mb3 and utf8mb4 on join columns forces a conversion and can disable the index on one side. Case-insensitive collations make A and a collide in a UNIQUE KEY. Convert tables with ALGORITHM=INPLACE where possible and keep character_set_client aligned with the table.

Binary prepared statements send the SQL text once and bind typed parameters, which blocks injection and lets the server cache a handle. Concatenating quoted strings still hits the parser every time and is the classic injection path. COM_STMT_EXECUTE also skips extra charset conversions when types match. Connection pools must reset sessions so leftover user variables do not leak into the next checkout.

Window functions such as ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) keep detail rows while ranking, which GROUP BY cannot do without a self-join. They still filesort unless there is an index on (user_id, created_at). Framing (ROWS versus RANGE) changes which peers are included. Mixing DISTINCT with windows often forces an extra temp table.

NO_ZERO_DATE and NO_ZERO_IN_DATE under strict sql_mode reject '0000-00-00', which legacy TIMESTAMP defaults used. Existing InnoDB rows already stored zeros remain until rewritten. Reloading that dump under STRICT_TRANS_TABLES fails unless you strip zeros or relax sql_mode for the load. Plan the rewrite before promoting a replica with a stricter mode.

Wrapping a column in a function (DATE(created_at) = CURDATE()) disables the B-tree range on created_at. Rewrite as created_at >= CURDATE() AND created_at < CURDATE() + INTERVAL 1 DAY so the clustered or secondary index can use type=range. A generated column with its own index is the 5.7+ escape for expressions you must persist. Check Extra for Using index condition versus a full Using where.

Three-valued logic is the trap: any comparison with NULL yields UNKNOWN, not TRUE, so the WHERE clause drops the row. Write col IS NULL or IS NOT NULL instead. InnoDB stores NULL as a distinct marker from empty string, and a UNIQUE index still allows multiple NULLs because NULL is not equal to NULL.

COUNT(*) on InnoDB still walks a secondary covering index when one exists; there is no stored table row count like MyISAM. A WHERE that cannot use an index becomes a clustered-index scan. COUNT(col) skips NULLs, so it disagrees with COUNT(*) on nullable columns. EXPLAIN Extra: Using index is the covering-index fast path.

UNION ALL streams every row as produced; UNION adds a distinct sort that often builds a temporary table and filesort. If the two SELECTs are already disjoint (active vs archive tables), UNION ALL is the default. Mixing column types still forces conversion. Interviewers notice people UNION two huge InnoDB results just to glue pages together.

ONLY_FULL_GROUP_BY (default since 5.7) rejects SELECT columns that are neither aggregated nor in GROUP BY, which used to return an arbitrary InnoDB row. ANY_VALUE() is the explicit escape hatch. Functional dependency on a unique key is allowed. Turning the mode off to make a query work hides bugs that surface when clustered-index order changes.

Derived tables in FROM are materialized unless the optimizer can merge them; wrapping SELECT * FROM (SELECT ...) t often blocks index use on the inner InnoDB table. DISTINCT or aggregates still materialize in 8.0. A correlated subquery in WHERE runs per outer row unless rewritten as a JOIN. EXPLAIN FORMAT=JSON shows materialized versus merged.

NOT IN (subquery) becomes UNKNOWN for the whole predicate when any subquery row is NULL, so the outer query can return zero rows. NOT EXISTS or a LEFT JOIN ... WHERE t2.id IS NULL does not have that three-valued trap. InnoDB can use an anti-join for NOT EXISTS. Declare join keys NOT NULL when the business forbids missing keys.

Join planning uses nested-loop plus hash join in 8.0; STRAIGHT_JOIN or JOIN_FIXED_ORDER freezes order when histogram stats are wrong. Older 5.7 showed Extra: Using join buffer for block nested-loop. EXPLAIN type and rows multiply across tables; a 1e6 times 1e5 estimate is the smell. Rebuild statistics and indexes before hinting.

DECIMAL(p,s) is exact packed BCD; FLOAT and DOUBLE are IEEE binary and will not round-trip 0.1. SUM() of FLOAT invoices drifts; DECIMAL does not. InnoDB stores DECIMAL compactly in the clustered row. Pick precision from the domain (for example DECIMAL(13,4)) and never CAST money through DOUBLE inside a stored procedure.