May 30, 2002 at 3:22 am
Is there an equivalent to the Oracle 'minus' in T-SQL? Minus returns all the rows in one select statement which are not present in the other select statement. Can't believe there is not an equivalent in T-SQL but can't find anything similar in the bol.
May 30, 2002 at 4:08 am
select a from a retuns:-
a
-----------
1
2
3
4
5
(5 row(s) affected)
select b from b returns:-
b
-----------
1
2
3
(3 row(s) affected)
select a from a where a not in (select b from b)
select a from a left outer join b on a.a = b.b where b.b is null
select a from a where not exists (select b from b where a.a = b.b)
Either of the above 3 selects return:-
a
-----------
4
5
(2 row(s) affected)
Regards,
Andy Jones
.
May 30, 2002 at 4:19 am
Thanks for your reply Andy. I have started using the NOT EXISTS construction to achieve the result set I require and it will work. The problem is that I have a very broad dimension table and I want to know if any of the columns have changed. This results in a huge query using NOT EXISTS (I think it will take me all week to type) and demonstrates the elegance of the minus in Oracle SQL. I guess I just find it hard to believe that there is no SQL Server equivalent of such a useful little word.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply