LEFT JOIN and COUNT - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

LEFT JOIN and COUNT

Author

Topic

angeloms
Starting Member

2 Posts

Posted - 2006-12-04 : 11:51:18

Hi all.

I was doing some tests in sql server, and i tried to execute a left join select in northwind database.
--
SELECT employees.employeeid, lastname, firstname, count(orderid)
from employees
left join orders on employees.employeeid=orders.employeeid
where shipregion='RJ'
group by employees.employeeid, lastname, firstname
--
SELECT employees.employeeid, lastname, firstname, count(orderid)
from employees, orders
where shipregion='RJ'
and employees.employeeid*=orders.employeeid
group by employees.employeeid, lastname, firstname
--

I supposed that their results are the same, but they don't!!! The second select returns more lines than the first...

Somebody knows why?

Tks in adv.

Angelo Shiraishi

angeloms
Starting Member

2 Posts

Posted - 2006-12-04 : 11:54:50

Sorry, i forgot some additional information.

XP Professional with SP2
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Personal Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-04 : 12:03:00

Is LEFT JOIN equal to *=?

The first query fetches all employees and their orders (if there are any)
The second query? Yes... Isn't it the other way around? Fetch all orders and the customers (if any)

Peter Larsson
Helsingborg, Sweden

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-12-04 : 12:10:09

The WHERE on the first query disallows any employees without orders, since null values in the shipRegion column will not be returned. Thus, your first query behaves exactly as an INNER JOIN would.

Not sure how this works when using the *= syntax for outer joins -- which is part of the reason why that syntax should never be used; you should always use ANSI style joins, which is spelling out LEFT OUTER JOIN and explicitly indicating what need.

If you want to see all employees in the first query regardless of whether they have an order in RJ or not, you would put the criteria in the JOIN condition:

SELECT employees.employeeid, lastname, firstname, count(orderid)
from employees
left join orders on employees.employeeid=orders.employeeid AND shipregion='RJ'
~~WHERE shipregion='RJ'~~
group by employees.employeeid, lastname, firstname

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources