MINUS in SQL Server

[zhonghandle]

Hi,

Is it possible to do a MINUS option between tables in SQL Server?

Like, (Select * from tableA) MINUS (Select * from tableB) assuming that both tableA and tableB have same number of columnes?

Thanks alot in advance.

 

[MAK9974]

No. But u can do this, if you dont have an unique column


create table tablez1 (id int, name char(10), amount int)
create table tablez2 (id int, name char(10), amount int)

insert into tablez1 select 1,"a",1
insert into tablez1 select 2,"a",55
insert into tablez1 select 3,"a",50
insert into tablez1 select 4,"a",59
insert into tablez1 select 5,"a",58
insert into tablez1 select 6,"a",57
insert into tablez1 select 7,"a",6



insert into tablez2 select 4,"a",59
insert into tablez2 select 5,"a",58
insert into tablez2 select 6,"a",57
insert into tablez2 select 7,"a",6


select * from tablez1 where convert(varchar,id)+name+convert(varchar,amount) not in
(select convert(varchar,id)+name+convert(varchar,amount) from tablez2)


select * from tablez2 where convert(varchar,id)+name+convert(varchar,amount) not in
(select convert(varchar,id)+name+convert(varchar,amount) from tablez1)

 

[tlbroadbent]

See thread183-284719. It contains three methods for determining if rows exist in tableA that don't exist in tableB. These techniques only compare key columns. If you want to compare all columns, other techniques are required. Check the following links.

http://www.windows2000faq.com/Articles/Index.cfm?ArticleID=14368

http://www.oreillynet.com/pub/a/network/2002/04/08/compsets.html

http://groups.google.com/groups?selm=874ccp$nic$1@nnrp1.deja.com

http://groups.google.com/groups?selm=7sd9ma$fnh$1@ctb-nnrp2.saix.net

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.