Table of Contents
Oracle SET Operators
Oracle SET operators combine two or more queries and return single set of result.
Types of SET operators:
- UNION
- UNION ALL
- INTERSECT
- MINUS
UNION Operator In Oracle:
UNION operators combine two or more queries and return the distinct rows from all of them. It will automatically removes the duplicate rows.
CREATE TABLE EMPLOYEES_UNION_1 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_UNION_2 AS SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID, SALARY, DEPARTMENT_ID FROM EMPLOYEES WHERE DEPARTMENT_ID = 30;
SELECT * FROM EMPLOYEES_UNION_1;
SELECT * FROM EMPLOYEES_UNION_2;
In the above example, we have created two tables EMPLOYEES_UNION_1 and EMPLOYEES_UNION_2.
Now we will create duplicate record in EMPLOYEES_UNION_1.
Insert into EMPLOYEES_UNION_1 (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.
SELECT * FROM EMPLOYEES_UNION_1 ORDER BY EMPLOYEE_ID;
SELECT * FROM EMPLOYEES_UNION_1 UNION SELECT * FROM EMPLOYEES_UNION_2 ORDER BY 1;
Although, we have created duplicate records in EMPLOYEES_UNION_1 table for the EMPLOYEE_ID 208, but after using the UNION operator single record has been shown in the result set. ORDER BY clause should be used at the end of all queries. 1 in the ORDER BY clause represents the first column in the SELECT list, i.e. EMPLOYEE_ID.
If we try to include ORDER BY command with the first SELECT statement, then oracle will raise error.
SELECT * FROM EMPLOYEES_UNION_1 ORDER BY 1 UNION SELECT * FROM EMPLOYEES_UNION_2;
ORA-00933: SQL command not properly ended 00933. 00000 - "SQL command not properly ended" *Cause: *Action: Error at Line: 18 Column: 1
But we can use subquery to modify the above command so that order by can be used in the first SELECT statement.
SELECT * FROM ( SELECT * FROM EMPLOYEES_UNION_1 ORDER BY 1) UNION SELECT * FROM EMPLOYEES_UNION_2;
So we can see that by using subquery we can ignore the error raised by oracle. In this way we can use ORDER BY clause against each query used in UNION operator. Data from each query will be appeared in the final result as per the order they appeared in the main query.
UNION ALL Operators In Oracle:
UNION ALL operator combine all the queries and returns all the rows from all the queries including duplicates.
In our example, we will now add a new row in EMPLOYEES_UNION_2 table.
Insert into EMPLOYEES_UNION_2 (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,30);
1 row inserted.
SELECT * FROM EMPLOYEES_UNION_2;
Now if we use the UNION ALL operator then the EMPLOYEE_ID 201 from table EMPLOYEES_UNION_2 and duplicate records for EMPLOYEE_ID 208 from table EMPLOYEES_UNION_1 will be displayed.
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID, SALARY, DEPARTMENT_ID, 1 SEQ FROM EMPLOYEES_UNION_1 UNION ALL SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID, SALARY, DEPARTMENT_ID, 2 SEQ FROM EMPLOYEES_UNION_2 ORDER BY SEQ, EMPLOYEE_ID;
In the above example, we have used SEQ column to differentiate the rows of table EMPLOYEES_UNION_1 with the same of table EMPLOYEES_UNION_2.
Lets check another example.
CREATE TABLE EMPLOYEES_UNION_3 AS SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID, SALARY, DEPARTMENT_ID FROM EMPLOYEES WHERE DEPARTMENT_ID = 60;
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID, SALARY, DEPARTMENT_ID FROM EMPLOYEES_UNION_3;
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID, SALARY, DEPARTMENT_ID FROM EMPLOYEES_UNION_1 UNION ALL SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID, SALARY, DEPARTMENT_ID FROM EMPLOYEES_UNION_3 ORDER BY DEPARTMENT_ID, EMPLOYEE_ID;
In the above example, we have used the UNION ALL operator which has included all the rows from table from table EMPLOYEES_UNION_1 with duplicate and all the rows from table EMPLOYEES_UNION_3.
RELATED TOPICS:
