![Close [x]](http://dw1.s81c.com/i/c.gif){kind=small}
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.
Terry Purcell is a Senior Consultant with YL&A (Yevich, Lawson & Associates) and is an industry recognized authority in DB2 SQL. Terry has presented at numerous conferences on Complex SQL and published many articles on SQL performance including an IBM white paper on Star Joins . He is a regular author for the DB2 Performance Journal and frequent contributor to the DB2 List Server. Terry has been working with DB2 for over 10 years in both database administration and application development. He is also an IBM Certified Solutions Expert in DB2 V7 Database Administration for OS/390 © and a member of the IBM Gold Consultants program for DB2. Terry can be reached at Terry_Purcell@ylassoc.com .
