PLEASE HELP SQL GURUS - 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 2005 Forums

Transact-SQL (2005)

PLEASE HELP SQL GURUS

Author

Topic

johnf
Starting Member

1 Post

Posted - 2009-07-20 : 12:48:59

Can anyone tell me why these 2 quesries produce different results ?

select z.licenceID from dbo.t_Import_Fields_Fields z
where z.licenceID not in
(select x.licenceID from dbo.t_Import_Fields_Fields x
where x.fieldstatus = 'organic') - RETURNS 0 RECORDS

select z.licenceID from dbo.t_Import_Fields_Fields z
left outer join
(select licenceID from dbo.t_Import_Fields_Fields
where fieldstatus = 'organic')x
on z.licenceid = x.licenceid
where x.licenceid is null - RETURNS 498 RECORDS

if it helps

the table dbo.t_Import_Fields_Fields has some 130,000 records of which approx 82,000 have a status of organic - there are 4,606 distinct LICENCEID's

thanks
John

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-07-20 : 12:57:55

Here is why by Mladen:

http://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources