Use of union in Oracle

UNIONThe purpose of the directive is to have twoSQLThe result of the statement is merged together,You can view the results of the query you want.

For example:

SELECT Date from Store_information
UNION
SELECT Date from Internet_sales

Note: Unionin use,two xSelectThe field type of the statement matches,and have the same number of fields,as the above example,in the actual software development process,will encounter more complicated situations,Please see the following example for details

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

The meaning of this sentence is to put twoSQLStatementUnionQuery out,the condition of the query is to seeXmdain the tablefl_idwhether and the main tableFldain thefl_idvalues Match,(Which is the existence).

UNIONduplicate records are filtered after the table link is made, so the resulting set of results is sorted after the table is connected, the duplicate records are deleted, and the results are returned.

in the query, you will encounterUNION All,It's usage andUnionsame,justUnioncontaindistinctthe function,it will remove the duplicate records from the two tables.,andUnion Allwon't,so in terms of efficiency, Union allIt'll be a little higher.,but it's not a lot to use in practice..

the header will use the field of the first connection block ....

andUNION AllSimply merge the two results and return them. Thus, if there are duplicate data in the two result sets returned, the returned result set will contain duplicate data.

in terms of efficiency,UNION Allto be more thanUNIONmuch faster, so if you can confirm that the combined two result sets do not contain duplicate data, then use theUNION All, as follows:

try to useUnion All, becauseUnionneed to sort, eliminate duplicate records, and be inefficient

Use of union in Oracle