101–110 of 222 posts
these aren’t anti patterns. these are just things you shouldn’t do
https://pragprog.com/titles/bksqla/sql-antipatterns/ There's an actual book on them that had me nodding along the entire time.
but also, the book anti-patterns is pretty clear here
> Overusing DISTINCT to “Fix” Duplicates Any time I see DISTINCT in a query I immediately become suspicious that the query author has an incomplete understanding of the data model, a lack of comprehension of set theory, or more likely both.
Set theory... There are self-identifying "senior software engineers" that cannot understand what even an XOR is, even after you draw out the entire truth table, all four rows.
I don't know about anti patterns but what I like to do is putting 1=1 after each WHERE to align ANDs nicely and this is enough to create huge dramas in PR reviews.
It's always perfectly aligned for me, because enter prefixes 2 whitespace in my ide in SQL files, ending with where a=1 And k=2 And v=3
Earlier quoted context omitted.
Set theory... There are self-identifying "senior software engineers" that cannot understand what even an XOR is, even after you draw out the entire truth table, all four rows.
Or, for a boolean type, that XOR is the same as the inequality operator.
Earlier quoted context omitted.
NULL should generally never be used to "mean" anything. If your business rules say that "not applicable" or "no entry" is a value, store a value that indicates that, don't use NULL.
Interesting, I don't think I've seen that while NULLs are very common. I guess you would handle it in the application and not in the query, right?
> Overusing DISTINCT to “Fix” Duplicates Any time I see DISTINCT in a query I immediately become suspicious that the query author has an incomplete understanding of the data model, a lack of comprehension of set theory, or more likely both.
It's the exact opposite in Cypher. I'm currently working with some complex data in neo4j, and wondered why my perfectly fine looking queries were so slow, until I remembered to use DISTINCT. It's very easy to get duplicate nodes in your results, especially when you use variable length relationships, and DISTINCT is the only fix I'm aware of that fixes that.
Earlier quoted context omitted.
Set theory... There are self-identifying "senior software engineers" that cannot understand what even an XOR is, even after you draw out the entire truth table, all four rows.
I am surprised at common it is for software engineers to not treat booleans properly. I can’t tell you how many times if seen ‘if(IsFoo(X) != false)’ It never used to bug me as a junior dev, but once a peer pointed this out it became impossible for me to ignore.
> Mishandling Excessive Case When Statements User Defined Functions (UDFs) are another option to consolidate the logic in one place. > Using Functions on Indexed Columns In other words, the query is not sargable [0] > Overusing DISTINCT to “Fix” Duplicates Orthogonal to author's point about dealing with fanout from joins, I'm a fan of using something like this for 'de-duping' records that aren't exact matches in orde…
Earlier quoted context omitted.
Anti-Patterns You Should Avoid: overengineering for potential future requirements. Are there real-life cases where you should design with the future in mind? Yes. Are there real-life cases where DISTINCT is the best choice by whatever metric you prioritize at the time? Also yes.
> Are there real-life cases where DISTINCT is the best choice by whatever metric you prioritize at the time Indeed, along that line, I would say that DISTINCT can be used to convey intent... and doing that in code is important. - I want to know the zipcodes we have customers in - DISTINCT - I want to know how many customers we have in each zipcode - aggregates Can you do the first with the second? Sure.. but the firs…
SOMEWHAT-DISTINCT with a fuzzy threshold would also be useful.
> Overusing DISTINCT to “Fix” Duplicates Any time I see DISTINCT in a query I immediately become suspicious that the query author has an incomplete understanding of the data model, a lack of comprehension of set theory, or more likely both.