How To Use Oracle MINUS Operator
  Home
 

This tutorial page is based on examples to be easier to follow. The Oracle MINUS Operator compares both Select statement’s values and returns only unique lines from the upper SQL query that are missing from the lower query. The Oracle MINUS Operator syntax is following:

<your_1st_select_statement>
MINUS
<your_2nd_select_statement>;

The first MINUS example has two Oracle dual tables combined with the MINUS Operator and their values are hard coded as the upper query has number “1” and the lower query has “2“. Oracle MINUS returns lines only from the upper Select statement that are missing from the lower SQL, so you can see in the output number “1“.

 SELECT 1
  FROM DUAL
MINUS
SELECT 2
  FROM DUAL;

online tech support computer help computer technician computer problems computer oracle minus oracle minus in oracle database software database oracle retirement planning retirement sql database programming database oracle retirement planning retirement sql database oracle database sql

If we would do some light changes in the lower select statement and switch number “2” with “1” then the MINUS ooperator finds no different and returns nothing. The reason is that Oracle Minus is looking for lines that are missing from the lower query and in this example the both Select statements are identical. See the output below.

 SELECT 1
  FROM DUAL
MINUS
SELECT 1
  FROM DUAL;

online tech support computer help computer technician computer problems computer oracle minus oracle minus in oracle database software database oracle retirement planning retirement sql database programming database oracle retirement planning retirement sql database oracle database sql

To understand the 3rd example we would need to take a look at the following Select statement. Online Tech Support will use it as the upper query and you will see that it returns two rows – number “1” and “2“.

 SELECT rownum
  FROM DUAL
CONNECT BY rownum < 3;

online tech support computer help computer technician computer problems computer oracle minus oracle minus in oracle database software database oracle retirement planning retirement sql database programming database oracle retirement planning retirement sql database oracle database sql

On this 3rd Oracle Minus operator example as we said we will use the SQL query above. Please take a look at the following example.

 SELECT rownum
  FROM DUAL
CONNECT BY rownum < 3
MINUS
SELECT 1
  FROM DUAL;

online tech support computer help computer technician computer problems computer oracle minus oracle minus in oracle database software database oracle retirement planning retirement sql database programming database oracle retirement planning retirement sql database oracle database sql

The output returned only number “2” because both queries have common value number “1” and number “2” exists only in the upper Select statement.

To see how does return Oracle Minus operator only unique rows we need to use the following query. Take a look at the output that has repeating values of “1” and “2“. In total you can find 5 lines with number “1” and 5 lines with number “2“.

 SELECT mod (rownum,2) + 1 AS nr
   FROM DUAL
    CONNECT BY rownum < 11;

online tech support computer help computer technician computer problems computer oracle minus oracle minus in oracle database software database oracle retirement planning retirement sql database programming database oracle retirement planning retirement sql database oracle database sql

Using the SQL query above Online Tech Support will do Minus from 1 line Dual table that has value “1“. In other words, the 10 lines with repeating values of 1-s and 2-s against one line with value 1.

 SELECT mod (rownum,2) + 1 AS nr
   FROM DUAL
    CONNECT BY rownum < 11
  MINUS
 SELECT 1
   FROM DUAL;

online tech support computer help computer technician computer problems computer oracle minus oracle minus in oracle database software database oracle retirement planning retirement sql database programming database oracle retirement planning retirement sql database oracle database sql

The output returned only 1 line with number “2“. Oracle Minus returns only lines that has no value match with the Select below and that removed all number “1” values. For second the Minus operator returns only unique lines and that means Oracle Minus does grouping. All repeating number “2” values got grouped into 1 line and if there would be a second value that doesn’t group we would have in output 5 number “2” value rows.



See Also:
Oracle Select Oracle Union Oracle Intersect Home

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)

*