꿈꾸는 개발자, DBA 커뮤니티 구루비

문제

다음과 같이 테이블이 3개가 있습니다.

1. 스터디 학생 테이블은 s_id가 PK입니다.
2. 스터디 과목 테이블은 c_id가 PK입니다.
3. 스터디마스터테이블은 학생과 과목 테이블을 각각 참조하며 PK는 s_id, c_id, chasu 3개 항목의 조합입니다.

스터디학생(student)

S_ID	S_NM
001	기민용
002	이현석
003	김정식
004	강정식

스터디과목(course)

C_ID	C_NM
001	Database
002	Java

스터디마스터(study)

S_ID	C_ID	CHASU
001	001	1
001	001	3
001	002	2
002	001	1
002	001	2
002	001	3
003	002	1
003	002	2
004	001	1

위 3개 테이블을 이용하여 다음과 같은 결과를 도출하는 쿼리를 작성해 보세요.

ID	성명	스터디	1차	2차	3차	참여횟수
001	기민용	Database	○		○	2
		Java		○		1
002	이현석	Database	○	○	○	3
		Java				0
003	김정식	Database				0
		Java	○	○		2
004	강정식	Database	○			1
		Java				0

쿼리 작성 조건입니다.

- 학생별로 2개행씩(스터티과목수만큼) 조회되어야 합니다.
- 스터디 차수별로 가입여부에 '○'표로 표시합니다. 3차까지 표시.
- 과목별 참여건수를 표시합니다.
- 서브쿼리 없이 작성하세요.

다음 with절을 이용하여 작성해 보세요.

WITH student AS
(
	SELECT '001' s_id, '기민용' s_nm FROM dual
	UNION ALL SELECT '002', '이현석' FROM dual
	UNION ALL SELECT '003', '김정식' FROM dual
	UNION ALL SELECT '004', '강정식' FROM dual
)
, course AS
(
	SELECT '001' c_id, 'Database' c_nm FROM dual
	UNION ALL SELECT '002', 'Java' FROM dual
)
, study AS
(
	SELECT '001' s_id, '001' c_id, 1 chasu FROM dual
	UNION ALL SELECT '001', '001', 3 FROM dual
	UNION ALL SELECT '001', '002', 2 FROM dual
	UNION ALL SELECT '002', '001', 1 FROM dual
	UNION ALL SELECT '002', '001', 2 FROM dual
	UNION ALL SELECT '002', '001', 3 FROM dual
	UNION ALL SELECT '003', '002', 1 FROM dual
	UNION ALL SELECT '003', '002', 2 FROM dual
	UNION ALL SELECT '004', '001', 1 FROM dual
)
-- 이곳에 들어갈 쿼리를 작성해 주세요.

정답

SELECT CASE WHEN b.c_id = '001' THEN a.s_id END AS "ID"
     , CASE WHEN b.c_id = '001' THEN a.s_nm END AS "성명"
     , b.c_nm AS "스터디"
     , MIN(CASE WHEN c.chasu = 1 THEN '○' END) AS "1차"
     , MIN(CASE WHEN c.chasu = 2 THEN '○' END) AS "2차"
     , MIN(CASE WHEN c.chasu = 3 THEN '○' END) AS "3차"
     , COUNT(c.s_id) AS "참여횟수"
 FROM student a
CROSS JOIN course b
 LEFT OUTER JOIN study c
   ON a.s_id = c.s_id
  AND b.c_id = c.c_id
GROUP BY a.s_id, a.s_nm, b.c_id, b.c_nm
ORDER BY a.s_id, b.c_id;

해설

이 문제는 3개의 테이블을 조인하여 결과를 도출하는 문제입니다.

위의 문제에 제시된 여러 단서 조항들을 한꺼번에 모두 적용하지 않고 한단계, 한단계 차근차근 접근하면서 문제를 해결해 나가는 방법으로 설명하겠습니다.

1단계. 조인

SELECT a.s_id, a.s_nm, b.c_id, b.c_nm, c.chasu
  FROM student a, course b, study c
 WHERE a.s_id = c.s_id
   AND b.c_id = c.c_id
