Oracle MINUS equivalent in sql server

Hi,
Is there something equivalent to the MINUS in ORacle ?
Or a workaround ?
thanks

What does it do?

Why cant you use "Not in"?

select id from table1 where
id not in (select id from table2 where x='cc')

Here is the difference between oracle and SQL.

http://vyaskn.tripod.com/oracle_sql_...quivalents.htm

thx

rmiao,
MINUS is the set difference operator. so if you have 2 sets, A & B A MINUS B is all items in A which are not in B.

MAK,
I tried to use NOT IN but somehow it doesnot work. All queries which donot have NOT give expected results, but once I use NOT it stops working. I also tried '<> ALL'.
I have a query which has 2 subwueries.
lets say A & B.
so,

A, when ran alone gives me 92 rows.
B giveis 195 rows.

Select accnt.id
FROM accnt
where
accnt.id IN (A)
and accnt.id IN (B)
gives me 46 rows.

so with the following query
Select accnt.id
FROM accnt
where
accnt.id IN (A)
and accnt.id NOT IN (B)

I should get 46 rows. (92-46).
But I get 0.
if I do NOT IN (A) and IN (B), i should get 195 - 46 = 149 rows. But I get 0 rows.
Please advise.
Thanks in Advance.

Try this. It works for me. If it dont work. give me a sample data like below for all the tables and queries that you do and also expected results.


create table accnt (id int, name char(10))
create table subqry1 (id int)
create table subqry2 (id int)

insert into accnt select 1,'claire'
insert into accnt select 2,'mak'
insert into accnt select 3,'John'
insert into accnt select 4,'rmiao'
insert into accnt select 5,'chgrik'
insert into accnt select 6,'Alex'
insert into accnt select 7,'skhanal'
insert into accnt select 8,'Nelly'
insert into accnt select 9,'Anu'
insert into accnt select 10,'Amar'


insert into subqry1 select 1
insert into subqry1 select 2
insert into subqry1 select 3
insert into subqry1 select 7


insert into subqry2 select 1
insert into subqry2 select 2
insert into subqry2 select 3
insert into subqry2 select 4
insert into subqry2 select 5
insert into subqry2 select 6
insert into subqry2 select 9
insert into subqry2 select 12
insert into subqry2 select 14
insert into subqry2 select 17


select * from subqry1
--4 rows
select * from subqry2
--10 rows


Select accnt.id FROM accnt where
accnt.id IN (select id from subqry1)

--4 rows

Select accnt.id FROM accnt where
accnt.id not IN (select id from subqry2)

--3 rows

Select accnt.id
FROM accnt
where
accnt.id IN (select id from subqry1)
and accnt.id NOT IN (select id from subqry2)

--1 row

If you have one column primary key then you can use NOT IN as in MAK's example, but if you have multiple column primary key then it gets complicated.

You may either concatenate multiple columns or do it in phases using temp tables.

I created a sample using the columns my query uses. On sample data, my query works!!!!!.
The tables in the sample data might have different constraints & primary key settings though. I guess the answer is in there somewhere.

It would be great if someone can point me to the right documentation, which mentions about these complications.

Many thanks.

I created a sample using the columns my query uses. On sample data, my query works!!!!!.
The tables in the sample data might have different constraints & primary key settings though. I guess the answer is in there somewhere.

It would be great if someone can point me to the right documentation, which mentions about these complications.

Many thanks.

Hi,

This should work for SQL Server 7.0 & 2000.

-- also refer to MAK's table creation & insert script

select a.id from
(select accnt.id FROM accnt where
accnt.id IN (select id from subqry1)) a,
(Select accnt.id FROM accnt where
accnt.id not IN (select id from subqry2)) b
where a.id = b.id

Finally,
Got the script to work.
The reason it didnt work is that there were some rows with accnt_id as NULL.
So if I add a condition in both sub queries to check for accnt_id IS NOT NULL, it works.
Query B gave out a couple of NULL records, and due to that NOT IN ( <> ALL) would compare every value to NULL and hence get no result at all.
Thanks for everybody's help.