Oracle--union and UNION ALL

When we need to display the results of two SELECT statements as a whole, we need to use either the Union or the union ALL keyword.

The Union works by combining multiple results together to display them.

The difference between union and Uinon all is that the union will weigh the repeated results in the result set, and union all will show all the results.

Union: Sets an assembly operation on two (or more) result sets, excluding duplicate rows, and sorting the default rules.

UNION ALL: Sets the two result set operations, including repeating rows, without sorting.

The ORDER BY clause can be specified in the last result set to change the ordering method.

Examples are as follows

User table T_user as follows

Dealer Table T_fchs as follows

Union joins two tables, showing only two fields: Name and phone number

Select*from (selecttu.user_name as Name,tu.telephonefromt_user tu) union (selecttf.fchs_name,tf.fchs_telephonefromt_ FCHS TF)

The results are as follows:

Union ALL connection Two sheets

Select*from (selecttu.user_name as Name,tu.telephonefromt_user tu) union All (Selecttf.fchs_name,tf.fchs_ Telephonefromt_fchs TF)

The results are as follows:

From the above: Union is going to be heavy (remove the line of name: Miller), and union all is displayed.

Ii. usage of intersect and minus

Intersect: An intersection of two result sets, excluding duplicate rows. Default Rule Ordering

Example: intersection of T_user table and t_fchs table

Select*from (selecttu.user_name as Name,tu.telephonefromt_user tu) Intersect (selecttf.fchs_name,tf.fchs_ Telephonefromt_fchs TF)

The results are as follows:

Note: From two tables, the result of the intersection is only this one.

Third, the use of minus

Minus: Differential operation on two result sets, excluding duplicate rows, with default ordering

Minus's role is to go with a different

Select*from (selecttu.user_name as Name,tu.telephonefromt_user tu) minus (selecttf.fchs_name,tf.fchs_telephonefromt_ FCHS TF)

The results are as follows:

Note: Thedifference between the T_user and the T_fchs operation results in

T_user the same as in the T_fchs table, different (only for t_user table ) left.

Oracle--union and UNION ALL