If your database design allows null values in any column, you need to know how different clauses are treated in your query statement.
From the surface, this case may be displayed, that is, the SQL clause in and EXISTS can be interchangeable. However, when processing NULL values, their performance is very different, and the results obtained are also likely to be different. The problem stems from such a fact that in an Oracle database, a NULL value means unknown, so any comparison or operation of a NULL value is invalid, and any testing of NULL has been ignored. For example, the following query statements will not return any lines:
Select 'True' from Dual Where 1 = NULL DIMER;
SELECT 'TRUE' from Dual Where 1! = NULL;
Value 1 can not be said to be equal to NULL, and cannot say that it is not equal to NULL. Only a true null value will be returned only when null is NULL and returns a line.
Select 'True' from Dual Where 1 is NULL;
Select 'True' from Dual Where Null Is Null;
When you use in, it is equivalent to telling SQL to accept a value and compare it with each value or set of values used in a list. As long as there is any NULL value, it will not return any lines, even if both values are null.
Select 'True' from Dual Where Null In (NULL);
SELECT 'TRUE' from Dual Where (NULL, NULL) IN (NULL, NULL);
Select 'True' from Dual Where (1, Null) In ((1, NULL));
An in function is equivalent to = any clause:
Select 'True' from Dual Where Null = Any (NULL);
Select 'True' from Dual Where (Null, NULL) = Any (null, null);
Select 'True' from Dual Where (1, NULL) = Any ((1, NULL));
When you use a format with an exists equivalent, the SQL accounts accounts, but ignores the values in the subquery, even if you return NULL.
Select 'True' from Dual WHERE EXISTS (SELECT NULL from DUAL);
SELECT 'TRUE' from Dual WHERE EXISTS (SELECT 0 from Dual Where Null Is Null);
From logically, IN is the same as EXISTS. The IN clause is the value returned by the sub-query in the external query, and the dropping line is filtered; the exists clause compares those values inside the subquery and filter out rows. In the event of a NULL value, those rows that appear as the result are the same.
SELECTENAME FROM EMP WHERE Empno in (SELECT MGR from EMP);
SELECTENAME FROM EMP E WHERE EXISTS (SELECT 0 from Emp Where Mgr = E.Empno);
However, when the logic is converted into the use of NOT IN and NOT EXISTS, the problem will return to different rows (the first query will return 0 line; the second returned data - they are different Query): SELECTENAME FROM EMP WHERE Empno Not in (SELECT MGR from EMP);
SELECTENAME FROM EMP E Where Not Exists (SELECT 0 from Emp Where Mgr = E.EMPNO);
The NOT IN clause is actually the same as each value with = compare, if any one is tested for false or null, it will fail. E.g:
SELECT 'TRUE' from Dual Where 1 Not in (NULL, 2);
SELECT 'TRUE' from Dual Where 1! = Null and 1! = 2;
SELECT 'TRUE' from Dual Where (1,2) Not in ((2, 3), (2, NULL);
SELECT 'TRUE' from Dual Where (1, NULL) Not in ((1, 2), (2, 3));
These queries will not return any rows. And the second is more worthy, 1! = NULL is NULL, so it is wrong for the entire WHERE condition. They will run like this:
SELECT 'TRUE' from Dual Where 1 Not in (2, 3);
SELECT 'TRUE' from Dual Where 1! = 2 and 1! = 3;
As long as you block the system back NULL in the results, you can still use the Not in inquiry before this (the same, these can run, but I assume that Empno is not null, in our case, this is a good assumption):
SELECTENAME FROM EMP WHERE Empno NOT IN (SELECT MGR from Emp Where Mgr Is Not Null);
SELECTENAME FROM EMP WHERE Empno Not in (SELECT NVL (MGR, 0) from EMP);
Due to the difference between IN, EXISTS, NOT IN, and NOT EXISTS, you can avoid a very common problem when null has null in the data of a subquery.