ORDER BY a.s_id, b.c_id, c.chasu;

S_ID	S_NM	C_ID	C_NM	CHASU
001	기민용	001	Database	1
001	기민용	001	Database	3
001	기민용	002	Java	2
002	이현석	001	Database	1
002	이현석	001	Database	2
002	이현석	001	Database	3
003	김정식	002	Java	1
003	김정식	002	Java	2
004	강정식	001	Database	1

2단계. 행을 열로

조인을 한 후 s_id, c_id 로 그룹핑하여 차수를 옆으로 나열하는 단계입니다.

SELECT a.s_id, a.s_nm, b.c_id, b.c_nm
     , MIN(CASE WHEN c.chasu = 1 THEN '○' END) AS "1차"
     , MIN(CASE WHEN c.chasu = 2 THEN '○' END) AS "2차"
     , MIN(CASE WHEN c.chasu = 3 THEN '○' END) AS "3차"
     , COUNT(c.s_id) AS "참여횟수"
FROM student a
   , course b
   , study c
WHERE a.s_id = c.s_id
  AND b.c_id = c.c_id
GROUP BY a.s_id, a.s_nm, b.c_id, b.c_nm
ORDER BY a.s_id, b.c_id;

원하던 결과와 매우 유사한 형태의 결과가 나왔습니다.

S_ID	S_NM	C_ID	C_NM	1차	2차	3차	참여횟수
001	기민용	001	Database	○		○	2
001	기민용	002	Java		○		1
002	이현석	001	Database	○	○	○	3
003	김정식	002	Java	○	○		2
004	강정식	001	Database	○			1

3단계. 아우터 조인 시도

자, 이제 없는 스터디 과목도 나오도록 해야겠습니다. 아우터 조인을 이용하면 되겠죠. 다음과 같이 study 테이블에 아우터조인을 걸어 쿼리를 실행해 보겠습니다.

SELECT a.s_id, a.s_nm, b.c_id, b.c_nm
     , MIN(CASE WHEN c.chasu = 1 THEN '○' END) AS "1차"
     , MIN(CASE WHEN c.chasu = 2 THEN '○' END) AS "2차"
     , MIN(CASE WHEN c.chasu = 3 THEN '○' END) AS "3차"
     , COUNT(c.s_id) AS "참여횟수"
FROM student a
   , course b
   , study c
WHERE a.s_id = c.s_id(+)
  AND b.c_id = c.c_id(+)
GROUP BY a.s_id, a.s_nm, b.c_id, b.c_nm
ORDER BY a.s_id, b.c_id;

ORA-01417 : 하나의 테이블은 하나의 다른 테이블과 포괄 조인할 수 있습니다

예기치 못한 에러가 발생했습니다. c를 a, b 두개 집합과 아우터조인을 하려고 해서 발생한 에러입니다. 이를 해결하기 위해서는 a, b를 하나의 집합으로 만들어야 합니다.

4단계. a, b를 하나의 집합으로 만들기(인라인뷰)

SELECT a.s_id, a.s_nm, a.c_id, a.c_nm
     , MIN(CASE WHEN c.chasu = 1 THEN '○' END) AS "1차"
     , MIN(CASE WHEN c.chasu = 2 THEN '○' END) AS "2차"
     , MIN(CASE WHEN c.chasu = 3 THEN '○' END) AS "3차"
     , COUNT(c.s_id) AS "참여횟수"
FROM (SELECT a.s_id, a.s_nm, b.c_id, b.c_nm
        FROM student a, course b
     ) a
   , study c
WHERE a.s_id = c.s_id(+)
  AND a.c_id = c.c_id(+)
GROUP BY a.s_id, a.s_nm, a.c_id, a.c_nm
ORDER BY a.s_id, a.c_id

S_ID	S_NM	C_ID	C_NM	1차	2차	3차	참여횟수
001	기민용	001	Database	○		○	2
001	기민용	002	Java		○		1
002	이현석	001	Database	○	○	○	3
002	이현석	002	Java				0
003	김정식	001	Database				0
003	김정식	002	Java	○	○		2
004	강정식	001	Database	○			1
004	강정식	002	Java				0

