오라클 옆으로 붙이기 - olakeul yeop-eulo but-igi

JOIN과는 다르게 UNION처럼 그냥 데이터를 붙이고 싶을 때가 있다.

UNION은 2개의 테이블이 위아래로 붙는데 좌우로 붙이고 싶으면 어떻게 해야할까?

SELECT한 것을 다시 SELECT함으로써 해결할 수 있다.

SELECT
    (SELECT COUNT(idx)
     FROM MEMBER
     WHERE 1=1
     AND GRADE = 'A') AS VIP
  , (SELECT COUNT(idx)
     FROM MEMBER) AS TOTAL
;

결과 :

1. LISTAGG 함수의 활용

오라클 쿼리를 실행하다보면 같은 조건을 가진 여러 행을 하나의 행으로 합치고 싶은 경우가 발생한다.

예를 들어 그림과 같은 테이블 데이터가 있다고 했을 때, 

하나의 COUNTRY_ID (국가)에 있는 도시 종류를 한 눈에 보고싶다고 가정하자.

같은 COUNTRY_ID를 GROUP BY 처리하고,

그 GROUP에 해당하는 모든 행(ROW)을 한 행(ROW)으로 합쳐야한다.

이 때, 유용하게 쓸 수 있는 함수가 LISTAGG이다.

SELECT COUNTRY_ID, LISTAGG(CITY,',') WITHIN GROUP (ORDER BY CITY) AS 도시목록
FROM HR.LOCATIONS
GROUP BY COUNTRY_ID
ORDER BY COUNTRY_ID;

이 쿼리를 실행하면 아래와 같은 결과가 나온다.

COUNTRY_ID로 GROUP BY를 하여,

여러 행이던 CITY 컬럼의 데이터를 한 ROW로 LISTAGG(LIST AGGREGATION) 해주는 것이다.

LISTAGG의 두번째 PARAMETER는 구분자로 위 쿼리에서는 ,을 사용했는데 다른 구분자로 활용 가능하다.

2. WM_CONCAT과의 차이점

11g 이전 버전에서는 WM_CONCAT이라는 함수를 사용했다.

SELECT COUNTRY_ID, WM_CONCAT(CITY) AS 도시목록
FROM HR.LOCATIONS
GROUP BY COUNTRY_ID
ORDER BY COUNTRY_ID;

10g 버전의 오라클이라면 위 쿼리도 앞선 LISTAGG와 유사하게 사용 가능하다.

다만, WM_CONCAT은 합친 ROW의 정렬이 안 된다는 단점이 있다.

LISTAGG는 사용시 WITHIN GROUP (ORDER BY {컬럼명}) 을 통해 정렬이 가능하지만,

WM_CONCAT은 정렬이 불가능하다.

11g 이후에는 공식적으로 지원하지 않으니 LISTAGG의 활용을 추천.

참고자료 : https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions089.htm#SQLRF30030

LISTAGG

LISTAGG Syntax Description of the illustration ''listagg.gif'' See Also: "Analytic Functions" for information on syntax, semantics, and restrictions of the ORDER BY clause and OVER clause Purpose For a specified measure, LISTAGG orders data within each gro

docs.oracle.com

SQL

[SQL] 테이블 합치기 (JOIN / UNION)

'칼퇴족 김대리는 알고 나만 모르는 SQL, 10장'을 인용하였다 

내부 조인

1. FROM / WHERE 를 사용

: 내부 조인은 두 테이블의 키값이 매칭될 경우 선택된 모든 열을 나타낸다

SELECT 테이블명1.열이름1, 테이블명2.열이름2 

FROM 테이블명1, 테이블명2

WHERE  테이블명1.KEY = 테이블명2.KEY;

2. INNER JOIN 을 사용

: INNER JOIN을 사용하면 두 테이블을 합칠 수 있다. ON 뒤에 두 테이블의 연결고리가 되는 키 값을 적어야 한다

SELECT 테이블명1.열이름1, 테이블명2.열이름2 

