- MySQL – BETWEEN
BETWEEN as shorthand for two comparisons, and the two things that catch people out: it is inclusive at both ends, and on a DATETIME column `BETWEEN '2024-01-01' AND '2024-01-31'` silently drops almost the whole of the 31st. NOT BETWEEN, BETWEEN on strings, and the half-open `>= ... < ...` form that is the right answer for dates.
- LeetCode 34 – Find First and Last Position of Element in Sorted Array
Plain binary search finds an occurrence; this wants the first and the last, and expanding outwards from a hit is O(n) the moment the array is all one value. The clean answer is one primitive — lower bound — called twice, with target + 1 giving the second answer. Why the window is half-open, and why removing the equality test removes the bugs.
- MySQL – NULL, IS NULL and ISNULL()
NULL is not a value, it is the absence of one, and every surprising thing about it follows from that. IS NULL and IS NOT NULL, the ISNULL() function, IFNULL() and COALESCE(), why `= NULL` is always false, how NULL behaves in aggregates and in GROUP BY, and the nullable column the pizza schema uses on purpose — `user_id` is NULL exactly when the order was placed by a guest.
- MySQL – WHERE
Filtering rows. Comparison operators, AND / OR / NOT and the precedence rule that makes parentheses worth typing, IN and NOT IN, filtering on a boolean column, and the trap that silently returns nothing: comparing to NULL with `=` instead of IS NULL. Also why wrapping a column in a function in the WHERE clause quietly disables the index on it.
- MySQL – SELECT
The statement you will write more than all the others combined. Choosing columns instead of SELECT *, and why the star is a habit worth losing, column and table aliases, DISTINCT, expressions and arithmetic in the select list, the order MySQL actually evaluates a query in — which is not the order you type it — and why that explains half the errors beginners hit.