a 와 b를 조인하려고 했는데 원래부터 둘 사이엔 아무런 관계가 없었네요. 조인조건을 줄 수가 없습니다. 조건 없이 조인을 하게 되는 것입니다.

위 결과 중 노란색으로 표시한 부분이 바로 인라인뷰의 결과입니다. 우리가 원하던 바로 그 결과가 나왔습니다.

5단계. 두번 중복되어 나오는 s_id 와 s_nm 항목은 한번만 나타내기

이는 c_id 값 두개중 한개만 선택적으로 출력되도록 Case문을 사용하면 됩니다.

SELECT CASE WHEN a.c_id = '001' THEN a.s_id END s_id
     , CASE WHEN a.c_id = '001' THEN a.s_nm END s_nm

이제 거의 모든 조건을 만족하게 되었습니다. 단, 한가지 조건을 만족하지 못하네요. 바로, 서브 쿼리 사용 안하기 조건입니다.

아우터조인 시 에러를 해결하기 위해 부득이하게 서브쿼리를 사용했습니다. 만약, 서브쿼리를 사용 안한다면 아우터조인 에러를 해결할 수 없습니다.

오라클에서 사용하는 를 이용한 (+) 아우터조인에는 몇가지 제약사항이 있습니다.

- ORA-01417 : 하나의 테이블은 하나의 다른 테이블과 포괄 조인할 수 있습니다
- ORA-01719 : 포괄 조인 운영 (+)는 OR 또는 IN의 연산수를 허용하지 않습니다

위 제약사항은 ANSI SQL 의 Outer Join을 이용하면 해결이 가능합니다.

6단계. ANSI SQL 의 Outer Join 사용하기

SELECT CASE WHEN b.c_id = '001' THEN a.s_id END AS "ID"
     , CASE WHEN b.c_id = '001' THEN a.s_nm END AS "성명"
     , b.c_nm AS "스터디"
     , MIN(CASE WHEN c.chasu = 1 THEN '○' END) AS "1차"
     , MIN(CASE WHEN c.chasu = 2 THEN '○' END) AS "2차"
     , MIN(CASE WHEN c.chasu = 3 THEN '○' END) AS "3차"
     , COUNT(c.s_id) AS "참여횟수"
 FROM student a
CROSS JOIN course b
 LEFT OUTER JOIN study c
   ON a.s_id = c.s_id
  AND b.c_id = c.c_id
GROUP BY a.s_id, a.s_nm, b.c_id, b.c_nm
ORDER BY a.s_id, b.c_id;

드디어 정답을 완성했습니다.

오라클에서 사용하던 Sql문법과는 많이 생소한 구문입니다.

- CROSS JOIN 은 a 와 b를 조건 없이 조인할 때 사용이 되는 구문입니다.
- LEFT OUTER JOIN 은 왼쪽 집합을 기준으로 오른쪽을 포괄조인하는 구문입니다.
- a, b 두개 집합과 c를 아우터조인하고 있지만 에러는 발생하지 않습니다.

이번 퀴즈의 정답은 매우 간단하지만 결과 도출과정은 그리 만만하지 않습니다.

기본적으로 테이블간의 관계를 알고 조인에 대한 기본개념을 알고 있어야 하며 그룹바이를 이용하여 행을 열로 바꾸어 표시하는 방법 아우터 조인의 개념과 제약사항 ANSI SQL의 사용법까지 알아야 합니다.

이번 퀴즈를 통해 이 모든 것들을 자기 것으로 만들 수 있는 계기가 되었으면 합니다.

S_ID	C_ID	CHASU
001	001	1
001	001	3
001	002	2
002	001	1
002	001	2
002	001	3
003	002	1
003	002	2
004	001	1

S_ID	C_ID	CHASU
001	001	1
001	001	3
001	002	2
002	001	1
002	001	2
002	001	3
003	002	1
003	002	2
004	001	1

S_ID	C_ID	CHASU
001	001	1
001	001	3
001	002	2
002	001	1
002	001	2
002	001	3
003	002	1
003	002	2
004	001	1