Not In Vs Minus in SELECT statement of Oracle

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,
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

DEPTNODNAMELOC
10ACCOUNTINGNEW YORK
20RESEARCHDALLAS
30SALESCHICAGO
40OPERATIONSBOSTON
50QUALITYCHICAGO
60ITCHICAGO
70MARKETINGNEY YORK


EMPLOYEE table
For the first row, employee no 9999, there is no department assigned. Deptno is NULL.

EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNOSTATUS
9999JamesJames778223-Jan-821300(null)(null)Y
7369SMITHCLERK790217-Dec-80800(null)20Y
7499ALLENSALESMAN769820-Feb-81160030030Y
7521WARDSALESMAN769822-Feb-81125050040Y
7566JONESMANAGER78392-Apr-812975(null)20Y
7654MARTINSALESMAN769828-Sep-811250140050N
7698BLAKEMANAGER78391-May-812850(null)30Y
7782CLARKMANAGER78399-Jun-812450(null)10N
7788SCOTTANALYST756619-Apr-873000(null)20Y
7839KINGPRESIDENT(null)17-Nov-815000(null)10N
7844TURNERSALESMAN76988-Sep-811500030Y
7876ADAMSCLERK778823-May-871100(null)20N
7900JAMESCLERK76983-Dec-81950(null)30Y
7902FORDANALYST75663-Dec-813000(null)20N
7934MILLERCLERK778223-Jan-821300(null)60Y



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