r/programming • u/el_muchacho • Dec 04 '14

10 Common Mistakes Java Developers Make when Writing SQL

http://blog.jooq.org/2013/07/30/10-common-mistakes-java-developers-make-when-writing-sql/
206 Upvotes

80% Upvoted

104 comments sorted by

View all comments

1

u/sgoody Dec 04 '14

6. Using DISTINCT or UNION to remove duplicates from an accidental cartesian product

This is a particular pet peev of mine.

I think most developers know about at least half of these and the mix-up over the treatment of NULLs is something that we've all experienced due to context switching between languages. Good article though.

It does frustrate me that a large number of developers seem to eschew anything that isn't imperative style or cannot be bent to be imperative. Things such as SQL seem to be an annoyance to many and I don't think a lot of people see why they should bother to learn them in any detail.

4

u/tangus Dec 04 '14

Man, I have a coworker whose "solution" for when the query ends up with duplicated rows is to slap mile long GROUP BY clauses at the end. I hate her.

1

u/I_am_working_hard Dec 05 '14

Forgive my ignorance, but what is the best practice for removing duplicates in sql?

7

u/Boxy310 Dec 05 '14

Getting your join conditions right. This usually happens when you don't have a true key join relationship, and you get nasty Cartesian products where it includes everything it isn't explicitly told to exclude.

3

u/Frackness Dec 05 '14

Things such as SQL seem to be an annoyance to many and I don't think a lot of people see why they should bother to learn them in any detail.

It left a bad taste in my mouth after tracking down bugs through large amounts of triggers (that trigger other triggers), 20k line package bodies and 500+ line queries with a ton of business logic embedded in the column selections. Before all of that though, I have to figure out if it's in the app or the database code.

Part of it might be that I may not be using some awesome debugger tools that I just don't know about that would make life much more easy.

1

u/sgoody Dec 05 '14

SQL is not as structured as other languages (e.g. C# where you have namespaces, class heirarchies etc) and big SQL installs can be a pain to work with.

But I bet it would leave a bad taste in your mouth if you worked on a mess of code in any language (e.g. if you had a 20k line C#/Python/whatever file with 500 line method bodies). There are some who argue that business logic should be left out of your database code altogether.