How To Use MINUS Operator In Oracle

Looking for some simple example how to use Oracle Minus Operator. Or do you need some Oracle database management help? Our Oracle DBA will help you to find a solutions for your Oracle database management system.

Oracle Minus Operator compares both Select statement outputs and returns unique lines from the upper Select query that are missing from the lower SQL query. The syntax of Oracle Minus Operator is following:

<your_1st_select_statement>
MINUS
<your_2nd_select_statement>;

The first Oracle DBA example has two Oracle Dual tables combined with the Oracle Minus Operator and their values are hard coded as the upper SQL query has number “1” and the lower query has number “2“. Oracle Minus returns lines only from the upper Select statement that do not exist in the lower SQL output, so the operator returns number “1“.

 SELECT 1
  FROM DUAL
MINUS
SELECT 2
  FROM DUAL;

Now we will do some light changes in the lower Select statement and switch number “2” with “1“. The Minus operator will find no difference in both Select outputs and it 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 better the third example we would need to take a look at the following Select statement below. Oracle DBA will use it as the upper Select statement and as you see from the following output it returns two rows with values number “1” and “2“.

 SELECT rownum
  FROM DUAL
CONNECT BY rownum < 3;

On this third Oracle Minus example as we said before we are going to use the SQL query above combined with another simple Dual Select. The lower query returns number “1“. Please take a look at the following example.

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

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

The following two SQL queries will show how does return Oracle Minus operator only unique rows. To do it we need to use the following query; take a look at the output to understand better the example. The SQL query output 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 Select statement above the Oracle DBA will do Minus with an one line table that has only value “1“. In other words 10 lines with repeating values of 1-s and 2-s against one line value 1.

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

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

Now Oracle MINUS works as group by even if all values in both SQL queries are totally different. The first SQL below shows the upper query result:

SELECT 2 AS nr
  FROM DUAL
   CONNECT BY rownum < 4;

The next SQL uses the upper query and it does minus between the second DUAL SQL that has only one row with number “1” value.

SELECT 2 AS nr
  FROM DUAL
   CONNECT BY rownum < 4
 MINUS
SELECT 1
  FROM DUAL;

The output shows only one row with number “2” even we did expect to see 3 rows with number “2” values as the source query shows above. Using the last query shows that Oracle MINUS does DISTINCT or grouping even with no any common values.

See Also:
Oracle Select Oracle Union Oracle Intersect Oracle Home