Uploaded image for project: 'IMPALA'
  1. IMPALA
  2. IMPALA-5022

Outer join simplification

Attach filesAttach ScreenshotAdd voteVotersWatch issueWatchersCreate sub-taskLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

Details

    • Improvement
    • Status: Open
    • Major
    • Resolution: Unresolved
    • Impala 2.9.0
    • None
    • Frontend

    Description

      As a general rule, an outer join can be converted to an inner join if there is a condition on the inner table that filters out non‑matching rows. In a left outer join, the right table is the inner table, while it is the left table in a right outer join. In a full outer join, both tables are inner tables. Conditions that are FALSE for nulls are referred to as null filtering conditions, and these are the conditions that enable the outer‑to‑inner join conversion to be made.

      An outer join can be converted to an inner join if at least one of the following conditions is true.

      • The WHERE clause contains at least one null filtering condition on the inner table.
      • The outer join is involved in another join, and the other join condition has one or more null filtering conditions on the inner table. The other join in this case can be an inner join, left outer join, or right outer join. It cannot be a full outer join because there is no inner table in this case.

      A null filtering condition on the right side of a full outer join converts it to a left outer join, while a null filtering condition on the left side converts it to a right outer join.

      For example the following query

      select t1.c1, t2.c1
      from t1 left outer join t2 using (x)
      where t2.c2 > 5
      

      can safely be converted to

      select t1.c1, t2.c1
      from t1 join t2 using (x)
      where t2.c2 > 5
      

      because the predicate t2.c2 > 5 is interpreted as FALSE if t2.c2 is NULL and therefore the condition removes all non‑matching rows of the outer join.

      Attachments

        Issue Links

        Activity

          This comment will be Viewable by All Users Viewable by All Users
          Cancel

          People

            xqhe Xianqing He
            grahn Greg Rahn

            Dates

              Created:
              Updated:

              Slack

                Issue deployment