We're getting a debate within our company. I personally use left outer joins quit frequently. I had been relayed through another programmer that there's an excessive amount of over mind and that i should avoid using them. Example, Allows say I've two tables, they'd rather me hit the database two times obtain the information I want from each table store it in memory and join the information around the java side, rather than just carrying out a left outer join. I believe this really is absurd and could be harmful to performance. Shall We Be Held wrong

You aren't wrong. Utilizing a join is that appears to be more effective. This is exactly what databases are great at.

We i did so joins within the application instead of within the database sometimes in the 80s. However ,, but 1988 approximately the database optimizer was usually adequate with DB2 that wasn't necessary anymore.

I can not think about any regular situations that warrant application-joins. Abnormal situations like when you have an over-loaded database server, body fat network pipe, a lot of capacity around the application server, and a lot of new queries - might warrant it for a few of the queries. Maybe.

However in general joins, including left/right/full outer joins are the friend. They give you more compact tables, improve use from both storage and memory, etc. But you will want current statistics, to possess a passing knowledge of the sorts of access pathways a database uses and obtain within the practice of doing describes.

My belief is you should avoid striking the database two times when you don't have to. I miss out on in which a left join could be much more of an issue than requesting the information two times. When the FKs aren't indexed there might be a performance problem, but that's poor design not really a fault from the left joins.

Eventually the equivalent data would need to be came back one of the ways or another and databases are made to use joins.

Nevertheless, maybe how you are writing the queries is probably not efficient (using left joins when inner joins are essential or joining to tables unnecessary whatsoever), without seeing them I've not a way to understand that.