April 8, 2010 at 1:59 pm
Hi all,
wondering if someone could please explain to me how / why I'm seeing this...
We have a SQL Server 2000 database that we're migrating up to 2008. As a result, we're re-writing our queries to the ANSI Standard, and this is something I've just noticed.
select b.streetname, b.area, b.company, a.Id, b.Id
from active as a left join companies as b on a.id = b.id
where area <> '415'
gives me 8,013 rows.
select b.streetname, b.area, b.company, a.Id, b.Id
from active a, companies b
where area <> '415'
and a.id *= b.id
gives me 8,032 rows. These extra rows have NULLs or blanks in a.ID or b.ID; why is the LEFT join not picking these up (at least the ones in the Active table)? Regardless of whether I use LEFT, RIGHT, or anything else I can think of, I always get 8,013. Very confused.
Any insight would be appreciated.
Thanks
Chris
April 8, 2010 at 2:39 pm
The *= stuff is the old style. Don't use this. It won't work in 2008 and it's just confusing.
The reason you are filtering more out on the above query is probably due to the fact that you have a field from the left joined table in your WHERE clause. You don't want to do that. You immediately turn your left join back into an inner join.
You need the criteria to be in the ON clause, not the WHERE clause.
Try this query:
select b.streetname, b.area, b.company, a.Id, b.Id
from active as a left join companies as b on a.id = b.id and b.area <> '415'
April 8, 2010 at 2:42 pm
Hi Seth - Thanks for the reply - that's why we're re-writing the queries, because we know that the *= won't work. However we're also validating them as we go and found this....thanks for the reply, I'll take a look at the changes and post back 😀
Chris
[Edit] - Yup, that worked perfectly ..... sweeeeet!! 😀 Thanks a million...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply