[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Finding values that don't exist in another table

Rick

3/27/2007 6:26:00 PM

Hello All,

I have a table like this

Id Target
1 access
2 access
3 acrodynamo
4 acrodynamo
5 addrecord

I have a second table like this

Id Destination
1 access
2 addrecord
3 afptrouble
4 approvals
5 auxlayoutmap

I need to find all of the records in the first table, where there is
not a match in the second table. For example, there is no "acrodynamo"
Destination in the second table to match Target 3 and 4 in the first
table, so I want to return records 3 and 4 from the first table. Thank
you very much.

Rick Quatro
585-659-8267
7 Answers

Anith Sen

3/27/2007 6:35:00 PM

0

Use NOT EXISTS(). Something along the lines of:

SELECT *
FROM tbl2
WHERE NOT EXISTS ( SELECT *
FROM tbl1
WHERE tbl2.destination = tbl1.Target );

--
Anith


mad8

3/27/2007 6:43:00 PM

0

On Mar 27, 2:34 pm, "Anith Sen" <a...@bizdatasolutions.com> wrote:
> Use NOT EXISTS(). Something along the lines of:
>
> SELECT *
> FROM tbl2
> WHERE NOT EXISTS ( SELECT *
> FROM tbl1
> WHERE tbl2.destination = tbl1.Target );
>
> --
> Anith

or alternately
SELECT *
FROM tbl2 left outer join tbl1 on tbl2.destination = tbl1.Target
where tbl1.id is null

Rick

3/27/2007 6:53:00 PM

0

On Mar 27, 2:34 pm, "Anith Sen" <a...@bizdatasolutions.com> wrote:
> Use NOT EXISTS(). Something along the lines of:
>
> SELECT *
> FROM tbl2
> WHERE NOT EXISTS ( SELECT *
> FROM tbl1
> WHERE tbl2.destination = tbl1.Target );
>
> --
> Anith

Thank you for the quick response. It works fine!

Rick

Rick

3/27/2007 7:05:00 PM

0

On Mar 27, 2:43 pm, "mad8" <mad8vski...@gmail.com> wrote:
> On Mar 27, 2:34 pm, "Anith Sen" <a...@bizdatasolutions.com> wrote:
>
> > Use NOT EXISTS(). Something along the lines of:
>
> > SELECT *
> > FROM tbl2
> > WHERE NOT EXISTS ( SELECT *
> > FROM tbl1
> > WHERE tbl2.destination = tbl1.Target );
>
> > --
> > Anith
>
> or alternately
> SELECT *
> FROM tbl2 left outer join tbl1 on tbl2.destination = tbl1.Target
> where tbl1.id is null

Thank you very much. The outer join version is much faster than the
not exists version.

Rick

kk

3/27/2007 10:59:00 PM

0

Rick
Keep in mind that JOIN is not always faster than EXISTS. In your scenario
this may be true. Their implementation is different. EXISTS only checks for
the existance of the rows that meet the condition and returns TRUE or FALSE.
But JOIN has to loop through the records.
--
Krishnakumar S

What lies behind you and what lies before you is nothing compared to what
lies within you


"Rick" wrote:

> On Mar 27, 2:43 pm, "mad8" <mad8vski...@gmail.com> wrote:
> > On Mar 27, 2:34 pm, "Anith Sen" <a...@bizdatasolutions.com> wrote:
> >
> > > Use NOT EXISTS(). Something along the lines of:
> >
> > > SELECT *
> > > FROM tbl2
> > > WHERE NOT EXISTS ( SELECT *
> > > FROM tbl1
> > > WHERE tbl2.destination = tbl1.Target );
> >
> > > --
> > > Anith
> >
> > or alternately
> > SELECT *
> > FROM tbl2 left outer join tbl1 on tbl2.destination = tbl1.Target
> > where tbl1.id is null
>
> Thank you very much. The outer join version is much faster than the
> not exists version.
>
> Rick
>
>

Tibor Karaszi

3/28/2007 10:22:00 AM

0

> Keep in mind that JOIN is not always faster than EXISTS. In your scenario
> this may be true. Their implementation is different. EXISTS only checks for
> the existance of the rows that meet the condition and returns TRUE or FALSE.
> But JOIN has to loop through the records.

That is the logical processing. However, SQL Server will flatten subqueries to joins, so you will in
most cases see the same performance assuming the semantics is the same.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/d...
http://www.solidqualitylea...


"Krishnakumar S" <kk@discussions.microsoft.com> wrote in message
news:A433DFEA-6771-412E-9161-F7547C9333A8@microsoft.com...
> Rick
> Keep in mind that JOIN is not always faster than EXISTS. In your scenario
> this may be true. Their implementation is different. EXISTS only checks for
> the existance of the rows that meet the condition and returns TRUE or FALSE.
> But JOIN has to loop through the records.
> --
> Krishnakumar S
>
> What lies behind you and what lies before you is nothing compared to what
> lies within you
>
>
> "Rick" wrote:
>
>> On Mar 27, 2:43 pm, "mad8" <mad8vski...@gmail.com> wrote:
>> > On Mar 27, 2:34 pm, "Anith Sen" <a...@bizdatasolutions.com> wrote:
>> >
>> > > Use NOT EXISTS(). Something along the lines of:
>> >
>> > > SELECT *
>> > > FROM tbl2
>> > > WHERE NOT EXISTS ( SELECT *
>> > > FROM tbl1
>> > > WHERE tbl2.destination = tbl1.Target );
>> >
>> > > --
>> > > Anith
>> >
>> > or alternately
>> > SELECT *
>> > FROM tbl2 left outer join tbl1 on tbl2.destination = tbl1.Target
>> > where tbl1.id is null
>>
>> Thank you very much. The outer join version is much faster than the
>> not exists version.
>>
>> Rick
>>
>>


Don Peterson

3/29/2007 3:22:00 PM

0

Rick wrote:
> Hello All,
>
> I have a table like this
>
> Id Target
> 1 access
> 2 access
> 3 acrodynamo
> 4 acrodynamo
> 5 addrecord
>
> I have a second table like this
>
> Id Destination
> 1 access
> 2 addrecord
> 3 afptrouble
> 4 approvals
> 5 auxlayoutmap
>
> I need to find all of the records in the first table, where there is
> not a match in the second table. For example, there is no "acrodynamo"
> Destination in the second table to match Target 3 and 4 in the first
> table, so I want to return records 3 and 4 from the first table. Thank
> you very much.
>
> Rick Quatro
> 585-659-8267
>
Or if you are using 2005 you can use my personal favorite:

SELECT Target
FROM Table1
EXCEPT
SELECT Destination
FROM Table2