Recently, two select results of union are used in a project. When debugging SQL statements, an error is always reported. The error only indicates that there is a problem near union, because group is used in SQL, maybe union does not support this function. Due to the tight schedule, merge operations are performed in the program first. However, programmers can never give up on the perfection requirements of code. Therefore, they often feel uncomfortable when they do not understand it.
After carefully checking the MS online help, find the following description in the description of UNION:
If the UNION operator is used, SELECT statements cannot contain their own order by or COMPUTE clauses. However, only one order by or COMPUTE clause can be used after the last SELECT statement. This clause applies to the final combination result set. You can only specify the group by and HAVING clauses in each SELECT statement.
In this section, union does not mean that it cannot be used for group. Instead, it means that order by cannot be included in each Select statement. In my statements, this is exactly what I guess is wrong. After trying to remove the order, no error will be reported. That is to say, when union is used, each query group can, but cannot order or compute. Is there any way to make group necessary? Under normal circumstances, it can only be used at the end and sorted for the combined result set. The group I mentioned earlier cannot be used for the final result set, but only for each query.
This is a normal usage. Some people want to sort each query first and then union, which also has an abnormal usage, similar:
Select * from (select a from [table] order by a) union...
In addition, all can be added after union. By default, duplicate items are deleted during union. If all is added, no filtering is performed and all results are combined. If you can determine that there are no repeated items in each query result, it is best to include all, because this can still improve the efficiency.