If DB2 Should Avoid External Joins

Question

We have a debate in our company. I use left outer joins that often go away. Another programmer told me that there was too much above my head, and I should not use them. Example. Suppose I have two tables, they would prefer me to get into the database, get the information I need from each table twice, storing it in memory, and join the data on the java side, rather than just making a left outer join. I think this is absurd and bad for performance. I'm wrong

Answer 1

We used to use connections in the application, not in the database, sometimes in the 80s. But in fact, by 1988 or so, the database optimizer was usually pretty good with DB2, which is no longer needed.

I cannot think of any ordinary scripts that guarantee application attachment. Abnormal situations, for example, when you have a overloaded database server, a bold network channel, a ton of capacity on the application server and many new queries, may require it for some queries. May be.

But overall, joins, including left / right / full external join, are your friend. They allow you to have smaller tables, it is better to use them both from storage and from memory, etc. But you want to get up-to-date statistics to familiarize yourself with the types of access paths that the database uses, and are used to doing explains.

Answer 2

You are not mistaken. Using a compound is likely to be more effective. This is what databases work well for.

Answer 3

I am convinced that you should not hit the database twice when you do not need it. I do not see where a left connection would be more of a problem than requesting data twice. If the FKs are not indexed, a performance issue may occur, but this is a poor design, not a malfunction of the left connection.

Ultimately, the same amount of data must be returned one way or another, and the databases are designed to use joins.

However, it may happen that the way you write queries can be inefficient (using left joins when internal joins are needed or joining tables that are not needed at all) without seeing them, I don't know that,