SQL Statement select from one table where not in another table

[Michael]

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,TCodeFNam e,Active

Tracking Table(multiple records)
ID,User,Project Code,TCode,Hour s,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?

[Shervin Shapourian]

Re: SQL Statement select from one table where not in another table

Michael,

Try this:

select TCode
from Tracking
where not exists (select *
from Task
where Task.TCode = Tracking.TCode
)

Shervin

"Michael" <lumiya@yahoo.c om> wrote in message
news:bc7ed00.03 10171224.1817b7 87@posting.goog le.com...[color=blue]
> 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,TCodeFNam e,Active
>
> Tracking Table(multiple records)
> ID,User,Project Code,TCode,Hour s,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?[/color]

[Bas]

Re: SQL Statement select from one table where not in another table

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" <ShShapourian@h otmail.com> wrote in message
news:vp0kjh8d73 cn7d@corp.super news.com...[color=blue]
> Michael,
>
> Try this:
>
> select TCode
> from Tracking
> where not exists (select *
> from Task
> where Task.TCode = Tracking.TCode
> )
>
> Shervin
>
> "Michael" <lumiya@yahoo.c om> wrote in message
> news:bc7ed00.03 10171224.1817b7 87@posting.goog le.com...[color=green]
> > 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,TCodeFNam e,Active
> >
> > Tracking Table(multiple records)
> > ID,User,Project Code,TCode,Hour s,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?[/color]
>
>[/color]

[Bas]

Re: SQL Statement select from one table where not in another table

By the way, it's best to not use reserved words like ID,User and Date as
column names :)

Bas

"Michael" <lumiya@yahoo.c om> wrote in message
news:bc7ed00.03 10171224.1817b7 87@posting.goog le.com...[color=blue]
> 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,TCodeFNam e,Active
>
> Tracking Table(multiple records)
> ID,User,Project Code,TCode,Hour s,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?[/color]

[Shervin Shapourian]

Re: SQL Statement select from one table where not in another table

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:3f907043$0 $82646$e4fe514c @dreader4.news. xs4all.nl...[color=blue]
> 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" <ShShapourian@h otmail.com> wrote in message
> news:vp0kjh8d73 cn7d@corp.super news.com...[color=green]
> > Michael,
> >
> > Try this:
> >
> > select TCode
> > from Tracking
> > where not exists (select *
> > from Task
> > where Task.TCode = Tracking.TCode
> > )
> >
> > Shervin
> >
> > "Michael" <lumiya@yahoo.c om> wrote in message
> > news:bc7ed00.03 10171224.1817b7 87@posting.goog le.com...[color=darkred]
> > > 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,TCodeFNam e,Active
> > >
> > > Tracking Table(multiple records)
> > > ID,User,Project Code,TCode,Hour s,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?[/color]
> >
> >[/color]
>
>[/color]

[Erland Sommarskog]

Re: SQL Statement select from one table where not in another table

Bas (nomailplease) writes:[color=blue]
> 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[/color]

Maybe it was in SQL 6.5. I would not expect so in SQL 2000. These two
queries have very similar query plans:

select * from Northwind..Cust omers c
where not exists (select * from
Northwind..Orde rs o where o.CustomerID = c.CustomerID)

select c.*
FROM Northwind..Cust omers c
LEFT JOIN Northwind..Orde rs o on c.CustomerID = o.CustomerID
WHERE o.OrderID IS NULL

But the latter has a extra filter step the first query don't need.

I advice against using outer joins for NOT EXISTS queries. NOT EXISTS
more clearly expresses what you are after. Another advantage to use
NOT EXISTS is that does require you to know what is LEFT and RIGHT.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Bas]

Re: SQL Statement select from one table where not in another table

Hmm funny!
Must have been som MS Access knowledge from long ago that got stuck with me.
I checked, Access uses the technique itself in one of its wizards to find
unmatched records.

Thanks for pointing this out.

Bas

"Shervin Shapourian" <ShShapourian@h otmail.com> wrote in message
news:vp0vopks3a 7433@corp.super news.com...[color=blue]
> 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[/color]
completely[color=blue]
> 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:3f907043$0 $82646$e4fe514c @dreader4.news. xs4all.nl...[color=green]
> > 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" <ShShapourian@h otmail.com> wrote in message
> > news:vp0kjh8d73 cn7d@corp.super news.com...[color=darkred]
> > > Michael,
> > >
> > > Try this:
> > >
> > > select TCode
> > > from Tracking
> > > where not exists (select *
> > > from Task
> > > where Task.TCode = Tracking.TCode
> > > )
> > >
> > > Shervin
> > >
> > > "Michael" <lumiya@yahoo.c om> wrote in message
> > > news:bc7ed00.03 10171224.1817b7 87@posting.goog le.com...
> > > > 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[/color][/color][/color]
records[color=blue][color=green][color=darkred]
> > > > that do not match based on a certain field.
> > > >
> > > > Here is how my tables are setup:
> > > >
> > > > Task Code Table
> > > > TCode,TCodeFNam e,Active
> > > >
> > > > Tracking Table(multiple records)
> > > > ID,User,Project Code,TCode,Hour s,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?
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]