SQL MINUS Operator

If you join two SELECT statements together with a MINUS, the output from the second SELECT is removed from the output from the first SELECT before the results are displayed. As always, it is easier to explain by using an example. First create a table for odd and even numbers and another table just for odd numbers:

SQL> create table odd_and_even
  2  (col1 number)
  3  /

Table created.

SQL> create table odd
  2  (col1 number)
  3  /

Table created.

SQL>

Now add some odd and even numbers to the first table then insert the contents again so that each number is stored twice (you will see why later):

SQL> insert into odd_and_even values (1);

1 row created.

SQL> insert into odd_and_even values (2);

1 row created.

SQL> insert into odd_and_even values (3);

1 row created.

SQL> insert into odd_and_even values (4);

1 row created.

SQL> insert into odd_and_even select * from odd_and_even;

4 rows created.

SQL> select * from odd_and_even

  2  /

      COL1

----------

         1

         2

         3

         4

         1

         2

         3

         4

8 rows selected.

SQL>

Now insert some odd numbers into the second table but do not replicate the values:

SQL> insert into odd values (1);

1 row created.

SQL> insert into odd values (3);

1 row created.

SQL> select * from odd

  2  /

      COL1

----------

         1

         3

SQL>

Now display the contents of the first table but before displaying the results, use the MINUS statement to remove any values which appear in the second table:

SQL> select col1 from odd_and_even

  2  minus

  3  select col1 from odd

  4  /

      COL1

----------

         2

         4

SQL>
  

Note that there were four odd numbers in the first table i.e. 1, 3, 1, 3 but only two odd numbers in the second table i.e. 1, 3. Even so, all the odd numbers have been subtracted from the output. This is equivalent to either of the following two SQL statements: 

SQL> select distinct col1 from odd_and_even

  2  where col1 not in

  3  (select col1 from odd)

  4  /

      COL1

----------

         2

         4

SQL> select distinct col1 from odd_and_even x

  2  where not exists

  3  (select col1 from odd

  4   where col1 = x.col1)

  5  /

      COL1

----------

         2

         4

SQL>