Difference btw left Join , not in , not exists

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)

Difference btw left Join , not in , not exists

Author

Topic

melon.melon
Yak Posting Veteran

76 Posts

Posted - 2009-07-07 : 22:48:37

Hi guys,

What is the difference btw these 3 commands ?
Run the 3 of them to check but they are returning different results..

select a.CompanyID
FROM t1 a where NOT EXISTS (Select b.CompanyID FROM t2 b) - 0 rows

select a.CompanyID
FROM t1 a, t2 b where a.CompanyID<>b.CompanyID - 218 rows

select distinct (a.CompanyID) FROM t2 a where a.CompanyID NOT IN (Select b.CompanyID FROM t1 b) - 4 rows

Will left join be able to work correctly if i use it in Delete like this:

delete a from t1 a left join tbDemurrage d on d.CompanyID=a.CompanyID left join t2 e on e.CompanyID=a.CompanyID where d.CompanyID is null and e.CompanyID is null

melon.melon
Yak Posting Veteran

76 Posts

Posted - 2009-07-07 : 22:53:23

Miss out one the Left Join:

select distinct(a.CompanyID) from t1 a left join t2 d on d.CompanyID=a.CompanyID where d.CompanyID is null - 13

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-07 : 22:56:52

first of all, if your purpose is to find missing record in a table, what you are doing here is mostly wrong
Maybe you can refer to here first.
http://weblogs.sqlteam.com/peterl/archive/2009/06/12/Timings-of-different-techniques-for-finding-missing-records.aspx

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-08 : 02:21:38

Also see this for using NULL and NOT IN.
http://weblogs.sqlteam.com/peterl/archive/2009/03/19/NULL-NOT-IN-conundrum.aspx

N 56°04'39.26"
E 12°55'05.63"

melon.melon
Yak Posting Veteran

76 Posts

Posted - 2009-07-08 : 05:24:11

Thank you
and the corrected query could be

delete FROM t1 where NOT EXISTS
(Select a.CompanyID, b.CompanyID FROM t2 a, t3 b
where a.companyID=t1.companyID and
b.CompanyID=t1.CompanyID)

Left Join deletion is applicable just that it can trigger twice since row count starts at 1.

And one question if it is possible to get the deleted CompanyID parameter above:

BEGIN
-- delete query

if @@error=0
begin
Insert into tbTest (CompanyID)
values(@companyID);
END

melon.melon
Yak Posting Veteran

76 Posts

Posted - 2009-07-08 : 05:34:21

It is able to insert the deleted into tbTest but NULL for CompanyID since it is not passed in correctly.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources