Oracle union/unionall/Intersect/Minus usage

The orderby clause must be written in the last result set, and its sorting rules change the sorting result after the operation.

The order by clause must be written in the last result set, and its sorting rules will change the sorting result after the operation.

Union: Perform Union operations on two result sets, excluding duplicate rows, and sort the default rules at the same time;

Union All: Perform Union operations on the two result sets, including duplicate rows without sorting;

Intersect: intersection of two result sets, excluding duplicate rows, and sorting by default rules;

Minus performs the Difference Operation on two result sets, excluding duplicate rows and sorting by default rules.

The order by clause must be written in the last result set, and its sorting rules will change the sorting result after the operation. It is effective for Union, Union All, Intersect, and Minus.

The header uses the first connection block field.

1. Union

In union usage, the Field Types of the two select statements match, and the number of fields must be the same.

The two tables connected by UNION are the same table. Using UNION filters duplicate rows and displays the data of one table.

In the following SQL query statement, the two tables are identical, so the query displays the information of a single table.

SCOTT @ bys1> select deptno, dname as "aa", loc from dept union select deptno, dname, loc from dept;
DEPTNO aa LOC
-------------------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

2. Union All

Union all: its usage is the same as union, except that union contains the distinct function. It removes duplicate records from two tables,

Union all does not, so in terms of efficiency, union all is a little higher, but not a lot in practice.

In terms of efficiency, union all is much faster than UNION. Therefore, if we can confirm that the two results of the merge do not contain duplicate data, we will use union all.
Try to use union all because union needs to be sorted to remove duplicate records, which is less efficient.

In the following SQL query statement, the two tables are identical, so the query shows the information in the two tables.

SCOTT @ bys1> select deptno, dname, loc as "aa" from dept union allselect deptno, dname, loc as "bb" from dept;
Deptno dname aa
-------------------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

3. Intersect

Perform the intersection operation on the two result sets, excluding duplicate rows, and sort the default rules at the same time. Return the data contained in the two tables, and retrieve the intersection as the final return result.

In the following SQL query statement, the two tables are identical, so the query displays the information of a single table.
SCOTT @ bys1> select deptno, dname, loc as "aa" from dept intersect select deptno, dname, loc as "bb" from dept;
Deptno dname aa
-------------------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

4. Minus

Perform the Difference Operation on the two result sets, excluding duplicate rows, and sort the default rules at the same time.

The first result set minus the content in the second result set, and the remaining content serves as the final returned result.

The two tables in the following SQL query statement are identical, so no information is displayed in the query.

SCOTT @ bys1> select deptno, dname, loc as "aa" from dept minus select deptno, dname, loc as "bb" from dept;

No rows selected