Node name: HSJOIN
Represents: A hash join for which the qualified rows from tables are hashed to allow direct joining, without pre-ordering the content of the tables.
A join is necessary whenever more than one table is referenced in a FROM clause. A hash join is possible whenever a join predicate equates columns from two different tables. The join predicates need to be exactly the same data type. Hash joins can also arise from a rewritten subquery, as is the case with nested loop joins.
A hash join does not require that the input tables are ordered. The join is performed by scanning the inner table of the hash join and generating a lookup table by hashing the join column values. It then reads the outer table, hashing the join column values, and checking in the lookup table generated for the inner table.