数据库中表连接方式有哪些

What is the difference between “Sort Merge” and “Hash” Joins. Don’t they both do a one
FULL scan each on the joining tables and join them?

I know Sort Merge is used in the case of “ALL ROWS” and Nested Loops in the case of
“FIRST ROWS” hints. How about Has Join? When is it used?

Would really appreciate if you could explain it with a couple of examples.

Thanks in advance.

and we said…
Well, a sort merge of A and B is sort of like this:

read A and sort by join key to temp_a
read B and sort by join key to temp_b

read a record from temp_a
read a record from temp_b
while NOT eof on temp_a and temp_b
loop
if ( temp_a.key = temp_b.key ) then output joined record
elsif ( temp_a.key elsif ( temp_a.key >= temp_b.key ) read a record from temp_b )
end loop

(its more complex then that, the above logic assumed the join key was unique — we really
need to join every match in temp_a to every match in temp_b but you get the picture)

The hash join is conceptually like:

create a hash table on one of A or B (say A免费主机域名) on the join key creating temp_a.

while NOT eof on B
read a record in b
hash the join key and look up into temp_a by that hash key for matching
records
output the matches
end loop

So, a hash join can sometimes be much more effici免费主机域名ent (one hash, not two sorts)