browse/
15 results
databases/sql-window-functions-how-every-row-gets-a-rank-or-running-total-withou.md
SQL window functions: how every row gets a rank or running total without collapsing the table
A window function adds a calculated column to every row without collapsing rows the way GROUP BY does. Learn what OVER, PARTITION BY and…
databases/pagination-why-next-page-shows-a-post-twice-and-how-a-bookmark-fixes-i.md
Pagination: why 'next page' shows a post twice, and how a bookmark fixes it
Why does "next page" sometimes show the same post twice, and why does page 90,000 load slowly? Both come from paging with LIMIT and OFFSET…
databases/caching-why-the-second-read-is-fast-and-how-a-copy-goes-stale.md
Caching: why the second read is fast, and how a copy goes stale
Why does a page load slowly the first time and fast the second? Usually a cache: a quick copy of an answer the database already gave. Run a…
databases/the-n-1-query-problem-why-a-page-that-asks-the-database-501-questions.md
The N+1 query problem: why a page that asks the database 501 questions is slow
A page that lists authors and their books can quietly send one query per author. With 500 authors that is 501 trips to the database, and…
databases/transactions-why-a-money-transfer-must-save-both-changes-or-neither.md
Transactions: why a money transfer must save both changes or neither
Sending £50 between two accounts takes two changes to a database. If the power fails between them, the money vanishes. A transaction groups…
security/sql-injection-how-a-quote-mark-in-a-login-form-can-rewrite-the-databas.md
SQL injection: how a quote mark in a login form can rewrite the database's question
A login form builds a database question out of the words you type. If it glues your text straight into the question, your text can rewrite…
databases/sql-indexes-why-finding-one-row-among-200-000-takes-about-18-steps.md
SQL indexes: why finding one row among 200,000 takes about 18 steps
A database with 200,000 customers can answer "find this email" by reading every row, or by reading about 18 entries. The difference is an…
databases/sql-group-by-how-many-rows-become-one-row-per-customer.md
SQL GROUP BY: how many rows become one row per customer
A shop's orders table has 8 rows, but the owner wants one line per customer. GROUP BY sorts rows into piles by a column's value, then an…
databases/sql-null-why-two-people-vanish-from-both-your-queries.md
SQL NULL: why two people vanish from both your queries
Two people have no age in your table. You ask for everyone older than 30, then for everyone not older than 30, and the two lists add up to…
databases/sql-joins-how-two-tables-become-one-answer-inner-left-right-full.md
SQL JOINs: how two tables become one answer (INNER, LEFT, RIGHT, FULL)
Your data lives in two tables, but your question needs both. A JOIN lines them up, and the four kinds (INNER, LEFT, RIGHT, FULL) differ…
databases/database-transactions-why-moving-money-is-all-or-nothing.md
Database transactions: why moving money is all or nothing
Paying someone is really two changes in a database: take money from one account, add it to another. If the power dies between them, money…
security/sql-injection-how-a-stray-quote-mark-turns-data-into-instructions.md
SQL injection: how a stray quote mark turns data into instructions
A search box looks harmless, but if a program builds its database question by gluing text together, a stray quote mark can turn a visitor's…