SQL Server - exclusion resulting collection

Asked By Pete

28-Oct-08 03:23 PM

Hi folks,
I have a exclusion question. I am listing the following code after
this. Is there is a way using SQL code to get the exclusion set without any
concatenations or LINQ solution. Thanks.
Peter Kao

create table #t1
(
id   int,
name varchar(20)
)
go
insert #t1 values(1, 'aaa')
insert #t1 values(2, 'bbb')
insert #t1 values(2, 'ccc')
go
select * from #t2
create table #t2
(
id   int,
name varchar(20)
)
go
insert #t2 values(1, 'aaa')
insert #t2 values(2, 'bbb')
go
-- question, the sql code
-- rows in #t1, not in #t2
-- all matches, 2,'ccc'

28-Oct-08 04:38 PM

You mean EXCEPT ?

select id,  name from #t1
EXCEPT
select id,  name from #t2

Mr Tea

28-Oct-08 04:46 PM

On SQL Server 2005/2008 you can use EXCEPT:

SELECT id, name FROM #t1
EXCEPT
SELECT id, name FROM #t2;

--
Plamen Ratchev
http://www.SQLStudio.com