Practical SQL Guides
Each guide now shows actual results or a checkable impact metric and labels correctness, efficiency, safety, and clarity improvements separately.
SQL clause order and logical execution
Understand the logical order of FROM, WHERE, GROUP BY, HAVING, SELECT, and ORDER BY.
Why NULL and three-valued logic cause errors
NULL is not an empty string, and comparisons with NULL can be UNKNOWN.
The real difference between WHERE and HAVING
WHERE filters source rows; HAVING filters groups after aggregation.
Why JOIN creates duplicates or missing rows
Diagnose JOIN row-count changes through result grain, key uniqueness, and unmatched rows.
LEFT JOIN filters in ON versus WHERE
The location of a right-table predicate determines whether unmatched left rows remain.
COUNT(*), COUNT(column), and COUNT DISTINCT
The three forms answer row count, non-NULL value count, and unique-value count.
Common GROUP BY errors and debugging
Use result grain to catch missing and excessive grouping, including MySQL ONLY_FULL_GROUP_BY errors.
Why deep OFFSET pagination gets slower
Compare OFFSET and keyset pagination by scan work, stable ordering, and behavior under data changes.
A safe workflow for UPDATE and DELETE
Verify scope first, modify inside a transaction, and inspect affected rows.
How to read and locate SQL errors
Start from the error type and parser stop position, then reduce the query to a minimal working statement.
Date ranges, month ends, and time boundaries
Use half-open ranges for days and months to avoid missing fractional seconds and preserve index opportunities.
Reading a query plan with EXPLAIN
Read MySQL 8 EXPLAIN through access type, indexes, estimated rows, and extra work, then verify the change.
The NOT IN NULL trap: why a result becomes empty
Use three-valued logic to explain empty NOT IN results and write reliable anti-joins with NOT EXISTS.
Top 1 per group: why a global LIMIT is not enough
Implement Top 1 or Top N per group with window functions and explicit rules for ties and row counts.
Conditional aggregation: calculate several metrics in one pass
Replace repeated scalar subqueries with SUM(CASE WHEN) to centralize metric definitions.
Latest row per entity: DISTINCT is not ordering
Use a partitioned window to define which complete row is the latest for each customer.