oracle database: collective operation

The collection operation is to concatenate multiple result sets through some keywords. For example, if you check a result set A1 in table A, and check a result set B1 in table B, you can perform union and intersection operations on A1 and B1.

  • Union , Union (de-duplication), the union operation of the two result sets, excluding duplicate rows, and the default sorting rule (asc)
  • Union All , Complete set (no deduplication), union operation of two result sets, including duplicate rows, no sorting
  • Intersect , Intersection (find duplicates), perform intersection operations on the two result sets, excluding duplicate rows, and sort by default rules at the same time.
  • Minus, Difference set (minus duplicates), the difference set operation is performed on the two result sets, excluding duplicate rows, and the default rule sorting is performed at the same time.

There is a condition for the collection operation, that is, the number of fields in the two result sets and the field types correspond one-to-one.
Here is a virtual table dual as an example:

select 'a','b' from dual;
select 'c','d' from dual;

These two statements respectively query a result set with one row and two columns in the virtual table, and the type of the result set is both carchar, and the set operation can be performed.
Union operation:

select 'a','b' from dual union select 'c','d' from dual;

The results are as follows:

Insert picture description here


two rows and two columns appear in the virtual table, and they are the contents of the two result sets. You can also use multiple to unionachieve the union of multiple sets:

select 'a','b' from dual 
union 
select 'c','d' from dual
union
select 'a','c' from dual 
union 
select 'c','d' from dual;

Note that there are a total of 4 result sets, of which two result sets are exactly the same. In order to see unionthe de- duplication effect

Insert picture description here


as shown in the figure above, there are no duplicate rows in the union result.
Example 2 union all::

select 'a','b' from dual 
union 
select 'c','d' from dual
union
select 'a','c' from dual 
union all
select 'c','d' from dual;

Pay attention to the above code, union allas shown in the figure above in front of the repeated results

Insert picture description here


, and the repeated content will also be queried.
Example 3: intersectFind the intersection

(select 'a','b' from dual 
union 
select 'c','d' from dual)
intersect
(select 'a','c' from dual 
union all
select 'c','d' from dual);

Here, the result set of the two unions is regarded as two tables for the intersection operation, and only cdthe same is the same.

Insert picture description here


The query came out successfully.
Example 4: minusDifference

(select 'a','b' from dual 
union 
select 'c','d' from dual)
minus
(select 'a','c' from dual 
union 
select 'c','d' from dual);

Here the first table has a absum cd, the second table has a acsum cd, and the two tables are subtracted:

Insert picture description here


from the result, only the same content of itself and the second table will be subtracted, and the displayed result set is the rest content.