LEFT OUTER LOOP JOIN and LEFT OUTER JOIN

[Jay]

hi all:
for a long time, I 've been confused about how to use LEFT OUTER
LOOP JOIN and LEFT OUTER JOIN,
sometimes, when I construct a query with LEFT OUTER JOIN, it's faster than
LEFT OUTER LOOP JOIN, but there is other time,
if I construct a query with LEFT OUTER LOOP JOIN, it's faster than LEFT
OUTER JOIN, this is a mystery to me, does anyone know
how to use these JOIN TYPE properly? like giving some explaination or some
examples?

please help!
thanks
-Jay

[sh]

In most cases the optimizer will choose the best join type
for a query,so specifying the join type is not necessary.

I have found that in rare cases you can speed up a query
by specifying LOOP in the join. In my case, using the loop
removed a Lazy Spool and a costly sort from the plan.

hth

>.
>

[Angel]

jay LOOP (nested loops), MERGE,HASH all these are hints, but you need not
concern yourselfwith them, the optimizer is good at that...

normally is which Algorhtm should the Optimizer use to join two SETS?

LOOP is
well think of somkething like
for each row in the table 1
for each row in table 2
JOIN
loop
loop

NESTED LOOPS..

if one of the tables is small and the other one big, you might see LOOP

merge is good for almost all cases
and HASH, is good for intermidiate results...JOINS

"Jay" <jc...@ibes.com> wrote in message news:eU9CXlm#AHA.1412@tkmsftngp02...