The VLDB Journal

, Volume 18, Issue 3, pp 599–610 | Cite as

Reducing outer joins

  • Gerhard Hill
  • Andrew RossEmail author
Regular Paper


We present a method for transforming some outer joins to inner joins and describe a generalized semijoin reduction technique. The first part of the paper shows how to transform a given outer join query whose join graph is a tree to an equivalent inner join query. The method uses derived relations and join predicates. Derived relations contain columns corresponding to join conditions and may have virtual row identifiers, rows and attribute values. The constructed inner join query, after elimination of virtual row identifiers, has the same join tuples as the outer join query. Both the theoretical maximum number of virtual rows and the average number in practice are shown to be low. The method confines consideration of the non-associativity of outer joins to a single step. The second part of the paper generalizes to outer joins the well known technique of semijoin reduction of inner joins. It does so by defining the notions of influencing and needing, and using them to define full reduction and reduction plans. The technique is applied here to perform one step of the method presented in the first part. Semijoin reduction is useful in practice for executing join queries in distributed databases.


Outer join evaluation Virtual row method Join transformation Semijoin reduction Efficient join evaluation 


Unable to display preview. Download preview PDF.

Unable to display preview. Download preview PDF.


  1. 1.
    Bhargava, G., Goel, P., Iyer, B.: Simplification of outer joins. In: Proceedings Conference of the Centre for Advanced Studies on Collaborative Research, Toronto (1995)Google Scholar
  2. 2.
    Bernstein P.A., Goodman N., Wong E., Reeve C.L., Rothnie J.B. Jr: Query processing in a system for distributed databases (SDD-1). ACM Trans. Database Syst. 6(4), 602–625 (1981)zbMATHCrossRefGoogle Scholar
  3. 3.
    Chiu, D.M., Ho, Y.C.: A methodology for interpreting tree queries into optimal semi-join expression. In: Proceedings ACM-SIGMOD International Conference on Management of Data, Santa Monica, CA, pp. 169–178. ACM, New York (1980)Google Scholar
  4. 4.
    Galindo-Legaria, C.A., Rosenthal, A.: How to extend a conventional optimizer to handle one- and two-sided outerjoin. In: Proceedings 8th International Conference on Data Engineering, Tempe, AZ, pp. 402–409 (1992)Google Scholar
  5. 5.
    Galindo-Legaria C.A., Rosenthal A.: Outerjoin simplification and reordering for query optimization. ACM Trans. Database Syst. 22(1), 43–74 (1997)CrossRefGoogle Scholar
  6. 6.
    Kambayashi, Y., Yoshikawa, M., Yajima, S.: Query processing for distributed databases using generalized semi-joins. In: Proceedings ACM-SIGMOD International Conference on Management of Data, Orlando, FL, pp. 151–160. ACM, New York (1982)Google Scholar
  7. 7.
    Stocker, K., Braumandl, R., Kemper, A., Kossmann, D.: Integrating semi-join-reducers into state-of-the-art query processors. In: Proceedings 17th International Conference on Data Engineering (ICDE’01), Heidelberg, pp. 575–584 (2001)Google Scholar

Copyright information

© Springer-Verlag 2008

Authors and Affiliations

  1. 1.SAP AGWalldorfGermany

Personalised recommendations