How can you get records that are in one table but not another?

[Ted Young]

I have 2 tables with identical schema.

Is there a way to query what records are in one table but not another?

Any ideas or suggestions would be greatly appreciated.

Thanks!

Ted

[DaveSatz]

set nocount on
create table #x
( id int
, x varchar(30))
go

insert #x select 1, 'a'
insert #x select 2, 'b'
insert #x select 3, 'c'
go

create table #y
( id int
, x varchar(30))
go

insert #y select 1, 'a'
insert #y select 3, 'c'
go

SELECT *
FROM #x x
WHERE NOT EXISTS ( SELECT 1
FROM #y y
WHERE x.id = y.id )
go

drop table #x
drop table #y
go

--
HTH,
David Satz
SQL Server MVP
Principal Software Engineer
Hyperion Solutions
{ SQL Server 2000 SP1/7.0 SP3/6.5 SP5a } { Cold Fusion 5/4.5.1 SP2 } { VSS }
(Please reply to group only - emails answered rarely)
-----------------------------------------------------------------
"Ted Young" <tedyoung@connecticare.com> wrote in message
news:3b84f6a1@news.devx.com...
>
> I have 2 tables with identical schema.
>
> Is there a way to query what records are in one table but not another?
>
> Any ideas or suggestions would be greatly appreciated.
>
> Thanks!
>
> Ted

[Ted Young]

I am doing this in ACCESS.

Is there any way to determinte the records that differ using a JOIN operator?

Thanks!

Ted

"DaveSatz" <davidNOSPAMsatz@yahoo.com> wrote:
>set nocount on
>create table #x
>( id int
>, x varchar(30))
>go
>
>insert #x select 1, 'a'
>insert #x select 2, 'b'
>insert #x select 3, 'c'
>go
>
>create table #y
>( id int
>, x varchar(30))
>go
>
>insert #y select 1, 'a'
>insert #y select 3, 'c'
>go
>
>SELECT *
>FROM #x x
>WHERE NOT EXISTS ( SELECT 1
> FROM #y y
> WHERE x.id = y.id )
>go
>
>drop table #x
>drop table #y
>go
>
>--
>HTH,
>David Satz
>SQL Server MVP
>Principal Software Engineer
>Hyperion Solutions
>{ SQL Server 2000 SP1/7.0 SP3/6.5 SP5a } { Cold Fusion 5/4.5.1 SP2 } { VSS
}
>(Please reply to group only - emails answered rarely)
>-----------------------------------------------------------------
>"Ted Young" <tedyoung@connecticare.com> wrote in message
>news:3b84f6a1@news.devx.com...
>>
>> I have 2 tables with identical schema.
>>
>> Is there a way to query what records are in one table but not another?
>>
>> Any ideas or suggestions would be greatly appreciated.
>>
>> Thanks!
>>
>> Ted
>
>

[DaveSatz]

Ted,

in the prev. ex.:

SELECT *
FROM #x x
LEFT OUTER JOIN #y y ON x.id = y.id

would return NULL for columns in "y" of there was no row.

Dave

"Ted Young" <tedyoung@connecticare.com> wrote in message
news:3b850a57$1@news.devx.com...
>
> I am doing this in ACCESS.
>
> Is there any way to determinte the records that differ using a JOIN
operator?
>
> Thanks!
>
> Ted
>
> "DaveSatz" <davidNOSPAMsatz@yahoo.com> wrote:
> >set nocount on
> >create table #x
> >( id int
> >, x varchar(30))
> >go
> >
> >insert #x select 1, 'a'
> >insert #x select 2, 'b'
> >insert #x select 3, 'c'
> >go
> >
> >create table #y
> >( id int
> >, x varchar(30))
> >go
> >
> >insert #y select 1, 'a'
> >insert #y select 3, 'c'
> >go
> >
> >SELECT *
> >FROM #x x
> >WHERE NOT EXISTS ( SELECT 1
> > FROM #y y
> > WHERE x.id = y.id )
> >go
> >
> >drop table #x
> >drop table #y
> >go
> >
> >--
> >HTH,
> >David Satz
> >SQL Server MVP
> >Principal Software Engineer
> >Hyperion Solutions
> >{ SQL Server 2000 SP1/7.0 SP3/6.5 SP5a } { Cold Fusion 5/4.5.1 SP2 } {
VSS
> }
> >(Please reply to group only - emails answered rarely)
> >-----------------------------------------------------------------
> >"Ted Young" <tedyoung@connecticare.com> wrote in message
> >news:3b84f6a1@news.devx.com...
> >>
> >> I have 2 tables with identical schema.
> >>
> >> Is there a way to query what records are in one table but not another?
> >>
> >> Any ideas or suggestions would be greatly appreciated.
> >>
> >> Thanks!
> >>
> >> Ted
> >
> >
>

[Daniel Laurent Lemire]

"Ted Young" <tedyoung@connecticare.com> wrote:
>
>I have 2 tables with identical schema.
>
>Is there a way to query what records are in one table but not another?
>
>Any ideas or suggestions would be greatly appreciated.
>
>Thanks!
>
>Ted

lets say to authors and authors2 table

select auid, aulname, aufname from authors where
auid not in (select auid from authors2)