Ideas: Tuning JOIN SQL Queries in DB2

Many times the three tables could all be coded with inner joins, which would run more efficiently. Outer joins are not inefficient, but if they bring in extra exception rows, and a subsequent inner join then gets rid of those extra rows, it was processing not needed.

SQL Performance when you use JOINs..

Also, make sure that if outer joins are coded, the program is set up to handle nulls being returned from the table where the join is not met.

The VALUE, COALESCE, or IFNULL function should be used to keep DB2 from trying to send a null indicator back to the program.

When coding outer join logic, it does not matter whether you code a left outer join or a right outer join in order to get the logic correct, as long as you have the starting (driver) table coded correctly. (See tuning tip #61.)

There is no difference between a left outer join and right outer join other than where the starting driver is coded. For example, the following two queries are identical in that they produce the exact same results, and both will have the EMP table as the driver table.

Example 1

The Department table will be the starting (driver) table:
SELECT D.DEPTNO, D.DEPTNAME, D.MGRNO,
E.LASTNAME
FROM DEPT D LEFT JOIN
EMP E ON D.MGRNO = E.EMPNO

Example 2

The Department table will also be the starting (driver) table:
SELECT D.DEPTNO, D.DEPTNAME, D.MGRNO,
E.LASTNAME
FROM EMP E RIGHT JOIN
DEPT D ON D.MGRNO = E.EMPNO

Whichever table is to the left of Left Join is the driver table. Also, whichever table is to the right of Right Join is the driver table.

What you need to understand..

The interesting part of these examples is that the DB2 optimizer actually takes any right outer join queries and rewrites them to left outer join queries before executing. This can be seen in the DB2 Explain tool by looking at the JOIN_TYPE column in the Explain.

Left versus right outer joins can be confusing to many developers. This leads to confusion when many queries in an application are coded with left outer joins and many others are coded with right outer joins. It can be even more confusing when in the same query, there are both left and right outer joins between tables.

Summary

DB2 developers should only code left outer joins. These are more straightforward because the driver table is always coded first, and all subsequent tables being joined to it have left outer joins coded beside them, making it more understandable and readable.