Use Cases of Oracle set operations and exists and in

1 set operation

Definition: displays the results of Multiple SQL statements in a logical integrated operation.

By default, set operations are sorted by the first column of the First query in ascending order, except for union all:

Minus: Get difference set, which can be used to test two result sets.

Union: union.

Union all: returns the union set without removing duplicates.

Intersect: intersection

Note:

① Union all is not sorted and does not remove duplicates, which is more efficient than union. If the business permits union all, union is not required.

② When the two tables perform set operations, the field types and numbers must be the same, and the column names do not have to be the same. If they are not the same, the field types should be paid attention to for display and conversion: to_char, to_number, to_date, etc.

③ Operations on multiple sets can change the execution sequence of the SQL engine through parentheses (the default SQL engine is top-down)

④ What union, minus, and intersect have in common:

Remove, sort, and ignore null

⑤ Order by can only appear at the end of the statement. It is the sorting of the operation results of the entire set. The column name, alias, or specified by position cannot be used for columns not displayed in the first select statement.

⑥ The first select statement receives the column name, alias, or location mark. The column name of the query result is consistent with that of the first select statement.

7. It can be used for subqueries.

2 Use Cases of exists and in

You can directly write multi-table join operations without writing subqueries. The reasons are as follows:

① In the parse stage, Oracle CBO converts sub-queries into multi-Table connections as much as possible.

② Sub-query is less readable

③ Complicated logic, subqueries may cause oracle CBO to select the wrong execution path

But if you must write a subquery, is it in or exists? Exists/not exists Is Used to associate subqueries. After 10 Gbit/s, oracle improved in, which is no different from the two.

Technical Principles of in and select:

(I) in: Perform subqueries first and then perform primary queries.

(Ii) exists: Perform the primary query first and then filter it in the subquery.

In and exists usage suggestions:

I. If the restrictive condition is in the subquery, The in

II if the restrictive condition is in the primary query, use exists

You can use the query result set to understand the above section and adopt the optimal matching principle: matching large records with the minimum record. If the restriction is strong, the result set is small. Otherwise, the result set is large.

Note:

① Using exists can locate the subquery results as constants without affecting the query results, and the efficiency is high.

For example:

1. SelectE .*
2. FromEmp e
3.  WhereExists
4. (Select1FromDept d
5. WhereE. deptno = d. deptnoAnd
6. D. dname ='Sales')

② Not in may change its behavior due to null, resulting in a discrepancy with the not exists result set. However, in related subqueries, The result sets of not in and not exists are the same.