Shulou(Shulou.com)05/31 Report--

This article is to share with you what the optimization principles of IN&EXISTS and NOT IN&NOT EXISTS are, the editor thinks it is very practical, so I share it with you to learn, I hope you can get something after reading this article, say no more, follow the editor to have a look.

1. The execution process of EXISTS select * from T1 where exists (select null from T2 where y = x) can be understood as follows: copy the code as follows: for x in (select * from T1) loop if (exists (select null from T2 where y = x.x) then OUTPUT THE RECORD end if end loop

For the performance difference between in and exists: if the subquery results in fewer result set records, in should be used when the table in the main query is larger and has an index, otherwise, if the outer main query record is less, the table in the subquery is large, and exists is used when there is an index. In fact, the difference between in and exists is mainly due to the change of the driving order (which is the key to the performance change). If it is exists, then the outer layer table is the driven table and is accessed first, and if it is IN, then the subquery is executed first, so we will aim to drive the quick return of the table, so we will take into account the relationship between index and result set. In addition, NULL is not processed when IN. For example, the result of select 1 from dual where null in is empty. 2. The copy code of NOT IN and NOT EXISTS: NOT EXISTS is as follows: select. From rollup R where not exists (select 'Found' from title T where R.source_id = T.Title_ID)

The copy code is as follows: for x in (select * from rollup) loop if (not exists (that query)) then OUTPUT end if; end

Note: NOT EXISTS and NOT IN cannot completely replace each other, depending on the specific requirements. If the selected column can be empty, it cannot be replaced. For example, the following sentence, look at the difference between them: select XJ y from t X y-- 1 3 3 1 1 2 1 1 3 1 5 select * from t where x not in (select y from t T2) no rows select * from t where not exists (select null from t T2 where t2.y=t.x) x y-- 5 NULL so specific requirements determine the performance difference between not in and not exists: not in only if the subquery The fields after the select keyword are not null constrained or not in is implied. In addition, if the table in the main query is large, and the table in the subquery is small but there are many records, you should use not in and anti hash join. If there are few records in the main query table, more records in the sub-query table, and there are indexes, you can use not exists. In addition, not in can also use / * + HASH_AJ * / or external join + is null NOT IN in cost-based applications. For example: the copy code is as follows: select. From rollup R where not exists (select 'Found' from title T where R.source_id = T.Title_ID)

Change it to (good) select. From title T, rollup R where R.source_id = T.Title_id (+) and T.Title_id is null; or (preferably) sql > select / * + HASH_AJ * /. From rollup R where ource_id NOT IN (select ource_id from title T where ource_id IS NOT NULL) Note: the above only put forward some suggestions in theory, the best principle is that on the basis of the above, we can use the execution plan to analyze and get the best way to write the sentence. '/ / = exists,not exists Summary 1 exists SELECT * FROM anken_m WHERE EXISTS (SELECT my_list_temp_m.sales_code FROM my_list_temp_m WHERE my_list_temp_m.sales_code=anken_m.sales_code) description: 1) query the sales_code stored in both the anken_ m table and the my_list_temp_ m table. 2) sales_code is the primary key of anken_m and the foreign key of my_list_temp_m. Note: 1) the outer query table anken_m is the object of the query. 2) the inner query table my_list_temp_m is a conditional object. 3) the query tables inside and outside cannot be the same. 4) the anken_m table as an association condition does not need to be added after the inner layer query FROM. 5) the left and right order of my_list_temp_m.sales_code=anken_m.sales_code conditions does not affect the query results. 2 not exists SELECT * FROM anken_m WHERE NOT EXISTS (SELECT my_list_temp_m.sales_code FROM my_list_temp_m WHERE my_list_temp_m.sales_code=anken_m.sales_code) description: 1) query sales_code that exists in the anken_ m table but does not exist in the my_list_temp_ m table. 2) sales_code is the primary key of anken_m and the foreign key of my_list_temp_m. Note: 1) the outer query table anken_m is the object of the query. 2) the inner query table my_list_temp_m is a conditional object. 3) the query tables inside and outside cannot be the same. 4) the anken_m table as an association condition does not need to be added after the inner layer query FROM. 5) the left and right order of my_list_temp_m.sales_code=anken_m.sales_code conditions does not affect the query results. 3 Comprehensive use of UPDATE anken_m SET (plan_type_code, branch_name, business_type_code) = (SELECT anken.plan_type_code,anken.branch_name,anken.business_type_code FROM anken WHERE anken.sales_code=anken_m.sales_code) WHERE EXISTS (SELECT anken.sales_code FROM anken) My_list_temp_m WHERE my_list_temp_m.sales_code=anken.sales_code AND anken.sales_code=anken_m.sales_code) description: 1) update the record data of one table with the record data of another table. 2) batch updates with a SQL statement. 2) sales_code is the primary key of anken,anken_m and the foreign key of my_list_temp_m. Note: 1) the fields to be updated in the set statement must correspond to the data source fields one by one, and the conditions in the data source query must be limited to one record. That is, sales_code can uniquely identify a record of anken and a record of anken_m, so as to ensure that the primary key of the record to be updated is the same as that of the data source record. 2) the scope of the data source records can be determined according to the WHERE EXISTS statement, that is, which records in the anken table can be used to update the anken_ m table. So anken_m does not need to be added after FROM in the WHERE EXISTS statement.

These are the optimization principles of IN&EXISTS and NOT IN&NOT EXISTS. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.