SET operator (UNION/UNIONALL/INTERSECT/MINUS)

Source: Internet
Author: User
Union is inefficient because it requires repeated value scanning. If the merge statement does not intentionally delete duplicate rows, the number of SQL statement fields to be joined using UnionAll must be the same, and the field types must be compatible (Consistent ); if we need to display the results of two select statements as a whole, we need to use union or union

Union is inefficient because it requires repeated value scanning. If the merge statement does not intentionally delete duplicate rows, use Union All to join the same number of SQL statement fields, and the field types must be compatible (Consistent ); if we need to display the results of two select statements as a whole, we need to use union or union

Union is inefficient because it requires repeated value scanning. If the merge operation does not intentionally delete duplicate rows, use Union All

The number of fields in the two SQL statements to be combined must be the same, and the field type must be "compatible" (Consistent );

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, Union will automatically compress the repeated results in multiple result sets, while union all will display all the results, whether 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 the Difference Operation on two result sets, excluding duplicate rows, and sorts the default rules at the same time.


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

For example:
Create two tables: s1 and s2
Create table s1
(
Id number (10 ),
Name varchar2 (20 ),
Age number (10)
)

Create table s2
(
Id number (10 ),
Name varchar2 (20 ),
Age number (10)
)

Insert data:
Insert into s1 values (1, 'Liu bei ', 51 );
Insert into s1 values (1, 'andy Lau ', 54 );
Insert into s1 values (3, 'zhang Xueyou ', 54 );
Insert into s1 values (4, 'day', 54 );

Insert into s2 values (1, 'Liu bei ', 51 );
Insert into s2 values (3, 'zhangfe', 52 );
Insert into s2 values (4, 'guan Yu ', 53 );

1. Union: Perform Union operations on two result sets, excluding duplicate rows, and sort the default rules at the same time;
Select * from s1
Union
Select * from s2;

Result set:

ID NAME AGE
1 1 Liu Bei 51
2 1 Andy Lau 54
3 3 Zhang Fei 52
4 3 Zhang Xueyou 54
5 4 Guan Yu 53
6 4 Dawn 54

2. Union All: Perform Union operations on two result sets, including duplicate rows without sorting;
Select * from s1
Union all
Select * from s2;

Result set:

ID NAME AGE
1 1 Liu Bei 51
2 1 Andy Lau 54
3 3 Zhang Xueyou 54
4 4 Dawn 54
5 1 Liu Bei 51
6 3 Zhang Fei 52
7 4 Guan Yu 53

3. Intersect: intersection of two result sets, excluding duplicate rows, and sorting by default rules;
Select * from s1
Intersect
Select * from s2;

Result set:

ID NAME AGE
1 1 Liu Bei 51

4. Minus: Perform the Difference Operation on the two result sets, excluding duplicate rows, and sort the default rules at the same time.
Select * from s1
Minus
Select * from s2;

Result set:

ID NAME AGE
1 1 Andy Lau 54
2 3 Zhang Xueyou 54
3 4 Dawn 54

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.