Tag: SQL

1. Normalization: 1NF to 3NF Explained Practically

   Apr 15, 2025

   Normalization is the systematic process of splitting a table into smaller, linked tables so that each piece of information is stored exactly once. This post walks through a single, continuous orders table step by step to show what first, second and third normal form each require, which concrete anomaly they eliminate, and what the result looks like as an actual SQL table structure.

   Read more

2. NULL Sorting and COALESCE Compared Across Databases

   Apr 11, 2025

   Where a NULL value ends up in a sorted result set, and which function replaces it with a fallback value, sounds like a footnote, yet it is regulated completely differently between MySQL, PostgreSQL, SQL Server, and Oracle. Anyone who wants to keep sorting logic and NULL handling portable across these systems needs to precisely distinguish NULLS FIRST, NULLS LAST, COALESCE, IFNULL, ISNULL, and NVL.

   Read more

3. Finding and Debugging Long Running Transactions

   Apr 6, 2025

   A transaction that never got committed does not block a single query, it silently prevents vacuum in the background, holds locks, and bloats undo structures without a single slow query ever showing up in the slow query log. Detecting idle in transaction sessions systematically through session views leads to the cause in application code, instead of treating symptoms only.

   Read more

4. Modeling Inheritance in Relational Databases Correctly

   Apr 6, 2025

   Object-oriented inheritance does not map directly onto the relational model, which is why every mapping is a compromise. This article explains the three established patterns for inheritance in databases, Single Table Inheritance, Class Table Inheritance and Concrete Table Inheritance, with real table designs and their respective tradeoffs around NULL values, queries and extensibility.

   Read more

5. Pagination Strategies: OFFSET vs. Keyset Pagination

   Apr 6, 2025

   OFFSET-based pagination gets slower with every additional page, because the database still has to read and discard every skipped row. Keyset pagination, also called cursor pagination, replaces the page counter with a comparison against the last seen value and thereby delivers constant response times regardless of how deep you page into the result set.

   Read more

6. ROW_NUMBER, RANK, DENSE_RANK Compared

   Apr 2, 2025

   ROW_NUMBER, RANK and DENSE_RANK look almost identical at first glance, yet behave fundamentally differently as soon as two rows share the same value. Anyone who does not know the difference ends up with broken deduplication logic and incorrect leaderboards. This article shows the exact behavior of all three ranking functions using concrete tied-value examples.

   Read more

7. PARTITION BY: Practical Examples for Group Analytics

   Mar 31, 2025

   PARTITION BY is the building block inside the OVER clause that turns a global calculation into a grouped one, without any rows disappearing along the way. This article shows, with real reporting examples, how single-column and multi-column partitioning work, how PARTITION BY interacts with ORDER BY, and where the typical pitfalls lie.

   Read more

8. PostgreSQL vs. MySQL: Syntax Differences Compared

   Mar 29, 2025

   Anyone migrating an application between PostgreSQL and MySQL, or running both systems side by side, quickly runs into syntax differences that look harmless at first glance but can delay an entire migration. This article compares data types, auto increment, JSON support, pagination, and upsert syntax between PostgreSQL and MySQL using runnable examples.

   Read more

9. Correlated Subqueries Explained

   Mar 29, 2025

   A correlated subquery differs from a normal subquery in that it references a column of the outer query and therefore has to be logically re-evaluated for every single outer row. This one difference explains both its expressive power for row-by-row logic and its biggest performance risk, when the optimizer cannot turn the repeated execution into an efficient join.

   Read more

10. Implementing Optimistic Locking with Version Columns

    Mar 28, 2025

    Optimistic locking resolves concurrent write access without database locks by giving every row a version column, so an UPDATE only succeeds if the version at write time still matches the version seen at read time. If the number of affected rows deviates from one, another transaction has written in the meantime, and the application must handle the conflict deliberately.

    Read more