51–60 of 222 posts
Earlier quoted context omitted.
IDK, "which ZIP codes do we have customers in?" seems like a reasonable thing to want to know
The very next ask will be "order the zipcodes by number of customers" at which point you'll be back to aggregations, which is where you should have started
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.
> what I like to do is putting 1=1 after each WHERE to align ANDs nicely Frankly, that sounds like one of those things that totally makes sense in the author’s head, but inconsiderately creates terrible code ergonomics and needless cognitive load for anyone reading it. You know to just ignore those expressions when you’re reading it because you wrote it and know they have no effect, but to a busy code reviewer, it’s…
Earlier quoted context omitted.
Eh, sometimes you need a quick fix and it’s just extremely concise and readable. I’ll take an INNER JOIN over EXISTS (nice but insanely verbose) or CROSS APPLY (nice but slow) almost every time. Obviously you have to know what you’re dealing with, and I’m mostly talking about reporting, not perf critical application code. Distinct is also easily explained to users, who are probably familiar with Excel’s “remove dupli…
The less verbose way of doing semijoins is by an IN subquery.
>less verbose
Well…
In any case, it depends. OP nicely guarded himself by writing “overusing”, so at that point his pro-tip is just a tautology and we are in agreement: not every use of DISTINCT is an immediate smell.
Earlier quoted context omitted.
IDK, "which ZIP codes do we have customers in?" seems like a reasonable thing to want to know
The very next ask will be "order the zipcodes by number of customers" at which point you'll be back to aggregations, which is where you should have started
> 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.
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.
Earlier quoted context omitted.
IDK, "which ZIP codes do we have customers in?" seems like a reasonable thing to want to know
The very next ask will be "order the zipcodes by number of customers" at which point you'll be back to aggregations, which is where you should have started
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.
> 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.
And that's okay. Not every developer knows every single thing there is to know about every single tech. Sometimes you just need a solution, and someone with more specific knowledge can optimize later. How many non-database related mistakes would you make if you had to build every part of a system yourself?
> 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.
> Overusing DISTINCT to “Fix” Duplicates I wrote a small tutorial (~9000 words in two parts) on how to design complicated queries so that they don't need DISTINCT and are basically correct by construction. https://kb.databasedesignbook.com/posts/systematic-design-of...
Edit: it’s also actually a book!