ITPEC FE Morning April 2018 Question 28

Sort both tables on the join column, then join them from the top — that is the sort-merge join.

The sort-merge join first sorts the rows of both tables by the join column. It then walks down both sorted lists together, like the merge step of merge sort: when the values match the rows are joined, and otherwise the side with the smaller value moves on. Each table is read once after sorting, which suits large tables and joins on inequalities.

Answer (a)

Why not others:

• •(b) — describes an index join (index nested loop): each row of one table looks up its partners through an index on the other table's join column
• •(c) — describes a nested loop join: take the rows of one table in turn and search the other table for each
• •(d) — describes a hash join: build a hash table on one table's join column, then probe it with each row of the other

Key rule: The three classic join methods are named after what they build: nothing (nested loop, optionally helped by an index), sorted inputs (sort-merge) or a hash table (hash join).

Databases · AI-written, may contain errors