From the surface, IN and EXITS SQL statements are interchangeable and equivalent. However, they will have great differences in handling UULL data, and result in different results. The root of the problem is in an Oracle database, a NULL value means unknown variables, so the result of the comparison function of the operation NULL value is also an unknown variable, and the value returns NULL is usually ignored. For example, the following queries will not return a row of values:
Select 'True' from Dual Where 1 = NULL DIMER;
SELECT 'TRUE' from Dual Where 1! = NULL;
Only is NULL can return true and return a line:
Select 'True' from Dual Where 1 is NULL;
Select 'True' from Dual Where Null Is Null;
When you choose to use I, you will tell SQL to select a value and compare each other. If the NULL value exists, it will not return a row, even if both 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));
A IN statement is equivalent to = any statement: 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 an EXISTS equivalent form of statement, SQL will calculate all rows and ignore the values in the subquery. Select 'True' from Dual WHERE EXISTS (Select Null From Dual); SELECT 'TRUE' from Dual WHERE EXISTS (SELECT 0 from Dual Where Null Is Null); IN and EXISTS are logically identical. The in statement is compared to the value returned by subqueries and filtering some rows in the output query. The EXISTS statement is a row value and filter some lines in the subquery. The result of the row is the same for the condition of the NULL value. 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 logic is used inversely, NOT IN and NOT EXISTS, the problem will Generation: 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); NOT IN statement is essentially equivalent to use = compare each value, If the test is false or NULL, the result is a comparison failure. For example: 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 line. The second query statement is more obvious, that is, 1! = Null, so the entire WHERE is False.
However, these queries can be variable: select 'true' from dual where 1 not in (2, 3); select 'true' from dual where 1! = 2 and 1! = 3; You can also use Not in inquiries, as long as You guarantee that the returned value does not appear null values: SELECTENAME FROM EMP WHERE Empno NOT NULL; SELECTENAME FROM EMP WHERE Empno Not in (SELECT NVL (MGR, 0) from EMP); Understand the difference between in, exists, not in, and NOT EXISTS, when Null appears in any subquery, you can avoid some common problems. Scott Stephens has worked more than 13 years in Oracle, including technical support, e-commerce, market development, and software development.
