Page 10 - SQL

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.