Union과 Order by

240425 Today I Learn 

해커랭크에서 아래 문제를 풀던 중 mysql에서 UNION 사용시 정렬이 틀어지는 현상을 발견했다. 아래 문제를 풀이하면서 알게된 몇가지 내용들을 기억에 남기고자 기록하려고 한다.

[문제] HackerRank. The Pads!

1. `Occupations`테이블에 있는 모든 이름을 알파벳순으로 나열하고, 직업의 첫글자를 그 뒤에 바로 나열는 쿼리를 작성하라. (예시 : AnActorName(A), ADoctorName(D), AProfessorName(P), and ASingerName(S).) 
2. 각각의 `occupation`이 몇 개인지 그 개수를 구하고 개수를 기준으로 오름차순 정렬해 `There are a total of [occupation_count] [occupation]s.` 형태로 출력하라

문제상황. 왜 정렬이 유지되지 않을까?

코드를 작성하기 전 나의 계획은 1, 2번 각각의 형식과 정렬을 맞춰 각각의 테이블을 union으로 결합하는 것이었는데,그렇게 하니 아래와 같이 정렬이 깨졌다.

 

trial #1

첫번째와 두번째 테이블 모두 concat, count를 조금 더 편하게 이용하기 위해 서브쿼리를 사용했는데, 이 때 서브쿼리 안에서 정렬을 시도해보았다. `name(occupation)`을 출력한 부분의 경우 정렬이 망가져있지만, `There are a total of n occupations.`를 출력한 경우에는 3→4(actors)→4(singers)→7로 올바르게 정렬이 된 모습을 확인할 수 있다.

혼자 끙끙 앓아도 도저히 UNION 외에는 해답이 생각나지 않아서 튜터님을 찾아갔고, 함께 문제를  논의해보게 되었다.

 

trial #2

최종적으로 UNION으로 두 테이블을 결합한 뒤 order by를 해준 결과 위와 같이 문제에서 요구한 정답이 나왔다. 

❓ UNION을 사용할 때 이전 테이블의 정렬이 유지될까

정답은 아니다. 때문에 union하기 이전 각각의 테이블에서 union을 사용하는 것은 굉장히 번거로운 프로세스가 된다. UNION을 사용할 때에는 UNION이 끝난 후 컬럼명을 지정해 정렬을 해주어야 한다.

extra

Hacker rank의 문제에 대한 풀이는 위의 코드로 끝이 났지만, result1컬럼을 기준으로 정렬한 것이기 때문에 `There are a total of n occupations.`라고 출력되는 컬럼이 맨 밑에 붙은게 아닐까 라는 궁금증이 생겼다. (S다음에는 T가 오니까..) 

그래서 위와 같이 `There are a total of n occupations.`를 `count n occupations`로 바꾸어보았다. 위에서 볼 수 있듯, 정렬 기준이 result1이기 때문에 count로 시작하는 두번째 테이블의 출력값들이 6-9행에 위치해있는 것을 확인할 수 있다. 

 

그렇다면, 문장의 시작에 관계 없이 정렬된 테이블 1, 테이블 2를 테이블 순서로 정렬할 수 있을까?

 

아쉽게도 UNION을 활용해서 정렬을 유지하면서 테이블을 순서대로 붙이는 방법은 찾지 못했다. 그러나 UNION ALL을 사용하면 테이블 1과 테이블 2를 순서대로 붙일 수는 있다.

• UNION ALL을 사용하면 중복을 제거하기 위해 쿼리가 전체 테이블을 순서대로 돈다고 한다...! (사실 정확히는 기억이 안나는데 이런 맥락이었음..)
• 이 때 위 정답 쿼리문과 같이 통합된 테이블에 `order by result1`을 사용하는 경우 다시 알파벳순으로 정렬되기 때문에 orderby절은 빼줘야 한다.