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
