How To Use Oracle MINUS Operator

online tech support Add comments

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;

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;

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;

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;

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;

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;

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.