codejump
Academy

SQL

10 pages · about 58 min of reading

  1. 01JOINs, and the rows you did not expectWhat a join really computes, the difference INNER and LEFT actually make, and why filtering a LEFT JOIN in the WHERE clause silently turns it back into an INNER one.
  2. 02GROUP BY and HAVINGThe order the clauses actually run in, why WHERE and HAVING are not interchangeable, and what COUNT(*) counts that COUNT(column) does not.
  3. 03NULL, and the rows it quietly dropsWhy NULL = NULL is not true, which operators quietly swallow rows because of it, and the handful of functions that give you an answer back.
  4. 04Subqueries: IN, EXISTS and correlatedThe three places a subquery can sit, what makes one correlated, and why EXISTS is the safe default where IN has a NULL problem.
  5. 05Window functions: keeping every rowHow OVER keeps your rows while still computing across them, what PARTITION BY and ORDER BY each change, and the frame clause that decides a running total.
  6. 06CTEs and recursive queriesWhat WITH buys beyond readability, whether a CTE is a fence the planner will not cross, and how a recursive one walks a tree without a loop in application code.
  7. 07Indexes: what a B-tree cannot doWhy column order in a composite index decides which queries it serves, what makes a condition unindexable, and the reason an index sometimes makes things slower.
  8. 08Transactions and isolation levelsWhat ACID actually promises, which anomalies each isolation level still allows, and why the read-then-write you wrote is a race unless you said otherwise.
  9. 09JSON in a relational databaseReading a field out of a JSON column, turning an array into rows, and the question worth asking first — whether this should have been a column.
  10. 10DISTINCT, UNION and set operationsWhat deduplication actually costs, the difference between UNION and UNION ALL, and why DISTINCT in a query is often a join that went wrong.