The efficiency and Difference analysis of Oracle Union and UNION ALL

Union operates on two result sets, excluding duplicate rows, while ordering the default rules;

UNION all sets up and does a set of two result sets, including repeating rows, not sorting

Union ALL is much faster than union, so if you can confirm that the merged two result sets do not contain duplicate data, then use Union ALL, as follows:

Use union all as much as possible, because the Union needs to be sorted to remove duplicate records and inefficient
Union
If a table has more than one indexed column, it is much more efficient to replace the or in the where with Union. Index columns using or can cause a full table scan. If a column does not use an index, you must remember or.

Select date from store_information
Union
Select date from Internet_sales

Note: In union usage, the field types of two SELECT statements match, and the number of fields is the same, as in the example above, in the actual software development process, you will encounter more complex situations, see the following example

Select ' 1 ' as Type,fl_id,fl_code,fl_cname,flda.fl_parentid from Flda
where zt_id=2006030002
Union
Select ' 2 ' as Type,xm_id,xm_code, Xm_cname, fl_id from Xmda
where exists (select fl_id from Flda where zt_id=2006030002) a where xmda.fl_id=a.fl_id)
Order by Type,fl_parentid, fl_id

The meaning of this sentence is to query the union of two SQL statements, and the query condition is to see whether the fl_id in the Xmda table matches the FL_ID value in the Main table Flda.

UNION ALL detailed examples

The purpose of the Union directive is to combine the results of two SQL statements to see the results of your query.

For example:

Sql> select * from A;

ID Name
----------   ----------
1 AA
2 BB
3 cc
6 DD
7 EE

Sql> SELECT * from B;

id                    Addr
----------  ----------
1                      AA
2                      BB
3                      CC
4                       DD
5                      ee

Sql> SELECT * from a
2 UNION ALL
3 SELECT * from B;

ID Name
----------   ----------
1 AA
2 BB
3 cc
6 DD
7 EE
1 AA
2 BB
3 cc
4 DD
5 EE

10 rows have been selected.

Sql> SELECT * from a
2 Union
3 SELECT * from B;

ID Name
----------   ----------
1 AA
2 BB
3 cc
4 DD
5 EE
6 DD
7 EE

7 rows have been selected.

Sql>

Note: In union usage, the field types of two SELECT statements match, and the number of fields is the same, as in the example above, more complex situations are encountered in the actual software development process