Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

The usage of union in Oracle

2025-04-04 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report