The usage of union in Oracle

The purpose of the UNION instruction is to merge the results of the two SQL statements so that you can view the query results you want.

For example:

SELECT Date FROM Store_Information

UNION

SELECT Date FROM Internet_Sales

Note: in union usage, the field types of the two select statements match and the number of fields should be the same. As in the example above, more complex situations will be encountered in the actual software development process. For more information, please see the example below.

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 * from (select FL_ID from FLDA WHERE ZT_ID=2006030002) a where XMDA.fl_id=a.fl_id)

Order by type,FL_PARENTID, FL_ID

This sentence means to query two sql statements union, the condition of the query is to see whether the FL_ID in the XMDA table matches the FL_ ID value in the main table FLDA.

UNION filters out duplicate records after table linking, so it sorts the resulting result set after table linking, deletes duplicate records and returns the result.

UNION ALL will be encountered in the query, its use is the same as union, except that union contains the function of distinct, it will remove the duplicate records of the two tables, but union all will not, so in terms of efficiency, union all will be a little higher, but in practice it does not use much.

The header uses the field of the first connection block.

UNION ALL simply merges the two results and returns. In this way, if there is duplicate data in the two result sets returned, the returned result set will contain duplicate data.

In terms of efficiency, UNION ALL is much faster than UNION, so if you can confirm that the two merged result sets do not contain duplicate data, use UNION ALL, as follows:

Use union all as much as possible, because union needs to sort, remove duplicate records, and is inefficient