-
Oracle MINUS equivalent in sql server
Hi,
Is there something equivalent to the MINUS in ORacle ?
Or a workaround ?
thanks
-
-
Why cant you use "Not in"?
select id from table1 where
id not in (select id from table2 where x='cc')
-
-
Not exists clause is better
-
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.
-
Try the following:
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|