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

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 usingUNION
, 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