SQL Help
Between 2006 and 2009 these tutorials lived at guelphdad.wefixtech.co.uk/sqlhelp, written by David J. Lake — a familiar, patient voice on the MySQL forums of that era. Forum threads all over the web still link here. Rather than let those links rot, we've rewritten every tutorial from scratch for modern MySQL 8 and put each one back at its original address. Same questions — they never stopped being asked — with today's answers.
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.
- Gaps in AUTO_INCREMENT sequences — why they happen and why you should leave them alone.
Schema design & data cleanup
- Stop storing comma-separated lists — normalisation with a junction table, and searching it properly.
- Convert VARCHAR dates to a proper DATE column —
STR_TO_DATEand a safe migration workflow. - 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 sub-categories with PHP and MySQL — one query, no N+1 loops.
The lost articles
The 2006 sidebar carried a hidden comment listing tutorials David planned but never published. We're finishing the set:
- 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.