DB2DD Reader : Thanks for your explanation of outer joins that appeared on DB2 Developer Domain. My question is, when tables are large (with many rows), how do I decide whether I should go for an inner join or an outer (left or right) join?
Terry Purcell : Whether you code an INNER or OUTER join is not dependent on the number of rows, but more whether the data is mandatory in each of the tables being joined.
For example, assume that we have two tables: PARTS and SUPPLIERS. If our business rule stated that we must store PARTS in the PARTS table only if they have a corresponding SUPPLIER in the SUPPLIERS table, then we would code an inner join such as:
SELECT P.PART_NUM, P.PART_DESC, S.SUPPLIER_NUM FROM PARTS P INNER JOIN SUPPLIER S ON P.PART_NUM = S.PART_NUM
If, however, it were possible that we stored information about parts regardless of whether they have a supplier (thus the supplier details are optional), then we would need to code a LEFT JOIN. If we coded an INNER JOIN then those parts without suppliers would not be returned. The LEFT JOIN query would be:
SELECT P.PART_NUM, P.PART_DESC, S.SUPPLIER_NUM FROM PARTS P LEFT JOIN SUPPLIER S ON P.PART_NUM = S.PART_NUM
If it were possible that we stored parts regardless of whether they have suppliers, and also suppliers, regardless whether or not they supply any parts, then this would be a FULL JOIN, such as:
SELECT P.PART_NUM, P.PART_DESC, S.SUPPLIER_NUM FROM PARTS P FULL JOIN SUPPLIER S ON P.PART_NUM = S.PART_NUM
Thus it is not how many rows are returned from each query that dictates what type of join; instead, it depends on whether the data has a mandatory or optional presence in each of the tables that you are joining.
I hope this answers your question.