7. OUTER 조인과 UNION ALL
7장까지 왔습니다. 벌써.. 36Page 째로군요. ^^* 흐뭇흐뭇..^^
이렇게 많이 써내려 올 줄이야.. 물론, 볼 것 없는 내용들이 많긴 하지만.
오늘도 읽어주시는 분들 덕에… 힘차게 써볼까 합니다.
이번에는 OUTER JOIN과 UNION에 대해 설명을 하겠습니다.
우선 OUTER JOIN은 무엇을 하는지 간단히 설명을 하자면, 조건이 안맞는 것도 결합을 시키는 겁니다.
이렇게 글로만 써서는 표현이 안되는 군요.. OUTER JOIN을 하면 주체가 되는 쪽의 데이터는 무조건 다 나온다는게 원칙입니다.
그리고 UNION은 데이터를 상 하로 결합을 하는 겁니다. 조인을 하게 되면 다른 테이블의 데이터는 옆으로 와서 붙습니다. 하지만 UNION을 하게 되면 다른 테이블의 결과는 밑으로 와서 붙게 됩니다. 물론 밑으로 데이터가 붙기 위한 몇가지 제약이 있습니다.
먼저, OUTER JOIN부터 살펴보도록 하겠습니다.
5장에서 SUBJECT 테이블을 만들었었고, 6장에서 STUDENT_SUBJECT 테이블에 셀프조인의 예를 들기 위해 추가적인 데이터를 삽입했습니다. 그래서, 9657004라는 학생에게는 SUBJECT에는 존재하지 않는 과목코드들이 들어가 있습니다. 이런 경우. 원칙상 데이터의 무결성이 깨진것으로 심각한 문제가 있는 것입니다. 하지만, 예를 들기 위한 거니까, 이 잘못된 데이터를 가지고 OUTER JOIN을 만들어보도록 하겠습니다.
|
문제7-1
9657004 학생이 수강한 모든 과목의 점수와 해당 과목들의 모든 정보를 보여주세요
쿼리7-1
SELECT *
FROM STUDENT_SUBJECT A, SUBJECT B
WHERE A.SubjectCode *= B.SubjectCode
AND A.SNO = '9657004' |
쿼리를 보시면 A.SNO = ‘9657004’라는 데이터를 걸러내는 검색조건이 있고 조인 조건이 *= 이런 표시로 = 앞에 *가 붙어 있습니다.
유심히 보실 부분은 *가 어느쪽으로 붙느냐입니다. 현재 A쪽 컬럼으로 붙어 있죠. 그러므로 A쪽 테이블이 주체가 되는 겁니다. 이런 경우 = 조건으로 조인이 되지만, 더 우선되는 것이 A쪽 테이블의 내용은 모두 나온다는 겁니다.
데이터를 봐야 이해가 좀더 쉽겠군요
SELECT * FROM STUDENT_SUBJECT WHERE SNO = ‘9657004’를 실행한 데이터
|
SNO |
SubjectCode |
Score |
여기서 색이 들어있는 부분의 데이터가 바로 SUBJECT
테이블에는 존재하지 않는 SubjectCode입니다.
|
|
9657004 |
01 |
95 |
|
9657004 |
02 |
65 |
|
9657004 |
03 |
94 |
|
9657004 |
04 |
63 |
|
9657004 |
05 |
92 |
|
9657004 |
06 |
66 |
|
9657004 |
07 |
97 |
|
9657004 |
08 |
68 |
만약에 *가 붙지 않은 일반 조인으로 A.SubjectCode = B.SubjectCode라는 조건을 준다면 결과에서 색이 있는 두개의 데이터는 제외되어서 데이터가 만들어집니다.
하지만 *가 있게 되면 주체쪽의 데이터는 이유불문!!!! 모두다 나오는겁니다.
결과는 다음과 같죠
|
SNO |
SubjectCode |
Score |
SubjectCode |
SubjectName |
ProfessorName |
|
9657004 |
01 |
95 |
01 |
컴퓨터구조 |
임신일 |
|
9657004 |
02 |
65 |
02 |
C++ |
이양선 |
|
9657004 |
03 |
94 |
03 |
Visual Basic |
유석원 |
|
9657004 |
04 |
63 |
04 |
기계역학 |
한현수 |
|
9657004 |
05 |
92 |
05 |
공학수학 |
유상규 |
|
9657004 |
06 |
66 |
06 |
경영의 이해 |
최철민 |
|
9657004 |
07 |
97 |
NULL |
NULL |
NULL |
|
9657004 |
08 |
68 |
NULL |
NULL |
NULL |
보시면 B쪽 테이블에는 SubjectCode가 07, 08인 것이 없으므로 NULL로 되어서 데이터가 표현이 되었습니다.
여기서 좀더 보기 좋게 하기 위해서는 ISNULL구문을 사용할 수 있습니다. ISNULL구문이란, NULL값인 경우 다른 값으로 대체를 하는 겁니다. NULL이란건 알 수 없는 값이므로 프로그램을 하는데 있어서 주의가 필요한 값입니다. 대체로 아예 발생되지 않는 것이 좋습니다. 꼭 그런건 아니지만요.
그럼 ISNULL을 사용해서 쿼리를 고치면 다음과 같을 수 있습니다.
|
SELECT A.SNO, A.SubjectCode, A.Score,
ISNULL(B.SubjectCode,’’), ISNULL(B.SubjectName,’알수없는과목’),
ISNULL(B.ProfessorName,’’)
FROM STUDENT_SUBJECT A, SUBJECT B
WHERE A.SubjectCode *= B.SubjectCode
AND A.SNO = '9657004' |
예제를 하나 보았는데 이래서는 대체 이걸 어디다 써먹는단 말인가?? 란 생각이 드실겁니다. 하지만 OUTER JOIN을 사용하는 경우가 많이 있습니다. 예를 들어 SUBJECT 테이블은 과목을 관리하는 테이블입니다. 해당 테이블에는 총 6개의 과목이 있습니다. 그리고 각 과목당 몇 명의 학생들이 수강을 하는지를 알고 싶어 합니다. 이럴때, 6개의 과목 모두 학생들이 듣고 있기 때문에 문제가 없습니다. 하지만 어떤 과목에 있어서는 한 학생도 수강하지 않을 경우 0명이 듣는다는 걸 표현해야 하는데 = 조인으로는 표현할 수 없습니다. 이럴 때 OUTER JOIN을 유용하게 써먹을 수 있습니다.
먼저 다음의 내용을 실행시켜서 수강 테이블에서 몇건의 데이터를 지워보도록 하겠습니다.
|
DELETE STUDENT_SUBJECT
WHERE SubjectCode IN ('07','08','02') |
07, 08과목은 원래 SUBJECT테이블에 없는 데이터이므로 지워버린 겁니다. 02과목을 듣는 학생들을 지운건 OUTER JOIN의 예를 보여주기 위해 지운겁니다.
|
문제7-2
각 과목별로 수업을 듣는 학생들의 수를 알려주세요
쿼리7-2
SELECT A.SubjectCode, '교수명' = MAX(A.SubjectName), '수강인원' = COUNT(SNO)
FROM SUBJECT A, STUDENT_SUBJECT B
WHERE A.SubjectCode = B.SubjectCode
GROUP BY A.SubjectCode |
위의 쿼리는 = 조인으로 표현을 했습니다. SUBJECT 테이블과 STUDENT_SUBJECT 테이블을 보면 다음과 같습니다.
<SUBJECT 테이블>(과목) <STUDENT_SUBJECT 테이블>(수강)
|
SubjectCode |
SubjectName |
Profes.Name |
|
SNO |
SubjectCode |
Score |
|
01 |
컴퓨터구조 |
임신일 |
|
9627001 |
01 |
90 |
|
02 |
C++ |
이양선 |
|
9627002 |
03 |
80 |
|
03 |
Visual Basic |
유석원 |
|
9657003 |
04 |
75 |
|
04 |
기계역학 |
한현수 |
|
9657004 |
01 |
95 |
|
05 |
공학수학 |
유상규 |
|
9657004 |
03 |
94 |
|
06 |
경영의 이해 |
최철민 |
|
9657004 |
04 |
63 |
|
|
|
|
|
9657004 |
05 |
92 |
|
|
|
|
|
9657004 |
06 |
66 |
|
|
|
|
|
9667005 |
06 |
100 |
같은 SubjectCode끼리 조인이 일어나니까, SUBJECT테이블쪽의 01코드에 수강에서 두 건이, 03코드에 수강에서 두 건이, 04코드에는 수강에서 두 건이 이런식으로 조인이 됩니다. 하지만 여기서 SUBJECT테이블쪽에 02코드의 데이터와 결합할 데이터는 수강테이블에 존재하지 않습니다. 그러므로 조인시에 02번 데이터는 빠지게 됩니다. 그렇게 조인이 된후 A.SubjectCode로 GROUP BY를 했습니다.
언제나 말하듯이, GROUP BY에 열거된 컬럼 이외에는 집계함수를 써야만 표현이 가능하기 때문에 교수이름을 표현하기 위해 MAX를 사용했습니다. 물론 해당 Subject Code에는 교수가 한명이므로 MAX를 안 써도 되지 않나 생각하시는 분들이 있겠지만 조인이 발생되면 같은 교수이름으로 여러건이 있는 것이므로 MAX나 MIN을 써야만 합니다. 그리고, COUNT(SNO)를 수행해서 해당 과목 코드에 몇 명의 학생이 있는지 건수를 세는 겁니다. 그럼 해당 과목의 수강인원을 볼 수가 있지요. 여기서 COUNT(*)를 써도 무관합니다.
하지만, 02과목코드의 데이터는 어디론가 사라지고 없군요.. 물론 아무도 안 들으니까 그렇긴 않지만, 실무에서도 해당 코드에 관련되는 금액이나 인원 같은 것이 없어도 0으로 표현해야 할 경우가 많이 있습니다.
예를 들어 병원에 내과, 외과, 산부인과가 있는데 어떤 특정일에 산부인과에는 환자가 아무도 없었습니다. 그런데 특정일을 내원환자수를 뽑는데, 산부인과가 0명이라고 화면에 안 보여준다면 그게 용납이 될까요?
이런경우도 산부인과 0명으로 처리를 해서 데이터를 보여주어야 실무에서도 무난히 넘어갈 수 있을겁니다.
(개인적으로 병원쪽에서 개발을 하고 있는데, 병원이 싫습니다… 프로그래밍도 어렵고, 업무도 복잡하고, 곧… 병원계를 떠날까 합니당..ㅋㅋ)
다음과 같이 문제를 냈다고 하죠
|
문제7-3
각 과목별로 수업을 듣는 학생들의 수를 알려주세요, 수강인원이 없는 경우는 0명으로 보여주세요^^
쿼리7-3
SELECT A.SubjectCode, '교수명' = MAX(A.SubjectName), '수강인원' = COUNT(SNO)
FROM SUBJECT A, STUDENT_SUBJECT B
WHERE A.SubjectCode *= B.SubjectCode
GROUP BY A.SubjectCode |
기존과 틀린 것은 조인조건에 과목쪽으로 *가 붙었다는 것밖에 없습니다.
여기서 SELECT * FROM SUBJECT A, STUDENT_SUBJECT B WHERE A.SubjectCode *= B.SubjectCode만 실행한 결과를 보도록 하죠.(GROUP BY가 수행되기 전의 결과입니다. GROUP BY가 들어가 있는 쿼리를 풀 때 GROUP BY하기 전의 결과를 보고 관찰하는 것이 큰 도움이 됩니다. 그 데이터를 보고 GROUP BY를 하면 어떻게 어떻게 되겠구나란 생각을 하는거죠.)
|
SubjectCode |
SubjectName |
ProfessorName |
SNO |
SubjectCode |
Score |
|
01 |
컴퓨터구조 |
임신일 |
9627001 |
01 |
90 |
|
01 |
컴퓨터구조 |
임신일 |
9657004 |
01 |
95 |
|
02 |
C++ |
이양선 |
NULL |
NULL |
NULL |
|
03 |
Visual Basic |
유석원 |
9627002 |
03 |
80 |
|
03 |
Visual Basic |
유석원 |
9657004 |
03 |
94 |
|
04 |
기계역학 |
한현수 |
9657003 |
04 |
75 |
|
04 |
기계역학 |
한현수 |
9657004 |
04 |
63 |
|
05 |
공학수학 |
유상규 |
9657004 |
05 |
92 |
|
06 |
경영의 이해 |
최철민 |
9657004 |
06 |
66 |
|
06 |
경영의 이해 |
최철민 |
9667005 |
06 |
100 |
보시면 02번 데이터에 SNO, SubjectCode, Score 부분이 NULL로 되어 있습니다. 수강하는 인원이 하나도 없기 때문이죠.
그럼, 여기서 A.SubjectCode로 GROUP BY를 수행합니다. 그리고 COUNT(SNO)를 하게 되면 01코드는 두건이니까 2명이 수강한다는 얘기가 되죠. 하나 02코드에서 COUNT(SNO)를 하게 되면 0이 됩니다. NULL은 숫자도 아니고 문자도 아니고 알 수 없는 값입니다. 그러므로 셀 수가 없습니다. 그래서 COUNT를 하게 되면 0건이 됩니다. 여기서 한가지 주의할 건 COUNT(*)를 하게 되면 1건이 된다는 겁니다. 왜냐면 다른 데이터들이 존재하고 있으므로(과목명, 교수명은 존재함) 1건이 됩니다.
NULL이란게 나왔으니까 한가지를 적고 가자면 NULL을 집계함수로 처리했을 때 COUNT를 사용한 경우는 0으로 카운트를 합니다. 하지만, SUM, MAX, MIN을 할때는 NULL을 처리하지 못합니다. 그래서 NULL만 있는 경우 SUM, MAX, MIN을 한 경우는 NULL 값이 나올 수 있습니다. 데이터가 전혀 없는 경우에도 SUM, MAX, MIN을 하게 되면 NULL이 나오게 되고요, NULL과 다른 값들이 섞여 있을때는 NULL값은 무시한채 결과가 진행됩니다. COUNT만이 NULL을 0으로 센다고 생각하시면 됩니다.
이번에는 UNION을 살펴보도록 하겠습니다.
UNION은 데이터를 상하로 결합시킵니다. UNION과 같은 표현으로 사용되는 것이 UNION ALL이 있습니다.
UNION ALL은 중복된 데이터도 무조건 출력시켜주고, UNION은 중복되는 데이터는 제외를 하고 출력을 해주는 방법입니다.
우선 적절한 예제를 만들기 위해 다음의 내용을 실행시킵니다.
|
CREATE TABLE PROFESSOR
( PNO char(7) NOT NULL PRIMARY KEY,
Name varchar(10) NOT NULL DEFAULT '',
MajorCode char(2) NOT NULL DEFAULT '',
Tel varchar(15) NOT NULL DEFAULT ''
)
go
INSERT PROFESSOR VALUES('0000001','임신일','01','101')
INSERT PROFESSOR VALUES('0000002','이양선','01','101')
INSERT PROFESSOR VALUES('0000003','유석원','01','101')
INSERT PROFESSOR VALUES('0000004','한현수','02','101')
INSERT PROFESSOR VALUES('0000005','유상규','02','101')
INSERT PROFESSOR VALUES('0000006','최철민','03','101')
go
SELECT A.SubjectCode, A.SubjectName, B.PNO
INTO new_SUBJECT
FROM SUBJECT A, PROFESSOR B
WHERE A.ProfessorName = B.Name |
첫 번째 문장인 CREATE문은 새로운 교수 테이블을 만드는 거구요, INSERT는 데이터를 입력한거구요, 세 번째 문장인 SELECT를 보시면 지금까지 봤던 것과는 다르게 INTO new_SUBJECT란 것이 있습니다. INTO는 어디로 데이터를 집어넣는다는 얘기입니다. INTO new_SUBJECT했으니까, SELECT 된 내용을 new_SUBJECT로 집어넣는다는 겁니다. 그러면 자동으로 new_SUBJECT란 테이블까지 만들어지게 되죠
SELECT * FROM new_SUBJECT하시면 다음과 같은 내용이 나오게 됩니다.
|
SubjectCode |
SubjectName |
PNO |
|
01 |
컴퓨터구조 |
0000001 |
|
02 |
C++ |
0000002 |
|
03 |
Visual Basic |
0000003 |
|
04 |
기계역학 |
0000004 |
|
05 |
공학수학 |
0000005 |
|
06 |
경영의 이해 |
0000006 |
보시면 기존의 SUBJECT테이블의 교수명이 PNO란 교수번호로 대체되었습니다. 실무에서도 이렇게 번호로 연결시키는게 맞습니다. 이름이란건 얼마든지 중복이 되기 때문이죠.
New_SUBJECT 테이블을 지금 사용할건 아니구요, UNION 설명을 하기 위해서 STUDENT 테이블과 PROFESSOR 테이블을 사용하도록 하겠습니다.
|
문제7-4
현 학교 내에 교수 및 학생 모든 사람의 목록을 보여주세요
쿼리7-4
SELECT Name
FROM STUDENT
UNION ALL
SELECT Name
FROM PROFESSOR |
간단하죠? 두 개의 문장에 UNION ALL을 사이에 넣어서 입력만 하면 됩니다.
UNION ALL의 제약 사항은 위쪽에 열거된 데이터형과 아래쪽에 열거된 데이터형이 동일해야 합니다. 다를 경우는 위쪽에 데이터 형을 따라가게 됩니다. 만약에 위쪽의 데이터형이 숫자이고 매치되는 아래쪽에 데이터형이 문자라면 이건 에러가 나겠죠. 문자를 숫자로 바꿀 수는 없으니까요.
또한 데이터 결과의 컬럼명칭은 위쪽 쿼리의 컬럼명칭을 따라가게 됩니다.
UNION ALL을 사용시 주의점은 위쪽 아래쪽 쿼리의 SELECT에 열거되는 컬럼들이 동일한 순서여야 한다는 겁니다. 같은 이름을 서로 찾는게 아니라 같은 순서에 있는 것 끼리 합쳐지는 것이기 때문에 순서가 동일해야 합니다.
UNION ALL을 사용해서 좀 더 보기 좋게 결과를 만들도록 해보겠습니다.
|
SELECT '구분' = '학생', Name, '학과명' = MajorName
FROM STUDENT A, MAJOR B
WHERE B.MajorCode = A.MajorCode
UNION ALL
SELECT '구분' = '교수', Name, '학과명' = MajorName
FROM PROFESSOR A, MAJOR B
WHERE B.MajorCode = A.MajorCode |
조인을 한 후 UNION ALL을 이용해 결합을 했죠. 그리고, ‘구분’이라는 가상 컬럼을 각 쿼리마다 만들었죠.
그래서 학생인지 교수인지 구분을 지어서 데이터를 표현을 한거죠. 만약에 직원이라는 테이블이 하나 더 있다면 다음과 같이도 구성이 가능하죠
|
SELECT '구분' = '학생', Name, '학과명' = MajorName
FROM STUDENT A, MAJOR B
WHERE B.MajorCode = A.MajorCode
UNION ALL
SELECT '구분' = '교수', Name, '학과명' = MajorName
FROM PROFESSOR A, MAJOR B
WHERE B.MajorCode = A.MajorCode
UNION ALL
SELECT '구분' = '직원', Name, '학과명' = ‘’
FROM PERSON |
제목은 UNION인데 왜 자꾸 UNION ALL을 쓰느냐고 생각하시는 분들이 있을지 모르겠네요. 위에서도 말씀드렸듯이 UNION은 같은건 제외하고 결합하는 것이고, UNION ALL은 같은것도 중복해서 결과를 보여주는 것입니다. 그러므로 UNION ALL은 같은 데이터가 있는지 검색하는 과정이 없으므로 UNION 보다 성능면에서 유리하죠. 만약에 UNION해서 데이터가 중복될 것이 없는게 확실하거나 중복되어도 상관없다면 당연히 UNION ALL을 써야 겠죠.
이번에는 UNION ALL으로 OR 연산을 대체하는 것을 보여드리죠
만약에 학과가 01이나 03인 교수를 보고자 할 때 다음의 IN이나 OR 를 사용한 쿼리를 생각할 수 있습니다.
|
SELECT *
FROM PROFESSOR
WHERE MajorCode IN ('01','03') |
위와 같은 문장의 결과와 다음의 UNION ALL결과는 동일합니다.
|
SELECT *
FROM PROFESSOR
WHERE MajorCode = '01'
UNION ALL
SELECT *
FROM PROFESSOR
WHERE MajorCode = '03' |
어… 갸우뚱 하시는 분이 계실겁니다. 저도, 어느 잡지에선가 처음 이 내용을 보고.. 얼~~~ 하면서 감탄했으니까요. 이렇게 사용하는 경우 해당 테이블의 인덱스가 어떻게 구성되어 있느냐에 따라 OR연산자보다 좋은 성능을 발휘할 수 있습니다.
출처 : 데브피아
