SQL A Union B result different from A+B?

I was using Union to merge two SQL queries, but the results are different from a combination of two different queries. Below is my SQL code:

(SELECT CONCAT(Name, '(', LEFT(Occupation, 1), ')')
FROM OCCUPATIONS
ORDER BY Name ASC)
UNION
(SELECT CONCAT('There are a total of ', COUNT(Occupation), ' ', 
LOWER(Occupation), 's.')
FROM OCCUPATIONS
GROUP BY Occupation
ORDER BY Occupation ASC)

If I only run the first half of the query, I get the following result:

Aamina(D) 
Ashley(P) 
Belvet(P) 
Britney(P) 
Christeen(S) 
Eve(A) 
Jane(S) 
Jennifer(A) 
Jenny(S) 
Julia(D) 
Ketty(A)  
Kristeen(S) 
Maria(P) 
Meera(P) 
Naomi(P) 
Priya(D) 
Priyanka(P) 
Samantha(A) 

If I only run the second half of the query, I get the following result:

There are a total of 4 actors. 
There are a total of 3 doctors. 
There are a total of 7 professors. 
There are a total of 4 singers. 

Both results above are in expected order. However, if I run all the query, I get the following result:

Ashley(P) 
Samantha(A) 
Julia(D) 
Britney(P) 
Maria(P) 
Meera(P) 
Priya(D) 
Priyanka(P) 
Jennifer(A) 
Ketty(A) 
Belvet(P) 
Naomi(P) 
Jane(S) 
Jenny(S) 
Kristeen(S) 
Christeen(S) 
Eve(A) 
Aamina(D) 
There are a total of 4 actors. 
There are a total of 3 doctors. 
There are a total of 7 professors. 
There are a total of 4 singers. 

As you may notice, the order of the first half is screwed. Does anyone know why? How does Union different from writting two separate SQL query? Thanks!

1 answer

  • answered 2018-03-13 20:28 Gordon Linoff

    The order is not "screwed". You have no order by for the overall query, just for the subqueries. The ordering is not preserved. You are using UNION, which removes duplicates.

    The safe way to execute this query is:

    select str
    from ((select concat(Name, '(', LEFT(Occupation, 1), ')') as str, 1 as which
           from OCCUPATIONS
          ) union all
          (select concat('There are a total of ', COUNT(Occupation), ' ', 
    lower(Occupation), 's.') as str, 2 as which
           from OCCUPATIONS
           group by occupation
          )
         ) o
    order by which, str