MINUS Operator In Oracle
MINUS returns all the distinct rows from first select statement which are not present in second select statement.
Below is the graphical representation of MINUS operator.
CREATE TABLE EMPLOYEES_MINUS_30 AS SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID, SALARY, DEPARTMENT_ID FROM EMPLOYEES WHERE DEPARTMENT_ID = 30; CREATE TABLE EMPLOYEES_MINUS_20 AS SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID, SALARY, DEPARTMENT_ID FROM EMPLOYEES WHERE DEPARTMENT_ID = 20;
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID, SALARY, DEPARTMENT_ID FROM EMPLOYEES_MINUS_20 ORDER BY EMPLOYEE_ID;
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID, SALARY, DEPARTMENT_ID FROM EMPLOYEES_MINUS_30 ORDER BY EMPLOYEE_ID;
Till now we have created two tables with completely distinct rows. Also there is no common rows in any of the table.
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID, SALARY, DEPARTMENT_ID FROM EMPLOYEES_MINUS_20 MINUS SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID, SALARY, DEPARTMENT_ID FROM EMPLOYEES_MINUS_30 ORDER BY EMPLOYEE_ID;
Now if we MINUS the data of table EMPLOYEES_MINUS_30 from EMPLOYEES_MINUS_20 then only the data of EMPLOYEES_MINUS_20 will be displayed as we can see in the above example.
Similarly, if MINUS the data of table EMPLOYEES_MINUS_20 from table EMPLOYEES_MINUS_30 then only the rows from EMPLOYEES_MINUS_30 will be displayed as we can see in below example.
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID, SALARY, DEPARTMENT_ID FROM EMPLOYEES_MINUS_30 MINUS SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID, SALARY, DEPARTMENT_ID FROM EMPLOYEES_MINUS_20 ORDER BY EMPLOYEE_ID;
Now we will insert the data of one table into the other to create common rows.
Insert into EMPLOYEES_MINUS_20 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,HIRE_DATE,JOB_ID,SALARY,DEPARTMENT_ID) values (208,'Jhon','Smith','JSMITH',to_date('18-OCT-2003','DD-MON-YYYY'),'PU_CLERK',1000,30); 1 row inserted.
Insert into EMPLOYEES_MINUS_20 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,HIRE_DATE,JOB_ID,SALARY,DEPARTMENT_ID) values (115,'Alexander','Khoo','AKHOO',to_date('18-MAY-2003','DD-MON-YYYY'),'PU_CLERK',3100,30); 1 row inserted.
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID, SALARY, DEPARTMENT_ID FROM EMPLOYEES_MINUS_20 ORDER BY EMPLOYEE_ID;
Insert into EMPLOYEES_MINUS_30 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,HIRE_DATE,JOB_ID,SALARY,DEPARTMENT_ID) values (201,'Michael','Hartstein','MHARTSTE',to_date('17-FEB-2004','DD-MON-YYYY'),'MK_MAN',13000,20); 1 row inserted.
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID, SALARY, DEPARTMENT_ID FROM EMPLOYEES_MINUS_30 ORDER BY EMPLOYEE_ID;
Now we will cee the impact of MINUS operator on both the tables.
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID, SALARY, DEPARTMENT_ID FROM EMPLOYEES_MINUS_20 MINUS SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID, SALARY, DEPARTMENT_ID FROM EMPLOYEES_MINUS_30 ORDER BY EMPLOYEE_ID;
The query result set has returned only one row because other EMPLOYEE_ID 115, 201, 208 are already there in the table EMPLOYEES_MINUS_30.
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID, SALARY, DEPARTMENT_ID FROM EMPLOYEES_MINUS_30 MINUS SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID, SALARY, DEPARTMENT_ID FROM EMPLOYEES_MINUS_20 ORDER BY EMPLOYEE_ID;
Now in the above operation, two rows of EMPLOYEE_ID 201, 208 are missing from EMPLOYEES_MINUS_30, because they are already present in table EMPLOYEES_MINUS_20.
Now we will create duplicate records to check the impact of MINUS operator.
Insert into EMPLOYEES_MINUS_20 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,HIRE_DATE,JOB_ID,SALARY,DEPARTMENT_ID) values (115,'Alexander','Khoo','AKHOO',to_date('18-MAY-2003','DD-MON-YYYY'),'PU_CLERK',3100,30); 1 row inserted.
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID, SALARY, DEPARTMENT_ID FROM EMPLOYEES_MINUS_20 ORDER BY EMPLOYEE_ID;
Insert into EMPLOYEES_MINUS_30 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,HIRE_DATE,JOB_ID,SALARY,DEPARTMENT_ID) values (201,'Michael','Hartstein','MHARTSTE',to_date('17-FEB-2004','DD-MON-YYYY'),'MK_MAN',13000,20); 1 row inserted.
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID, SALARY, DEPARTMENT_ID FROM EMPLOYEES_MINUS_30 ORDER BY EMPLOYEE_ID;
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID, SALARY, DEPARTMENT_ID FROM EMPLOYEES_MINUS_20 MINUS SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID, SALARY, DEPARTMENT_ID FROM EMPLOYEES_MINUS_30 ORDER BY EMPLOYEE_ID;
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID, SALARY, DEPARTMENT_ID FROM EMPLOYEES_MINUS_30 MINUS SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID, SALARY, DEPARTMENT_ID FROM EMPLOYEES_MINUS_20 ORDER BY EMPLOYEE_ID;
Now we can see that the result is same as it was in previous example. So the duplicate records are automatically removed from the final result set.
ORDER BY clause in MINUS
ORDER BY clause will be used with the last query only. Otherwise oracle will raise error.
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID, SALARY, DEPARTMENT_ID FROM EMPLOYEES_MINUS_30 ORDER BY EMPLOYEE_ID MINUS SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID, SALARY, DEPARTMENT_ID FROM EMPLOYEES_MINUS_20;
ORA-00933: SQL command not properly ended 00933. 00000 - "SQL command not properly ended" *Cause: *Action: Error at Line: 24 Column: 1
But if we use a subquery then oracle will allow that. By using subquery we can use ORDER BY clause in each query.
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID, SALARY, DEPARTMENT_ID FROM( SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID, SALARY, DEPARTMENT_ID FROM EMPLOYEES_MINUS_30 ORDER BY EMPLOYEE_ID) MINUS SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID, SALARY, DEPARTMENT_ID FROM EMPLOYEES_MINUS_20;
RELATED TOPICS: