Bas,
It's interesting! I tested these three queries on two big tables:
select TCode
from Tracking
where not exists (select *
from Task
where Task.TCode = Tracking.TCode
)
select TCode
from Tracking
where TCode not in (select TCode
from Task
)
select Tracking.TCode
from Tracking left outer join Task on Task.TCode = Tracking.TCode
where Task.TCode is null
Then I checked the execution plan for all of them. The first two queries
took 32.87% and the last one took 34.25% of execution time of whole batch.
Both tables have indexes on TCode field. The third query has an extra step
in it which is filtering the result set and eliminating records in which
Task.TCode is null.
As you see it's not a huge difference between results. I guess it completely
depends on how you setup your indexes. But still it seems to me that using
EXISTS should be faster. :-)
Is it possible to check it on your problem and compare the results? I'm
interested to know what makes the third query faster in your case.
Thanks,
Shervin
"Bas" <nomailplease> wrote in message
news:3f***********************@dreader4.news.xs4al l.nl...
This is usually faster by the way, I use it a lot:
SELECT Tracking.TCode FROM TaskCode RIGHT OUTER JOIN
Tracking ON TaskCode.TCode = Tracking.TCode
WHERE TaskCode.TCode IS NULL
Cheers,
Bas
"Shervin Shapourian" <Sh**********@hotmail.com> wrote in message
news:vp************@corp.supernews.com... Michael,
Try this:
select TCode
from Tracking
where not exists (select *
from Task
where Task.TCode = Tracking.TCode
)
Shervin
"Michael" <lu****@yahoo.com> wrote in message
news:bc*************************@posting.google.co m... I have two tables with a 1-many relationship. I want to write a
select statement that looks in the table w/many records and compares
it to the records in the primary table to see if there are any records
that do not match based on a certain field.
Here is how my tables are setup:
Task Code Table
TCode,TCodeFName,Active
Tracking Table(multiple records)
ID,User,ProjectCode,TCode,Hours,Date
I want to look at the tracking table and see if there are any TCode
listed here that are not listed in the Task Code table.
How do I write a SQL statement to do this?