About Oracle in in,exists with not in, not exists

This paper briefly discusses the problems in use of in,exists and not in and not exists, mainly the choice of keywords, the optimization of SQL

* Note: The following example is a table with Oracle built-in users, and if you do not select the Do not install Database sample table when you install Oracle it should be installed

1, in and exists

In statement:

SELECT *  from hr.employees T1 WHERE inch (                         SELECT  t2.employee_id                         from  hr.job_history T2                                                );

Exists statement:

SELECT *  from hr.employees T1 WHERE EXISTS (              SELECT1from                hr.job_history T2              WHERE  =  t1.employee_id                                   );

You can see that the results are the same, which means that two queries can meet the needs of our business. But the question is, is it quicker to query?

In and exists can be used to achieve the choice of data, but the efficiency of the two will often vary depending on the scene . The reasons are as follows:

In is a hash of the table of the main table and the subquery, while the exists is the loop loop of the main table, and each loop loop is queried on the internal table. So we've always thought that exists is not accurate in its high efficiency . If the query of two table size is equal, then with in and exists difference is not small, if two tables in a smaller one larger, then the subquery table large with exists, sub-query table small with in;

Namely: Table A (small table), table B (large table)

Select *  from where inch　　(Select from B) -- > Low efficiency, using the index of CC column on the a table; Select *  from where exists　　(Selectfromwhere cc=a.cc) -- > High efficiency, using the index of CC column on B table. 

On the contrary:

Select *  from where inch　　(Select from A) -- > High efficiency, using the index of CC column on B table Select *  from where exists　　(Selectfromwhere cc=b.cc) -- > Low efficiency, using the index of the CC column on table A. 

2, not in and not EXISTS

Not in statement:

SELECT *  from HR. EMPLOYEES T1WHERE not in (                             SELECT  T2. employee_id                              from  HR. Job_history T2                            );

NOT EXISTS statement:

SELECT *  from HR. EMPLOYEES T1WHEREnotEXISTS  (                  SELECT1                   from HR. Job_history T2                  WHERE=  T1. employee_id                 );

The contrast of not in,not exists is quite different from that of in,exists, because:

If the query statement uses not in, then the primary table, the subquery table is full table scan , No index is used , and not exists's subquery can still use the index on the table. so no matter which table is large, using not exists is faster than not.

and The pit Daddy thing hasn't finished so soon!

And show me a little bit more about the pit daddy thing.

--Constructing a temporary table Tmp1 withTmp1 as (SELECT 1  asField1,2  asField2 fromDualUNION  AllSELECT 1  asField1,3  asField2 fromdual),--multiple with as comma separated--Constructing a temporary table TMP2Tmp2 as (SELECT 1  asField1,2     asField2 fromDualUNION  AllSELECT 1  asField1,NULL  asField2 fromdual)SELECT * fromTMP1 T1WHERE  not EXISTS (                  SELECT 1                   fromTMP2 T2WHERET1.field2=t2.field2);

The results are as follows:

Nothing unusual, but in the words of not in the pit father's things will appear!

--Constructing a temporary table Tmp1 withTmp1 as (SELECT 1  asField1,2  asField2 fromDualUNION  AllSELECT 1  asField1,3  asField2 fromdual),--multiple with as comma separated--Constructing a temporary table TMP2Tmp2 as (SELECT 1  asField1,2     asField2 fromDualUNION  AllSELECT 1  asField1,NULL  asField2 fromdual)SELECT *  fromTMP1 T1WHERET1.field2 not inch (                        SELECTt2.field2 fromtmp2 T2);

The results are as follows:

WTF!!!!!!!

Why is it different??????

When not is used, it invokes a subquery , and when not exists is used, it invokes the associated subquery . If any one of the records returned in the subquery contains a null value, the query will not return any records . This is the cause of our problems, so in general we will use NOT exists instead of not

About Oracle in in,exists with not in, not exists