FROM 테이블명1 INNER JOIN 테이블명2

ON  테이블명1.KEY = 테이블명2.KEY;

Ex)

고객주소 테이블: ADDR

고객휴대폰 테이블: MOBILE

SELECT ADDR.*, MOBILE.MOBILE_NO 

FROM ADDR INNER JOIN MOBILE

ON  ADDR.CUST_ID = MOBILE.CUST_ID;

결과

외부 조인

1. LEFT OUTER JOIN

: 왼쪽 테이블을 기준으로 조인하는 방법으로, OUTER를 생략해도 무방하다

SELECT 별칭.1열이름1, 별칭2.열이름2 

FROM 테이블명1 (AS) 별칭1 LEFT (OUTER) JOIN 테이블명2 (AS) 별칭2

ON  별칭1.KEY = 별칭2.KEY;

2. RIGHT OUTER JOIN

: 오른쪽 테이블을 기준으로 조인하는 방법으로, OUTER를 생략해도 무방하다

SELECT 별칭.1열이름1, 별칭2.열이름2 

FROM 테이블명1 (AS) 별칭1 RIGHT (OUTER) JOIN 테이블명2 (AS) 별칭2

ON  별칭1.KEY = 별칭2.KEY;

3. FULL OUTER JOIN

: 왼쪽과 오른쪽 테이블의 모든 행이 반환된다  

SELECT 별칭.1열이름1, 별칭2.열이름2 

FROM 테이블명1 (AS) 별칭1 FULL (OUTER) JOIN 테이블명2 (AS) 별칭2

ON  별칭1.KEY = 별칭2.KEY;

Ex)

고객주소 테이블1: ADDR1

고객휴대폰 테이블1: MOBILE1

(LEFT OUTER JOIN)

SELECT TMP1.CUST_ID AS CUST_ID1, TMP1.HOME_ADDR1, TMP1.HOME_ADDR2,

               TMP2.CUST_ID AS CUST_ID2, TMP2.MOBILE_NO 

FROM ADDR1 AS TMP1

LEFT OUTER JOIN MOBILE1 AS TMP2 ON TMP1.CUST_ID = TMP2.CUST_ID;

결과 (CUST_ID가 6511인 고객의 MOBILE_NO는 NULL값으로 처리)

(RIGHT OUTER JOIN)

SELECT TMP1.CUST_ID AS CUST_ID1, TMP1.HOME_ADDR1, TMP1.HOME_ADDR2,

               TMP2.CUST_ID AS CUST_ID2, TMP2.MOBILE_NO 

FROM ADDR1 AS TMP1

RIGHT OUTER JOIN MOBILE1 AS TMP2 ON TMP1.CUST_ID = TMP2.CUST_ID;

결과 

(FULL OUTER JOIN)

SELECT TMP1.CUST_ID AS CUST_ID1, TMP1.HOME_ADDR1, TMP1.HOME_ADDR2,

               TMP2.CUST_ID AS CUST_ID2, TMP2.MOBILE_NO 

FROM ADDR1 AS TMP1

FULL OUTER JOIN MOBILE1 AS TMP2 ON TMP1.CUST_ID = TMP2.CUST_ID;

결과

UNION 연산자

1. UNION 연산자 사용

: UNION은 두 개 이상의 SELECT문의 행을 합치는 역할을 한다

SELECT 열이름1, 열이름2 FROM 테이블명1 WHERE 조건절

UNION

SELECT 열이름1, 열이름2 FROM 테이블명2 WHERE 조건절

ORDER BY 1;

2. UNION ALL 연산자 사용

: UNION ALL 연산자는 두 개 이상의 SELECT문의 행을 합치는 역할을 한다. UNION 연산자와의 차이는 중복을 포함한 모든 값을 나타낸다는 것이다

SELECT 열이름1, 열이름2 FROM 테이블명1 WHERE 조건절

UNION ALL

SELECT 열이름1, 열이름2 FROM 테이블명2 WHERE 조건절

ORDER BY 1;