What is the diff between NOT IN and MINUS. As per the SET theory, both should yield the same results.
Lets take an example : From the scott schema by using the Emp and Dept table, if the following queries were fired to get the list of DEPARTMENTS that do not have any EMPLOYEE, then,
and
both queries will return no rows.
--------------------------------------------------------------
Now, lets look at the following data:
DEPARTMENT table got few more rows
EMPLOYEE table
For the first row, employee no 9999, there is no department assigned. Deptno is NULL.
If the below queries were fired, to get the list of departments that does not have any employee, one row (department no 70) should be returned.
and
But surprisingly, first query will return no rows and the second will return the department no 70 because this dept does not have any employee.
Why? Its because, in the first query, each deptno from dept table will be compared with all the deptnos along with the NULL in the emp table. Remember, comparison of a value with NULL will be FALSE. So the deptno 70 comparison with NULL will be false and deptno 70 will not be returned.
Lets take an example : From the scott schema by using the Emp and Dept table, if the following queries were fired to get the list of DEPARTMENTS that do not have any EMPLOYEE, then,
SELECT deptno FROM dept WHERE deptno NOT IN (SELECT deptno FROM emp);
and
SELECT deptno FROM dept
MINUS
SELECT deptno FROM emp;
both queries will return no rows.
--------------------------------------------------------------
Now, lets look at the following data:
DEPARTMENT table got few more rows
DEPTNO | DNAME | LOC |
10 | ACCOUNTING | NEW YORK |
20 | RESEARCH | DALLAS |
30 | SALES | CHICAGO |
40 | OPERATIONS | BOSTON |
50 | QUALITY | CHICAGO |
60 | IT | CHICAGO |
70 | MARKETING | NEY YORK |
EMPLOYEE table
For the first row, employee no 9999, there is no department assigned. Deptno is NULL.
EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | STATUS |
9999 | James | James | 7782 | 23-Jan-82 | 1300 | (null) | (null) | Y |
7369 | SMITH | CLERK | 7902 | 17-Dec-80 | 800 | (null) | 20 | Y |
7499 | ALLEN | SALESMAN | 7698 | 20-Feb-81 | 1600 | 300 | 30 | Y |
7521 | WARD | SALESMAN | 7698 | 22-Feb-81 | 1250 | 500 | 40 | Y |
7566 | JONES | MANAGER | 7839 | 2-Apr-81 | 2975 | (null) | 20 | Y |
7654 | MARTIN | SALESMAN | 7698 | 28-Sep-81 | 1250 | 1400 | 50 | N |
7698 | BLAKE | MANAGER | 7839 | 1-May-81 | 2850 | (null) | 30 | Y |
7782 | CLARK | MANAGER | 7839 | 9-Jun-81 | 2450 | (null) | 10 | N |
7788 | SCOTT | ANALYST | 7566 | 19-Apr-87 | 3000 | (null) | 20 | Y |
7839 | KING | PRESIDENT | (null) | 17-Nov-81 | 5000 | (null) | 10 | N |
7844 | TURNER | SALESMAN | 7698 | 8-Sep-81 | 1500 | 0 | 30 | Y |
7876 | ADAMS | CLERK | 7788 | 23-May-87 | 1100 | (null) | 20 | N |
7900 | JAMES | CLERK | 7698 | 3-Dec-81 | 950 | (null) | 30 | Y |
7902 | FORD | ANALYST | 7566 | 3-Dec-81 | 3000 | (null) | 20 | N |
7934 | MILLER | CLERK | 7782 | 23-Jan-82 | 1300 | (null) | 60 | Y |
If the below queries were fired, to get the list of departments that does not have any employee, one row (department no 70) should be returned.
SELECT deptno FROM dept WHERE deptno NOT IN (SELECT deptno FROM emp);
and
SELECT deptno FROM dept
MINUS
SELECT deptno FROM emp;
But surprisingly, first query will return no rows and the second will return the department no 70 because this dept does not have any employee.
Why? Its because, in the first query, each deptno from dept table will be compared with all the deptnos along with the NULL in the emp table. Remember, comparison of a value with NULL will be FALSE. So the deptno 70 comparison with NULL will be false and deptno 70 will not be returned.
Comments
Post a Comment