This discussion is archived
4 Replies Latest reply: Dec 11, 2012 11:14 AM by Frank Kulash RSS

Union and Minus together in a query

979267 Newbie
Currently Being Moderated
Dears,

I need to help this Query...
--
SELECT a, b, c
FROM table1
WHERE a not in ('1','2')
union
(
SELECT a, b, c
FROM table2
WHERE a not in ('1','2')
minus
Select a, b, c
FROM table1
)
"
--
is there another way to do this?

Thanks in advance,

Casapia

Edited by: user4908221 on 11-dic-2012 6:55
  • 1. Re: Union and Minus together in a query
    Frank Kulash Guru
    Currently Being Moderated
    Hi, Casapia,

    Welcome to the forum!
    user4908221 wrote:
    Dears,

    I need to help this Query...
    --

    SELECT a, b, c
    FROM table1
    WHERE a not in ('1','2')
    union
    (
    SELECT a, b, c
    FROM table2
    WHERE a not in ('1','2')
    minus
    Select a, b, c
    FROM table1
    )

    --
    is there another way to do this?
    One way is:
    SELECT  a, b, c
    FROM    table1
    WHERE   a   not in ('1','2')
        UNION
    SELECT  a, b, c
    FROM    table2
    WHERE   a   not in ('1','2')
    The MINUS in what you posted just removes rows from the output of the query on table2 that duplicated output rows from table1. However, UNION (as opposed to UNION ALL ) always checks for duplicates and removes them, so it's redundant for you to do the same thing.

    You could also get the same results wuith a FULL OUTER JOIN, but it takes a lot more code and is probably less efficient.
  • 2. Re: Union and Minus together in a query
    veejays_user10302525-Oracle Journeyer
    Currently Being Moderated
    Welcome to the forum, you have not stated what you intend to do, also please share your table structures and sample data, with what output you are looking. You should include your code in "{code}" tag without quotes.
  • 3. Re: Union and Minus together in a query
    979267 Newbie
    Currently Being Moderated
    Thanks,

    My problem are the columns "d", "e" and "f" in table1... (do NOT exist in table b).
    SELECT a, b, c, d, e, f
    FROM table1
    WHERE a not in ('1','2')
    union
    (
    SELECT a, b, c, null, null, null
    FROM table2
    WHERE a not in ('1','2')
    minus
    SELECT a, b, c, null, null, null
    FROM table1
    )
    If I execute the query without nulls, i will get the error: The used SELECT statements have a different number of columns
    SELECT a, b, c, d, e, f
    FROM table1
    WHERE a not in ('1','2')
    union
    SELECT a, b, c
    FROM table2
    WHERE a not in ('1','2')
    And the other way i obtain incorrect number of records with repeated key (a, b, c).
    SELECT a, b, c, d, e, f
    FROM table1
    WHERE a not in ('1','2')
    union
    SELECT a, b, c, null, null, null
    FROM table2
    WHERE a not in ('1','2')
    Please any help?...

    Casapia
  • 4. Re: Union and Minus together in a query
    Frank Kulash Guru
    Currently Being Moderated
    Hi, Casapia,
    user4908221 wrote:
    Thanks,

    My problem are the columns "d", "e" and "f" in table1... (do NOT exist in table b).
    By "table b", I assume you mean table2.
    SELECT a, b, c, d, e, f
    FROM table1
    WHERE a not in ('1','2')
    union
    (
    SELECT a, b, c, null, null, null
    FROM table2
    WHERE a not in ('1','2')
    minus
    SELECT a, b, c, null, null, null
    FROM table1
    )
    If I execute the query without nulls, i will get the error: The used SELECT statements have a different number of columns
    Exactly: all branhces of the set operators have to slect the same number and (more or less) the same datatypes.
    SELECT a, b, c, d, e, f
    FROM table1
    WHERE a not in ('1','2')
    union
    SELECT a, b, c
    FROM table2
    WHERE a not in ('1','2')
    And the other way i obtain incorrect number of records with repeated key (a, b, c).
    Are you saying that all the ways you tried give you the wrong results (if they run at all)? What are the right results, and what is the data the produces them?
    SELECT a, b, c, d, e, f
    FROM table1
    WHERE a not in ('1','2')
    union
    SELECT a, b, c, null, null, null
    FROM table2
    WHERE a not in ('1','2')
    Please any help?...
    help to do what? Sorry, I don't know where you're starting from, or where you want to go, so I can't give you very good directions.



    Whenever you have a problem, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) from all tables involved.
    Also post the results you want from that data, and an explanation of how you get those results from that data, with specific examples.
    Which of columns can be NULL? Include some examples in your sample data and results.
    Always say which version of Oracle you're using (for example, 11.2.0.2.0).
    See the forum FAQ {message:id=9360002}

    You may need to do a Top-N Query , where you select rows from both tables (possibly including duplicates), and then pick only the best 1 row from each set of duplicates. Exactly how to do this depends on your data and your requirements, so it's really important for you to post some sample data, the results you want from that data, and an explanation of why you want those results from that data.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points