1. Exists's implementation process
SELECT * from t1 where exists (select null from t2 where y = x)
Can be understood as:
Copy Code 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
Performance differences for in and exists:
If a subquery results in a small number of result sets, the table in the main query should be in if it is large and indexed, whereas if the Outer main query record is small, the table in the subquery is large and indexed with exists.
In fact, our distinction between in and exists is mainly caused by the change in drive order (which is the key to performance change), if it is exists, then the outside table is the driver table, first accessed, if in, then execute the subquery first, so we will take the fast return of the driver table as the target, Then you take into account the relationship between the index and the result set.
In addition, NULL is not treated as:
Select 1 from dual where null in (0,1,2,null)
The result is empty.
2. Not in and not EXISTS:
Execute process of NOT exists
Copy Code code as follows:
Select .....
From Rollup R
Where NOT EXISTS (select ' Found ' from title T
where r.source_id = t.title_id);
Can be understood as:
Copy Code code as follows:
For x in (SELECT * from Rollup)
Loop
if (not EXISTS (this 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.
The following statements, for example, look at their differences:
Select X,y from T;
X y
------ ------
1 3
3 1
1 2
1 1
3 1
5
SELECT * from t where x isn't in (select Y from T T2)
No rows
SELECT * from t where don't exists (select null from T T2
where T2.y=t.x)
X y
------ ------
5 NULL
So the specific needs to decide
Performance differences for not in and not exists:
Not in only in subqueries where the field after the SELECT keyword has a NOT null constraint or if there is such a hint, and if the table in the main query is large and the table in the subquery is small but there are more records, you should use not in and use the anti hash join.
If there are fewer records in the main query table, there are more records in the subquery table, and there is an index, you can use not exists, and in the best possible/*+ hash_aj/or outer joins +is NULL
Not in is better in cost-based applications
Like what:
Copy Code code as follows:
Select .....
From Rollup R
Where NOT EXISTS (select ' Found ' from title T
where r.source_id = t.title_id);
Change to (good)
Select ...
From title T, Rollup R
where r.source_id = t.title_id (+)
and t.title_id is null;
or (good)
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 is only theoretically put forward a number of suggestions, the best principle is that everyone on the basis of the above, can use the implementation plan to analyze, to get the best statement of the wording.
'//=============================
Summary of Exists,not exists
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 in the Anken_m table and My_list_temp_m table.
2) Sales_code is the primary key of the anken_m, the foreign key of My_list_temp_m.
Attention:
1 The outer query table Anken_m is the object of the query.
2 The inner-level query table my_list_temp_m is a condition object.
3 the internal and external level of the query table can not be the same.
4 The Anken_m table as an associated condition does not need to be added after the inner query from.
5) The left and right order of the My_list_temp_m.sales_code=anken_m.sales_code condition does not affect the query result.
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 The query exists in the Anken_m table, but it does not exist in the My_list_temp_m table Sales_code.
2) Sales_code is the primary key of the anken_m, the foreign key of My_list_temp_m.
Attention:
1 The outer query table Anken_m is the object of the query.
2 The inner-level query table my_list_temp_m is a condition object.
3 the internal and external level of the query table can not be the same.
4 The Anken_m table as an associated condition does not need to be added after the inner query from.
5) The left and right order of the My_list_temp_m.sales_code=anken_m.sales_code condition does not affect the query result.
3 Comprehensive Utilization
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 another table with the record data of one table.
2) Use a SQL statement for batch update.
2) Sales_code is the primary key of the anken,anken_m, the foreign key of My_list_temp_m.
Attention:
1 The field to be updated in the SET statement must correspond to the data source field one by one, and the condition in the data source query must qualify a record. That is, according to the Sales_code can uniquely determine a anken record, and anken_m a record, so as to ensure that the record to be updated and the data source record of the primary key is the same.
2 The scope of the data source record can be determined according to the where exists statement, that is, the Anken_m table can be updated with which records in the Anken table. Therefore, Anken_m does not need to be added in the where exists statement.
