Oracle Minus

The sql minus operator is used between two SQL statements and returns only rows returned by the first query but not by the second:

SELECT column_name FROM table_name1
MINUS
SELECT column_name FROM table_name2;

Very useful, for instance to make a comparison between two tables and insert the values on the second table that do not exist in the 1st one. For e.g.:

declare

cursor c1 is
SELECT emp_name FROM employers
MINUS
SELECT emp_name FROM employers_bck;

begin
    for val in c1 loop
        insert into employers_backup(emp_name, inserted_date)
        values (val.emp_name, sysdate);
    end loop;
    commit;
end;
/

It could also be done by using cursors with parameters, passing the emp_name from employers table as a parameter, and then inserting the non-existent employers from employers table, in the employers_bck table.

But this approach is very bad regarding performance. So in these situations is very recommended to use SQL Minus as shown above.

Advertisement