Oracle set operation functions: Union, Union All, Intersect, Minus, intersectminus
Union: Perform Union operations on two result sets, excluding duplicate rows, and sort the default rules at the same time;
Union All: Perform Union operations on the 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 sorting by default rules. Find out which records in the first SQL query that are not in the query results of the second SQL statement are better than not exists, not in
You can specify the Order by clause in the last result set to change the sorting method.
After multiple result sets in oracle are union, the original sorting is maintained.
Feasible
However, you must store each result set in the subquery.
For example:
SELECT *
FROM (SELECT '002 ', 'China', ''from dual order by 1)
UNION ALL
SELECT *
FROM (SELECT '001', 'mat', ''from dual order by 1)
UNION ALL
SELECT * FROM (SELECT '003 ', 'English', ''from dual order by 1)
However, if you do not write it in a subquery
For example:
SELECT '002 ', 'China'
FROM DUAL
Order by 1
UNION ALL
SELECT '001', 'mat'
FROM DUAL
Order by 1
UNION ALL
SELECT '003 ', 'English' from dual order by 1
This method is certainly not, and SQL reports an error.
Oracle query problems?
SELECT Field 1 FROM table 1
UNION
SELECT Field 2 FROM table 2