PLSQL _ basic Series 3 _ merge operations UNION/union all/MINUS/INTERSET, plsqlinterset

PLSQL _ basic Series 3 _ merge operations UNION/union all/MINUS/INTERSET, plsqlinterset

2014-11-30 BaoXinjian

I. Summary

If we need to display the results of two select statements as a whole, we need to use the Union or Union All keywords. Union (or Union) is used to display multiple results together.

The difference between Union and Union All is that Union will automatically compress the repeated results in multiple result sets, while Union All will display All the results, whether they are repeated or not.

• Union: Perform Union operations on two result sets, excluding duplicate rows and sorting by default rules;
• Union All: Perform Union operations on two result sets, including duplicate rows without sorting;
• Intersect: intersection of two result sets, excluding duplicate rows, and sorting by default rules;
• Minus: performs a difference operation on two result sets, excluding duplicate rows and sorting by default rules;

You can specify the Order by clause in the last result set to change the sorting method.

 

Ii. UNION

UNION operations

The UNION operation returns all rows selected by any query. Returns all rows from multiple tables using the UNION operation, but removes any duplicate rows.

Principles

• The selected columns and columns must be of the same data type as all SELECT statements used in the query. The column names do not have to be the same.
• The union operation is performed on all selected columns.
• The NULL value is not ignored during repeated checks.
• The IN operation has a higher priority than the UNION operation.
• By default, the output is sorted in ascending order of the first column of the SELECT clause.

 

Iii. UNION ALL

Returns results that include all repeated values from two queries.

Principles

• Unlike union, duplicate rows are not filtered and output is not sorted by default.
• You cannot use the DISTINCT keyword.

Note: Except the preceding two points, the principle of union all is the same as that of UNION.

 

Iv. MINUS

INTERSECT)

Returns all public rows in multiple queries using the intersection operation.

Principles

• The number of columns and Data Types selected by the SELECT statement in the query must be the same as those in all the TCT statements used in the query, but the column name does not need to be the same.
• The result is not changed when the sorting of the tables with the intersection is reversed.
• NULL values are not ignored during intersection.

 

5. INTERSET

Subtraction (MINUS)

The subtract operation is used to return the rows returned by the first query, which do not appear in the second query (the first SELECT statement minus the second SELECT statement ).

Principles

• The number of columns and Data Types selected by the SELECT statement in the query must be the same as those in all the TCT statements used in the query, but the column name does not need to be the same.
• For MINUS operations, all columns in the WHERE clause must be in the SELECT clause.

 

Thanks and Regards