different between inner join, left join and left outer join

[hokky]

Hi guys,

I'm just abit confuse between those two joins, I need your guys idea if I could :
Here's the example

SELECT Employees.Name, Orders.Product
FROM Employees
INNER JOIN Orders
ON Employees.Employee_ID=Orders.Employee_ID

-- result :
Name                Product 
Hansen, Ola         Printer 
Svendson, Stephen   Table 
Svendson, Stephen   Chair 

SELECT Employees.Name, Orders.Product
FROM Employees
LEFT JOIN Orders
ON Employees.Employee_ID=Orders.Employee_ID

--result
Name               Product 
Hansen, Ola        Printer 
Svendson, Tove   
Svendson, Stephen  Table 
Svendson, Stephen  Chair 
Pettersen, Kari

As you can see the inner join only shows whatever if first table and second table match.
But the left joins only shows whatever exists in first table, don't care whether exist in second table.

So, what's the difference with left outer join then ?
I thought left outer join would do the same as left join for example above.

Can anyone throws more idea please ?

 

[nickdel]

Hokky, left and right joins are both outer joins in that they do not require each record in the 2 tables to having matching records.

Why... are you seeing different results?

Nick

 

[r937]

you already understand the difference:

As you can see the inner join only shows whatever if first table and second table match.
But the left joins only shows whatever exists in first table, don't care whether exist in second table.

LEFT JOIN is the same as LEFT OUTER JOIN

the OUTER keyword is optional


r937.com | rudy.ca

 

[ESquared]

... not to mention
RIGHT (OUTER) JOIN
FULL JOIN
CROSS JOIN

and at a deeper level,

LOOP
HASH
MERGE

from which we could get

INNER LOOP JOIN
INNER HASH JOIN
INNER MERGE JOIN
LEFT LOOP JOIN
LEFT HASH JOIN
LEFT MERGE JOIN
...


[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]

 

[danvlas]

I thought left outer join would do the same as left join for example above."

It actually does. See rudy's reply.







Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html