The Curious Engineer

All blogs / Databases

EXPLAIN EXTENDED

explainextended.com

How to create fast database queries

Databases

’10’15’20’25
Posts
275 from 28 Feb 2009 to 31 Dec 2025
Posting
now and then (1 in the last year)
Kind
personal blog
Feed
https://explainextended.com/feed/

2025 1

  1. Happy New Year: Lisp interpreter in SQL

2024 1

  1. Happy New Year: Diffusion Model image generator in about 700 lines of pure SQL

2023 1

  1. Happy New Year: GPT in 500 lines of SQL

2022 2

  1. Happy New Year: solving the Rubik’s Cube in SQL
  2. A good first word for Wordle

2021 1

  1. Happy New Year: quantum computer emulator in SQL

2020 1

  1. Happy New Year: 3D picture of the coronavirus in SQL

2019 1

  1. Happy New Year: a stereogram in SQL

2018 1

  1. Happy New Year: GIF decoder in SQL

2017 1

  1. Happy New Year: Settlers of Catan in SQL

2016 1

  1. Happy New Year: the Sultan’s Riddle in SQL

2015 1

  1. Happy New Year: Conway’s Game of Life in SQL

2014 3

  1. Happy New Year: composing music in SQL
  2. SQL Server: deleting with self-referential FOREIGN KEY, handling loops
  3. Top 5 XKCD comics which can illustrate programming questions

2013 10

  1. Happy New Year: drawing fractals in SQL
  2. 5 Claims About SQL, Explained
  3. NULL in SQL: explaining its behavior
  4. Did You Know You Could Index SUM() and COUNT(): SQL Server Indexed Views, Part 3
  5. Storing a Friendship: SQL Server Indexed Views, Part 2
  6. 10 things in SQL Server (which don’t work as expected)
  7. Score charts, friendship lists and unique multiple columns: easy with SQL Server’s indexed views
  8. SQL Server: joining NULL values
  9. INSERT … SELECT … OUTPUT …: returning source table data
  10. Empty row if condition does not match

2012 1

  1. Happy New Year: view of Earth from space in SQL

2011 7

  1. Happy New Year: drawing snowflakes in SQL
  2. What’s UNPIVOT good for?
  3. Shared Plan and Algorithm Network Cache (SPANC)
  4. MySQL: GROUP BY in UNION
  5. MySQL: splitting aggregate queries
  6. Things SQL needs: SERIES()
  7. Late row lookups: InnoDB

2010 41

  1. Happy New Year: drawing clock in SQL
  2. PostgreSQL: parametrizing a recursive CTE
  3. 10 things in MySQL (that won’t work as expected)
  4. Mixed ASC/DESC sorting in MySQL
  5. Correlated LEFT JOIN in a subquery
  6. OLAP in MySQL: four ways to filter on higher level dimensions
  7. 20 latest unique records
  8. Questions
  9. Indexing for ORDER BY / LIMIT
  10. GROUP_CONCAT in SQL Server
  11. LEFT JOIN / IS NULL vs. NOT IN vs. NOT EXISTS: nullable columns
  12. Things SQL needs: determining range cardinality
  13. MAX and MIN on a composite index
  14. Things SQL needs: MERGE JOIN that would seek
  15. Groups holding highest ranked items
  16. Hierarchical query in MySQL: limiting parents
  17. Date ranges: overlapping with priority
  18. Multiple attributes in a EAV table: GROUP BY vs. NOT EXISTS
  19. Nosql
  20. Bayesian classification
  21. Greatest N per group: dealing with aggregates
  22. Aggregates and LEFT JOIN
  23. SQL Server: deleting with self-referential FOREIGN KEY
  24. PostgreSQL: using recursive functions in nested sets
  25. PostgreSQL: nested sets and R-Tree
  26. Six degrees of separation
  27. Efficient circle distance testing
  28. Matching sets: aggregates vs. first miss
  29. Sargability of monotonic functions: example
  30. Things SQL needs: sargability of monotonic functions
  31. Tags in nested sets: efficient indexing
  32. SQL Server: EXCEPT ALL
  33. Index search time depends on the value being searched
  34. Searching for arbitrary portions of a date
  35. Join on overlapping date ranges
  36. Aggregates: subqueries vs. GROUP BY
  37. Counting concurrent sessions
  38. SQL Server: running totals
  39. Cumulative values
  40. Calculating mode
  41. Latest visitors

