This query performs a mixture of merge and nested-loop joins:
select pub_name, au_lname, price
from titles t, authors a, titleauthor ta,
publishers p
where t.title_id = ta.title_id
and a.au_id = ta.au_id
and p.pub_id = t.pub_id
and type = ’business’
and price < $25
Adaptive Server executes this query in three steps:
Step 1 uses 3 worker processes to scan titles as the outer table, performing a full-merge join with titleauthor and then a nested-loop join with authors. No sorting is required for the full-merge join. titles has a clustered index on title_id. The index on titleauthor, ta_ix, contains the title_id and au_id, so the index covers the query. The results are stored in Worktable1, for use in the sort-merge join performed in Step 3.
Step 2 scans the publishers table, and saves the needed columns (pub_name and pub_id) in Worktable2.
In Step 3:
Worktable1 is sorted into join column order, on pub_id.
Worktable2 is sorted into order on pub_id.
The sorted results are merged.
Figure 6-5 shows the steps.
Figure 6-5: Multiple steps in processing a merge join
