Results 1 to 12 of 12

Thread: Oracle MINUS equivalent in sql server

  1. #1
    Join Date
    Mar 2003
    Location
    NJ/NY
    Posts
    17

    Oracle MINUS equivalent in sql server

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

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    What does it do?

  3. #3
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    Why cant you use "Not in"?

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

  4. #4
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254

  5. #5
    Join Date
    Apr 2003
    Posts
    8

    Not exists clause is better

    thx

  6. #6
    Join Date
    Mar 2003
    Location
    NJ/NY
    Posts
    17
    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.

  7. #7
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    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

  8. #8
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    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.

  9. #9
    Join Date
    Mar 2003
    Location
    NJ/NY
    Posts
    17
    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.

  10. #10
    Join Date
    Mar 2003
    Location
    NJ/NY
    Posts
    17
    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.

  11. #11
    Join Date
    Jan 2003
    Location
    Los Angeles
    Posts
    14

    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

  12. #12
    Join Date
    Mar 2003
    Location
    NJ/NY
    Posts
    17

    Thumbs up

    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
  •