Union learning in Oracle

UnionThe purpose of the command is to combine the results of two SQL statements to 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 must match and the number of fields must be the same. In the preceding example, more complex conditions may occur during the actual software development process, for more information, see the following example.

Select '1' as type, fl_id, fl_code, fl_cname, FLDA. fl_parentid from FLDA
Where zt_id = 1, 2006030002
Union
Select '2' as type, xm_id, X

UnionThe purpose of the command is to combine the results of two SQL statements to 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 must match and the number of fields must be the same. In the preceding example, more complex conditions may occur during the actual software development process, for more information, see the following example.

Select '1' as type, fl_id, fl_code, fl_cname, FLDA. fl_parentid from FLDA
Where zt_id = 1, 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 query condition is to check whether the fl_id in the xmda table matches the fl_id value in the flda of the master table (that is, it exists ).

Union filters out duplicate records after table link. Therefore, after table link, it sorts the generated result sets and deletes duplicate records before returning results.

In the query, Union all is encountered. Its usage is the same as union, except that Union contains the distinct function. It removes duplicate records from two tables, but union all does not, therefore, in terms of efficiency, Union all is a little higher, but not much is used in practice.

The header uses the first connection block field ..........

Union all simply merges the two results and returns them. In this way, if duplicate data exists in the two returned result sets, the returned result sets will contain duplicate data.

In terms of efficiency, Union all is much faster than Union. Therefore, if you can confirm that the two results of the merge do not contain duplicate data, use Union all, as shown below:

Try to use Union all because union needs to be sorted to remove duplicate records, which is less efficient.

M_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 query condition is to check whether the fl_id in the xmda table matches the fl_id value in the flda of the master table (that is, it exists ).

Union filters out duplicate records after table link. Therefore, after table link, it sorts the generated result sets and deletes duplicate records before returning results.

In the query, Union all is encountered. Its usage is the same as union, except that Union contains the distinct function. It removes duplicate records from two tables, but union all does not, therefore, in terms of efficiency, Union all is a little higher, but not much is used in practice.

The header uses the first connection block field ..........

Union all simply merges the two results and returns them. In this way, if duplicate data exists in the two returned result sets, the returned result sets will contain duplicate data.

In terms of efficiency, Union all is much faster than Union. Therefore, if you can confirm that the two results of the merge do not contain duplicate data, use Union all, as shown below:

Try to use Union all because union needs to be sorted to remove duplicate records, which is less efficient.