INTERSECT Operator In Oracle
INTERSECT operators returns the distinct common rows from each query.
Below is the graphical representation of INTERSECT operator.
CREATE TABLE EMPLOYEES_INTERSECT_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_INTERSECT_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_INTERSECT_30 ORDER BY EMPLOYEE_ID;
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID, SALARY, DEPARTMENT_ID FROM EMPLOYEES_INTERSECT_20 ORDER BY EMPLOYEE_ID;
We have created two new tables EMPLOYEES_INTERSECT_30 and EMPLOYEES_INTERSECT_20. Now we will create common rows in both the tables.
Insert into EMPLOYEES_INTERSECT_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.
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID, SALARY, DEPARTMENT_ID FROM EMPLOYEES_INTERSECT_20 ORDER BY EMPLOYEE_ID;
Insert into EMPLOYEES_INTERSECT_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_INTERSECT_30 ORDER BY EMPLOYEE_ID;
Till now we have created a new record in both the tables by taking the data from other table. So from data we can check that the EMPLPOYEE_ID 201 and 208 are present in both the tables. These two rows will be the result of INTERSECT operator.
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID, SALARY, DEPARTMENT_ID FROM EMPLOYEES_INTERSECT_20 INTERSECT SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID, SALARY, DEPARTMENT_ID FROM EMPLOYEES_INTERSECT_30;
Now we will check the duplicity impact of INTERSECT. We will insert the same record again in respective tables.
Insert into EMPLOYEES_INTERSECT_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_INTERSECT_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_INTERSECT_20 ORDER BY EMPLOYEE_ID;
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID, SALARY, DEPARTMENT_ID FROM EMPLOYEES_INTERSECT_30 ORDER BY EMPLOYEE_ID;
Now both the tables have duplicate rows.
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID, SALARY, DEPARTMENT_ID FROM EMPLOYEES_INTERSECT_20 INTERSECT SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID, SALARY, DEPARTMENT_ID FROM EMPLOYEES_INTERSECT_30;
Although we have seen both the tables have duplicate rows, but after using INTERSECT operator, the duplicate rows have been removed from the result query.
ORDER BY clause in INTERSECT
ORDER BY clause will be used with the last query only. Otherwise oracle will raise error.
Insert into EMPLOYEES_INTERSECT_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_INTERSECT_20 ORDER BY EMPLOYEE_ID;
Added a new common row in EMPLOYEES_INTERSECT_20 table.
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID, SALARY, DEPARTMENT_ID FROM EMPLOYEES_INTERSECT_20 INTERSECT SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID, SALARY, DEPARTMENT_ID FROM EMPLOYEES_INTERSECT_30 ORDER BY EMPLOYEE_ID;
Now we will use the ORDER BY clause with the first query to see the impact.
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID, SALARY, DEPARTMENT_ID FROM EMPLOYEES_INTERSECT_20 ORDER BY EMPLOYEE_ID INTERSECT SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID, SALARY, DEPARTMENT_ID FROM EMPLOYEES_INTERSECT_30;
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_INTERSECT_20 ORDER BY EMPLOYEE_ID) INTERSECT SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID, SALARY, DEPARTMENT_ID FROM EMPLOYEES_INTERSECT_30;
RELATED TOPICS: