Why a bank transfer needs more than two UPDATE statements Move $50 from one account to another and the naive version is two statements: subtract from account A, add to account B. Run them separately, and if the process crashes or the connection drops between the two, the money is gone from A and never arrives in B. The database did exactly what it was told, twice. The business still lost $50. ...
A wrong JOIN doesn鈥檛 throw an error. It runs, returns a result set, and the rows are wrong: customers missing, totals inflated, orphaned records hidden. Most of the time the query asked for exactly what it got, and the author picked the wrong join type or filtered in the wrong place. A join combines rows from two tables on a condition you write. Which rows survive depends on the join type, and the six types below give six different answers from the same two tables. ...
On a million-row table, the same SELECT can take fifty milliseconds or well under one. The only difference is whether the database reads every row to find the ones you asked for, or jumps straight to them. An index is what lets it jump, and you pay for it on every write. Concretely, an index is a second, sorted copy of one or more columns, stored next to the table and updated automatically whenever the table changes. The database uses it to reach matching rows without touching the rest. ...