At the risk of repeating myself, and again quoting from Quora … I came across this today:
https://www.quora.com/What-are-the-pros-and-cons-of-having-complex-SQL-queries-vs-simple-SQL-queries-but-using-other-programming-languages-to-handle-the-complexity-Should-I-write-a-complex-SQL-query-to-handle-everything/answer/Bill-Karwin
It still baffles me that even some senior, hands-on and so called technical people in IT, still do not know how to write efficient SQL.
Here is the full quote: - - - - - - - - - - - - - - - - - - - - - - - - - -
What are the pros and cons of having complex SQL queries vs. simple SQL queries, but using other programming languages to handle the complexity? Should I write a complex SQL query to handle everything?
Bill Karwin, author of "SQL Antipatterns: Avoiding the Pitfalls of Database Programming"
I saw a coworker, a senior Java programmer (with a PhD), struggling to get some code written. He was behind on his deadline. I asked him what he was trying to do and if I could help.
He said he needed data from two tables. He needed all data from the first table and only matching data from the second table. Where there was no matching data, he still wanted the data from the first table, but with nulls for the objects where there was no match in the second table.
“What’s the problem? That’s an outer join in SQL,” I replied.
He said he didn’t know how to use an outer join, so he had spent many hours trying to code the task from scratch using Java. He was fetching all the data from both tables, matching it up, and discarding data from the second dataset that didn’t match. He was trying to make the matching more efficient by implementing some sophisticated sorting algorithm and a B-tree data structure.
“No, really, the database can do that easily, it’ll run faster, require less code, and you’ll only fetch the results of the match. I’ll show you how,” I offered.
His face grew dark, and he literally said to me, “I forbid you from showing me an outer join!”
He had invested so much in implementing his data structure that he couldn’t bear to admit he had wasted his time. He would rather finish his pages of Java code, and never know how to do the same task in one line of SQL.
Don’t be that guy. Use the right tool for the job.
