Page 14 - SQL

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.

A single common table expression already makes a query noticeably more readable than a deeply nested subquery, but the real payoff shows up once several CTEs build on each other in sequence, breaking a complex analysis into clearly named, traceable intermediate steps. That readability comes at a price though, since not every database treats a chain of CTEs the same way technically: some materialize each CTE as an independent intermediate result, others transparently fold them into the surrounding query. This article covers how to structure multi step CTE chains sensibly, how materialization behavior differs between PostgreSQL before and after version 12 as well as other databases, and at what point a CTE chain becomes a genuine performance problem in practice.

A many-to-many relationship cannot be represented directly in two tables, it needs a third table acting as a mediator. This article shows how junction tables are built, whether a composite or a surrogate primary key makes more sense, and how additional attributes are stored on the relationship itself without endangering referential integrity.

An endpoint loads a list of a hundred rows and triggers a hundred additional single row queries, one per row. The N+1 problem rarely comes from bad SQL, it comes from how application code loads relationships between tables. Once you recognize the pattern in the query log, you can fix the root cause with a JOIN or batch loading instead of optimizing symptoms.