Ask the Experts: Terry Purcell on Outer Joins

Terry Purcell answers a question from a reader about using large tables with outer joins.

Terry Purcell (mailto:Terry_Purcell@ylassoc.com), Senior Consultant, Yevich, Lawson & Associates

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 .



15 July 2002

Q&A

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.

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management, IBM i
ArticleID=14364
ArticleTitle=Ask the Experts: Terry Purcell on Outer Joins
publish-date=07152002