9-oracle_union and union all

Union is a union operation on the result set, which requires that the two collections have the same fields and types.

Union: joins two result sets, excluding duplicate rows, and sorts the default rules

Union all: joins two result sets, including duplicate rows, without sorting

Union: we did two queries on the same table, and no duplicate 2 items appeared in the query results:

Select user_no, dept_code, sales_amt

From t_sales

Union

Select user_no, dept_code, sales_amt

From t_sales

Which statement is sorted by default rule? Is sorted in ascending order in the order in which we query the fields. The above is sorted by user_no,dept_code,sales_amt. To prove what we said, we change the order of the query fields and query by sales_amt,user_no,dept_code:

Select sales_amt, user_no, dept_code

From t_sales

Union

Select sales_amt, user_no, dept_code

From t_sales

The results are clearly sorted in ascending order in what we call the query fields.

Union all: merge the collections of 2 queries without dealing with duplicate rows:

Select user_no, dept_code, sales_amt

From t_sales

Union all

Select user_no, dept_code, sales_amt

From t_sales

We queried the table twice, so there were two records for each record. And the resulting dataset is out of order. In fact, this is our superficial phenomenon, and there is also an order in the returned result set: first return the results of the first query, the order of return is in the order of record insertion, and then return the result set of the second query. The order of return is also in the order of record insertion. This can be verified by the following two scripts:

Select user_no, dept_code, sales_amt

From t_sales a

Where a.sales_amt 3000

The first query returns three blue records.

Select user_no, dept_code, sales_amt

From t_sales a

Where a.sales_amt > 3000

Union all

Select user_no, dept_code, sales_amt

From t_sales a

Where a.sales_amt