Beautiful T-SQL Queries: Three Could Delete the Company
This title could be clearer and more informative.Try out Clickbait Shieldfor free (5 uses left this month).
AI now produces T-SQL that looks polished, aligned, aliased, and commented, but formatting no longer signals correctness the way it once did. Five realistic query examples show subtle traps: a DELETE that removes far more customers than intended due to a JOIN vs correlated subquery mix-up, a NOT IN query silently returning zero rows because of a NULL, an UPDATE with non-deterministic results from duplicate staging rows, a BETWEEN date range that excludes the last moments of a month, and a TRY/CATCH block that swallows a failed money transfer without rethrowing. The piece argues that review discipline must replace trust in appearance: read the FROM clause first, turn destructive statements into SELECT previews, ignore comments as evidence, and assume the data is messier than imagined.
Table of contents
Exhibit A: The One That Can Delete the CompanyExhibit B: The One That Silently Returns NothingExhibit C: The One That Gives a Different Answer Every TimeExhibit D: The One Everybody Has ShippedExhibit E: The One That Is Fine Until the Server Is BusyWhy Our Instincts Are Now Working Against UsWhat I Actually Do NowThe Half of This That Is Good NewsQuestions this post answers
why does my SQL Server DELETE with a JOIN remove more rows than expected
A DELETE using a JOIN on a foreign key like CustomerID removes a row for every matching joined row, not just customers where all orders meet a condition. If a customer has any order matching the WHERE clause, they get deleted, even if they also have unrelated recent orders, because the JOIN multiplies matches rather than filtering by a per-customer condition on all rows. daily.dev surfaces practical write-ups like this for developers hardening their SQL review habits.
why does my SQL NOT IN subquery return zero rows unexpectedly
A NOT IN subquery returns zero rows for the entire outer query if even one row in the subquery's result set has a NULL value in the compared column. This happens because NOT IN against a set containing NULL evaluates to unknown rather than true, so every comparison silently fails and no rows are returned. track SQL gotchas like NULL handling in NOT IN clauses via daily.dev before they reach production.
why does my SQL Server UPDATE from a staging table give different results each time it runs
When a JOIN-based UPDATE matches multiple rows in the source table to a single target row, such as duplicate SKUs in a staging table, SQL Server does not raise an error or guarantee which matching row's value is applied. Microsoft's documentation calls the result undefined, so re-running the same query on the same data can produce a different final value each time. daily.dev helps developers stay ahead of non-obvious SQL behaviors like undefined UPDATE outcomes.
236.4K Impressions6 Comments