[Oracle] UNION (ALL)에서 정확하게 ORER BY 하는 방법

SQL/Oracle

[Oracle] UNION (ALL)에서 정확하게 ORER BY 하는 방법

Unlimited Jun 2023. 11. 5. 18:20

작성일자 : 2023-11-05

Ver 0.1.1

0.Intro

Oracle에서 UNION (ALL)을 사용할 때 ORDER BY 절의 정확한 방법을 숙지하지 않으면 오류가 발생할 수 있다. UNION (ALL) 쿼리문이 모두 끝나고 마지막에 ORDER BY 절을 선언해야 하며, ORDER BY 절에 사용할 수 있는 칼럼은 UNION의 첫 번째 쿼리문(첫번째 ~ N-1 번째)의 별칭 또는 칼럼의 순번이다.

(쿼리문은 Oracle의 LIVESQL을 통해 실행)

SELECT empno
      ,ename
      ,job
  FROM SCOTT.emp
 WHERE job = 'SALESMAN'
 ORDER
    BY emp_job
      ,emp_nm
 UNION
   ALL
SELECT empno
      ,ename 
      ,job   
  FROM SCOTT.emp
 WHERE job = 'ANALYST'
 ORDER
    BY emp_job
      ,emp_nm

[OUTPUT]

ORA-00933: SQL command not properly ended

1. 별칭(컬럼 명)으로 정렬

UNION (ALL) 쿼리는 UNION (ALL) 기준으로 위쪽인 첫 번째 쿼리문의 칼럼명 또는 별칭이 하위 쿼리문(UNION (ALL) 아래)에 동일하게 적용된다.

첫 번째 쿼리문에 별칭을 부여하면 하위 쿼리문도 동일한 별칭이 부여된다고 생각하면 된다.

UNION (ALL) 쿼리문의 마지막에 첫 번째 쿼리문의 칼럼 별칭으로 정렬하면 된다.

SELECT empno AS emp_no
      ,ename AS emp_nm
      ,job   AS emp_job
  FROM SCOTT.emp
 WHERE job = 'ANALYST'
 UNION
   ALL
SELECT empno
      ,ename 
      ,job   
  FROM SCOTT.emp
 WHERE job = 'SALESMAN'
-- 첫번째 쿼리문의 별칭으로 정렬
 ORDER BY emp_job 
         ,emp_nm

그렇다면 3개 이상의의 SELECT문을 UNION (ALL) 하는 경우는 어떠할까?

두번의 UNION 중에서 첫번째 UNION 위에만 별칭을 지정하면 아래와 같은 오류가 발생한다.

SELECT empno AS emp_no
      ,ename AS emp_nm
      ,job   AS emp_job
  FROM SCOTT.emp
 WHERE job = 'ANALYST'
 UNION
   ALL
SELECT empno 
      ,ename
      ,job
  FROM SCOTT.emp
 WHERE job = 'SALESMAN'
 UNION
   ALL
SELECT empno
      ,ename
      ,job
  FROM SCOTT.emp
 WHERE job = 'MANAGER'
-- 첫번째 쿼리문의 별칭으로 정렬
 ORDER BY emp_job 
         ,emp_nm

[OUTPUT]

ORA-00904: "EMP_JOB": invalid identifier

이에 두번째(마지막) UNION (ALL) 위까지 동일한 별칭을 지정해주면 ORDER BY가 작동된다

SELECT empno AS emp_no
      ,ename AS emp_nm
      ,job   AS emp_job
  FROM SCOTT.emp
 WHERE job = 'ANALYST'
 UNION
   ALL
SELECT empno AS emp_no
      ,ename AS emp_nm
      ,job   AS emp_job
  FROM SCOTT.emp
 WHERE job = 'SALESMAN'
 UNION
   ALL
SELECT empno
      ,ename
      ,job
  FROM SCOTT.emp
 WHERE job = 'MANAGER'
-- 첫번째, ... , (n-1)번째 쿼리문의 별칭으로 정렬
 ORDER BY emp_job 
         ,emp_nm

2. 컬럼 순번으로 정렬

컬럼 명 또는 별칭 대신에 컬럼의 순번으로도 정렬이 가능하다.

SELECT empno AS emp_no -- 1
      ,ename AS emp_nm -- 2
      ,job   AS emp_job -- 3
  FROM SCOTT.emp
 WHERE job = 'SALESMAN'
 UNION
   ALL
SELECT empno
      ,ename 
      ,job   
  FROM SCOTT.emp
 WHERE job = 'ANALYST'
-- 첫번째 쿼리문의 순번으로 정렬
 ORDER BY 3 DESC
         ,2

3. 서브쿼리를 사용한 정렬

컬럼명이나 컬럼순번으로 UNION (ALL) 쿼리문의 정렬을 할 수 있지만, 위 방법들을 정확히 숙지하지 못해서 서브쿼리를 사용하여 정렬을 하는 경우도 종종 있다. 간단한 정렬은 서브쿼리를 사용하지 않아도 위의 방법을 사용하면 된다.

UNION (ALL) 쿼리를 정렬할 때 서브쿼리를 사용하는 경우는, ORDER BY 칼럼으로 사용은 하지만 SELECT 결과에 포함하고 싶지 않을 때 서브쿼리를 사용하여 정렬을 하면 유용하게 사용할 수 있다.

SELECT emp_no
      ,emp_nm
  FROM (
           SELECT empno AS emp_no
                 ,ename AS emp_nm
                 ,1     AS emp_grp -- 정렬에 사용되는 컬럼 생성
             FROM SCOTT.emp
            WHERE job = 'ANALYST'
            UNION
              ALL
           SELECT empno
                 ,ename
                 ,2                -- 정렬에 사용되는 컬럼 생성
             FROM SCOTT.emp
            WHERE job = 'SALESMAN'
       )
 ORDER BY emp_grp -- 정렬을 위해 사용했지만, SELECT 문에는 포함되어 있지 않음
         ,emp_nm

참고 사이트 : https://gent.tistory.com/605

저작자표시