SQL: Guides and Practical Know-how | Page 13 | mironsoft Blog
SQL
-
May 13, 2025
Choosing a primary key is one of the most consequential decisions in table design: a business natural key such as an email address or a product SKU carries meaning, an artificial surrogate key such as an auto-increment value or a UUID is stable and meaningless. This post compares both strategies concretely, presents composite keys as a third option, and delivers a practical decision guide.
-
May 3, 2025
NTILE(n) splits a sorted result set into n roughly equal buckets and turns quartile, decile and general percentile analysis into a single SQL query. Instead of exporting customer values, response times or test scores into statistics software, distribution buckets are produced directly in the database, reproducibly and without manual sorting work.
-
Apr 27, 2025
A wrongly chosen join type either returns too few rows, because unmatched records silently vanish, or too many rows, because a missing join condition creates a cartesian product. Inner join, left join, right join and full join differ exactly in which rows from the involved tables end up in the result set when a condition is not met, and anyone who does not understand this builds reports with silently wrong numbers.
-
Apr 25, 2025
Most data models only know a single time axis: when a row was last changed. Once retroactive corrections enter the picture, though, such as a miscalculated invoice that gets corrected three months later, a single time axis stops being enough. Bitemporal modeling consistently separates valid time, meaning when a fact was actually true from a business perspective, from transaction time, meaning when it became known to the system. This article explains both axes, walks through a fully worked example, and gives a realistic sense of when the extra modeling effort actually pays off.
-
Apr 21, 2025
EXISTS and IN look interchangeable at first glance, but differ in one decisive point: how they handle NULL values in the inner result set. While EXISTS only checks for the existence of a row and NULL plays no role there, a NOT IN with a NULL-containing list can silently return an empty result set, a behavior that regularly leads to missing rows in reports in production, without any error appearing.
-
Apr 17, 2025
Teams that only find slow queries once users complain are working reactively instead of proactively. With slow query log, pg_stat_statements and a clear sampling process, slow queries can be found systematically before they become a production problem, regardless of which database system is in use.
-
Apr 15, 2025
A thousand individual INSERT statements are almost always slower than the same thousand rows sent as one batch, because every single round trip to the database costs network latency, parsing, and transaction overhead. Batch processing bundles these costs, whether through multi-row INSERTs, bulk-load tools, or larger transactions, and can cut load time by an order of magnitude.
-
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.
-
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.
-
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.