Abstract
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.
Similar content being viewed by others
References
Bhargava, G., Goel, P., Iyer, B.: Simplification of outer joins. In: Proceedings Conference of the Centre for Advanced Studies on Collaborative Research, Toronto (1995)
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)
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)
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)
Galindo-Legaria C.A., Rosenthal A.: Outerjoin simplification and reordering for query optimization. ACM Trans. Database Syst. 22(1), 43–74 (1997)
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)
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)
Author information
Authors and Affiliations
Corresponding author
Rights and permissions
About this article
Cite this article
Hill, G., Ross, A. Reducing outer joins. The VLDB Journal 18, 599–610 (2009). https://doi.org/10.1007/s00778-008-0110-5
Received:
Revised:
Accepted:
Published:
Issue Date:
DOI: https://doi.org/10.1007/s00778-008-0110-5