SQL Help
Based on the SQL Help series David J. Lake published here in 2006 to 2009. Rewritten for MySQL 8 by the WeFixTech editorial team. The tutorials stay at their original addresses, which old forum threads still link to.
Each rewrite keeps the original question and updates the SQL for MySQL 8, including window functions and the replacements for SQL_CALC_FOUND_ROWS.
Choosing a SQL book
- Best SQL books for beginners, MySQL-friendly picks, plus PostgreSQL and SQL Server options.
Retrieving the rows you actually want
- LEFT JOIN vs INNER JOIN: the difference, explained, plus RIGHT and FULL OUTER joins, chaining LEFT JOINs, and the anti-join trick.
- Get the latest row per group, the classic "greatest-n-per-group" problem, solved with window functions and the old correlated-subquery way.
- SQL_CALC_FOUND_ROWS is deprecated: paginating with a total count, the modern replacements, and keyset pagination.
- Top N results plus an "everything else" total, league tables with a "rest of world" row.
- Find free rooms: booking availability queries, the overlap test every reservation system needs.
Keys, IDs and duplicates
- AUTO_INCREMENT and LAST_INSERT_ID(): inserting related rows safely, why "SELECT MAX(id)+1" is a race condition, and what to do instead.
- Delete duplicate rows in MySQL, safely, with window functions or a self-join.
- Why are there gaps in my IDs?, why AUTO_INCREMENT sequences skip numbers, and why to leave them.
Schema design & data cleanup
- Stop storing comma-separated lists, normalisation with a junction table, and searching it properly.
- Convert VARCHAR to DATE in MySQL with STR_TO_DATE, a format table, a migration checklist, and CAST for ISO dates only.
- Create and fill a calendar table, recursive CTEs (and the classic cross-join trick).
- INT(11) doesn't mean what you think, display width, ZEROFILL and the real integer ranges.
Working with the mysql client
- Save query output to a file with column headers
- Transform data as you import it with LOAD DATA
- Categories and Subcategories in PHP and MySQL: One Query, No N+1, one round-trip with a LEFT JOIN or WITH RECURSIVE.
More MySQL topics
These tutorials were written for the archive by the WeFixTech editorial team.
- MySQL transactions: all-or-nothing changes, START TRANSACTION, COMMIT, ROLLBACK, and the locking basics.
- NULL vs empty string: they are not the same, three-valued logic without the tears.
- Stored procedures: a practical introduction, how to write them, and an honest take on when to.