Patent Number: 6,253,197

Title: System and method for hash loops join of data using outer join and early-out join

Abstract: A system and method for joining a build table to a probe table in response to a query for data includes executing a hash loops join of the build table and the probe table. Matched rows are joined and output when the rows match each other by satisfying a join predicate. In an outer join, unmatched rows in the probe table are joined to a NULL build table field values and output, such that all rows of the probe table are output regardless of whether they have matched rows in the build table. In an early-out join, on the other hand, a "match once" table defines the probe table and in response to a query for unique probe table outputs, the joining of a probe table row, once joined and output a first time, to any other rows in the other table is prevented regardless of whether the row might match other rows. In both the hash loops early-out join and the hash loops outer join, when the build table is larger than main memory, the roles of the build and probe tables are reversed.

Inventors: Lindsay; Bruce Gilbert (San Jose, CA), Shekita; Eugene Jon (San Jose, CA)

Assignee: International Business Machines Corporation

International Classification: G06F 17/30 (20060101); G06F 017/30 ()

Expiration Date: 06/26/2018