2009 201

  1. Happy New Year!
  2. Grouping continuous ranges
  3. Linked lists in MySQL: multiple ordering
  4. Oracle: joining timestamps and time intervals
  5. Oracle: Selecting records holding group-wise maximum
  6. SQL Server: Selecting records holding group-wise maximum (with ties)
  7. SQL Server: Selecting records holding group-wise maximum
  8. PostgreSQL: Selecting records holding group-wise maximum
  9. MySQL: Selecting records holding group-wise maximum (resolving ties)
  10. MySQL: Selecting records holding group-wise maximum (on a unique column)
  11. Recursive CTE’s: PostgreSQL
  12. SQL Server: are the recursive CTE’s really set-based?
  13. Selecting most relevant row
  14. Inverting date ranges: MySQL
  15. SQL Server: clustered index and ordering
  16. MySQL: average of sums using indexes
  17. Inverting date ranges
  18. MySQL ORDER BY / LIMIT performance: late row lookups
  19. Generating row placeholders: SQL Server
  20. What is entity-relationship model?
  21. Constraints and the optimizer in SQL Server: FOREIGN KEY
  22. Constraints and the optimizer in SQL Server: PRIMARY KEY and UNIQUE
  23. Running root mean square: SQL Server
  24. Minimum and maximum on bit fields: SQL Server
  25. Oracle: nested SUM
  26. IN list vs. range condition: MySQL
  27. Shuffling rows: PostgreSQL
  28. Date range with possible NULL values
  29. Bitwise operations and indexes
  30. IN vs. JOIN vs. EXISTS: Oracle
  31. Adjacency list vs. nested sets: MySQL
  32. Adjacency list vs. nested sets: Oracle
  33. Adjacency list vs. nested sets: SQL Server
  34. Adjacency list vs. nested sets: PostgreSQL
  35. Building alphabetical index
  36. Finding sets within a time frame
  37. Rounding numbers preserving their sum
  38. NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: MySQL
  39. NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: Oracle
  40. NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: PostgreSQL
  41. NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: SQL Server
  42. MySQL: emulating ROW_NUMBER with multiple ORDER BY conditions
  43. SQL Server 2005: finding foreign keys that have no index
  44. Oracle: generating a list of dates and counting ranges for each date
  45. MySQL: matching whole sets
  46. PostgreSQL: selecting items that belong to all categories
  47. SQL Server: leading wildcard match using an index
  48. What is a relational database?
  49. SQL Server: finding date closest to a birthdate
  50. SQL Server: efficient DISTINCT on dates
  51. MySQL: difference between sets
  52. Passing parameters in MySQL: IN list vs. temporary table
  53. Hierarchical queries in MySQL: VARCHAR keys
  54. Presentation layer
  55. Oracle: updating rows with partitioned incremental value
  56. Efficient date range query: SQL Server
  57. Fallback language names: MySQL
  58. Fallback language names: PostgreSQL
  59. Fallback language names: SQL Server
  60. Fallback language names: Oracle
  61. MySQL: LIMIT on LIMIT
  62. SQL Server: deleting adjacent values (improved)
  63. SQL Server: deleting adjacent values
  64. MySQL: DISTINCT on DISTINCT
  65. MySQL: counting items in pairs
  66. PostgreSQL: making duplicate records unique
  67. PostgreSQL 8.4: efficient MIN(COUNT(*))
  68. SQL Server: random records avoiding CTE reevaluation
  69. Dynamic pivot
  70. MySQL: selecting rows before and after filtered one
  71. MySQL: grouping continuous ranges
  72. PostgreSQL: last 12 weeks of a year
  73. Hierarchial queries in MySQL: identifying trees
  74. PostgreSQL: round-robin order
  75. Hierarchical data in MySQL: parents and children in one query
  76. PostgreSQL 8.4: sampling random rows
  77. PostgreSQL 8.4: preserving order for hierarchical query
  78. INNER JOIN vs. CROSS APPLY
  79. Oracle: OR on multiple EXISTS clauses
  80. Flattening timespans: PostgreSQL 8.4
  81. SQL Server: aggregate bitwise OR
  82. Double-thinking in SQL
  83. Selecting compatible articles
  84. Selecting birthdays
  85. Finding duplicates on either of the fields
  86. Selecting last forum posts
  87. Overlapping numbers
  88. Schema junk
  89. Searching for NULL columns
  90. Overlapping ranges: PostgreSQL
  91. Overlapping ranges: Oracle
  92. Overlapping ranges: MySQL
  93. Overlapping ranges: SQL Server
  94. The Island of Misfit Cursors
  95. Efficient INSERT ON DUPLICATE KEY UPDATE
  96. Included columns
  97. Building permutations: SQL Server
  98. Constant vs. NULL to mark missing values in OUTER JOINs
  99. IN with a comma separated list: SQL Server
  100. Indexing tiny tables
  101. Click
  102. NVL vs. COALESCE
  103. Ordering NULLs
  104. Counting bans
  105. Efficient EXISTS
  106. IN vs. JOIN vs. EXISTS
  107. Group based identity
  108. Flattening timespans: PostgreSQL
  109. Flattening timespans: MySQL
  110. Flattening timespans: Oracle
  111. Flattening timespans: SQL Server
  112. Joining unjoinable
  113. Latest DISTINCT records: efficient subquery
  114. Latest DISTINCT records
  115. Longest common prefix: MySQL
  116. Longest common prefix: Oracle
  117. Longest common prefix: PostgreSQL
  118. Longest common prefix: SQL Server
  119. Selecting records unique on each column
  120. Distinct pairs
  121. Numbering groups
  122. Aggregate AND
  123. Hierarchical queries in PostgreSQL
  124. Generating XML in subqueries
  125. Converting currencies
  126. Selecting options
  127. Genealogy: finding a descendant
  128. Genealogy query on both parents
  129. Ordering columns independently
  130. Choosing column order
  131. Oracle: matching SYS_GUID’s
  132. Finding loops in a tree
  133. Ranging random values
  134. UNION vs UNION ALL
  135. Ordering UNION’s
  136. PostgreSQL: selecting a function
  137. Oracle: returning deleted records
  138. Indexing a link table
  139. PostgreSQL: emulating ROW_NUMBER
  140. Creating indexes
  141. Selecting first letters
  142. Checking event dates
  143. Oracle: ROW_NUMBER vs ROWNUM
  144. PostgreSQL: row numbers
  145. Assigning people
  146. PostgreSQL: optimizing DISTINCT
  147. GROUP_CONCAT in PostgreSQL without aggregate functions
  148. Indexing VARCHAR(MAX)
  149. Rating movies
  150. Selecting N records for each group: PostgreSQL
  151. Descending indexes
  152. Keeping latest rows for a group
  153. Selecting timestamps for a time zone
  154. Selecting lowest value
  155. Making an index UNIQUE
  156. Counting missing rows: PostgreSQL
  157. Counting missing rows: Oracle
  158. Counting missing rows: SQL Server
  159. Counting missing rows: using DISTINCT
  160. Counting missing rows
  161. Matching 3 of 4
  162. Missing entries
  163. Keeping rows
  164. Emulating FULL OUTER JOIN in MySQL
  165. GROUP_CONCAT in Oracle 10g
  166. Banning IP’s
  167. Endless loops
  168. Emulating SKIP SCAN
  169. Choosing index
  170. Matching whole sets
  171. Sorting lists: deleting blocks
  172. Sorting lists: moving blocks
  173. Sorting lists: deleting items
  174. Sorting lists: adding items
  175. Sorting lists: moving items
  176. Sorting lists
  177. Article-aware title filtering: internationalization
  178. Article-aware title filtering
  179. Selecting non-unique rows
  180. Hierarchical queries in MySQL: finding loops
  181. Hierarchical queries in MySQL: finding leaves
  182. Hierarchical queries in MySQL: adding ancestry chains.
  183. Hierarchical queries in MySQL: adding level
  184. Hierarchical queries in MySQL
  185. Counting page views
  186. Aggregated hours
  187. Deleting duplicates
  188. Finding incomplete orders
  189. Analytic functions: optimizing LAG, LEAD, FIRST_VALUE, LAST_VALUE
  190. Analytic functions: optimizing SUM and ROW_NUMBER
  191. Analytic functions: FIRST_VALUE, LAST_VALUE, LEAD, LAG
  192. Analytic functions: NTILE
  193. Analytic functions: SUM, AVG, ROW_NUMBER
  194. Selecting friends
  195. Advanced row sampling
  196. Row sampling
  197. Aggregate concatenation
  198. First common ancestor
  199. Zen update
  200. Selecting random rows
  201. Counting skills