9. 서브쿼리

9. 서브쿼리

막강한 쿼리를 사용하기 위해 가장 많이 사용될 수 있는 부분이 바로 서브 쿼리입니다.

서브쿼리란 하나의 쿼리 문장안에 또 다른 쿼리문이 들어가 있는 겁니다. 이 서브 쿼리는 SELECT절, FROM절, WHERE절에 올 수 있습니다.

각각 쓰이는 장소에 따라 약간의 제약사항이 있습니다.

 

말로만 들으면 고개만 갸우뚱 하게 될 겁니다. 쿼리문안에 또 다른 쿼리문을 쓴다는 것이.

우선 다음과 같은 테이블을 만들도록 하죠

DROP TABLE #PERSON_POINT   CREATE TABLE #PERSON_POINT (       PERSON_ID CHAR(5) NOT NULL PRIMARY KEY,         Score INT NOT NULL DEFAULT 0 ) go INSERT #PERSON_POINT VALUES('00001',45) INSERT #PERSON_POINT VALUES('00002',70) INSERT #PERSON_POINT VALUES('00003',65) INSERT #PERSON_POINT VALUES('00004',90) INSERT #PERSON_POINT VALUES('00005',67) INSERT #PERSON_POINT VALUES('00006',92)

#PERSON_POINT란 임시 테이블에 아이디와 점수를 넣었습니다. 여기서 가장 최고의 점수자와 모든 사람들의 점수를 비교할려고 합니다. 이럴 경우 다음과 같은 쿼리를 생각할 수 있습니다.

쿼리9-1 DECLARE @maxScore INT SELECT @maxScore = MAX(Score) FROM #PERSON_POINT   SELECT *, @maxScore - score FROM   #PERSON_POINT

3개의 문장으로 이루어지긴 했지만 보기 쉽고, 알기 쉽습니다.

하지만, 한 개의 문장으로 처리를 하고자 한다면 다음과 같은 서브쿼리를 사용 할 수 있습니다.

정확히 파생된 테이블이라고 되어 있군요. BOL의 FROM절 사용을 보시면 알 수 있습니다.

* 여기서 잠깐!! BOL이란? Books online으로서 도움말이라 할 수 있다. 쿼리창이나 EM에서 F1을 누르면 BOL이 나오고 찾고 싶은 항목을 찾아서 볼 수 있다. BOL이야 말로 SQL을 공부하시려는 분들에게 필수라 하겠다.

쿼리9-2 SELECT  A.*, B.Score - A.Score FROM    #PERSON_POINT A,         (SELECT TOP 1 Score FROM #PERSON_POINT B ORDER By Score DESC) B

보시면 FROM절에 #PERSON_POINT 뒤에 괄호를 열고 또 다른 쿼리를 사용했습니다. 여기서 관건은 TOP 1으로 한줄만 리턴했다는 겁니다. 그러므로 조인 조건도 없이 그냥 CROSS JOIN을 해버린거구요.

TOP 1 Score를 했는데, 이것은 ORDER BY Score DESC를 한 것의 TOP 1입니다. 그러므로 Score의 내림차순으로 정렬을 해서 가장 위에 데이터 한 건을 가져오는 겁니다. 물론 가장 높은 점수가 되겠죠. 이렇게 한 건 가져온게 파생된 테이블로 존재하게 됩니다. 파생된 테이블로 한건을 가져왔는데, 파생된 테이블에서 한건만 가져와야 한다 그런 제약 사항은 없습니다. 이렇게 갖고 오게 되면 데이터가 한 건 들어있는 테이블을 가져왔다고 생각하시면 됩니다. 이렇게 해서 한 문장으로 처리했으니 성능이 훨씬 좋겠군이라고 생각하신다면 그건 착각입니다. 성능에 있어서는 한 문장이다 두 문장이다가 중요하지는 않습니다. 물론 여러 문장보다야 한 문장이 DBMS 콜을 덜 하기 때문에 성능상 이점이 있겠지만, 성능에 있어서 중요한건 이런 것이 아닙니다. 성능에 관한건 나중에 몰아서 말씀 드리도록 하고 일단은 한 문장으로 만든 SQL이 절대로 성능이 좋은 것은 아니란 것을 명심하시기 바랍니다.

많은 예제를 보는게 도움이 될 듯 하군요

우선 다음의 리스트를 실행시켜서 해당 점수당 등급을 알 수 있는 테이블을 만들도록 하겠습니다.

DROP TABLE #CLASS   CREATE  TABLE   #CLASS (       fromScore INT NOT NULL PRIMARY KEY,         toScore INT NOT NULL DEFAULT 1,         ClassName VARCHAR(2) NOT NULL DEFAULT '', ) go INSERT #CLASS VALUES(90,100,'A+') INSERT #CLASS VALUES(80,89,'A') INSERT #CLASS VALUES(70,79,'B+') INSERT #CLASS VALUES(60,69,'B') INSERT #CLASS VALUES(50,59,'C') INSERT #CLASS VALUES(40,49,'D') INSERT #CLASS VALUES(0,39,'F')

위의 테이블은 해당 점수당 이 사람이 무슨 등급인지를 구별해내는 테이블입니다. 어떤 사람이 90에서 100사이 점수면 A+등급 이런식으로 등급이 되는 거죠

문제9-3 A+부터 F학점까지 해당 학점의 인원수를 알려주세요 학점을 가로로 출력해주세요 쿼리9-3 SELECT  'A+' = SUM(CASE WHEN ClassName = 'A+' THEN 1 ELSE 0 END),         'A' = SUM(CASE WHEN ClassName = 'A' THEN 1 ELSE 0 END),         'B+' = SUM(CASE WHEN ClassName = 'B+' THEN 1 ELSE 0 END),         'B' = SUM(CASE WHEN ClassName = 'B' THEN 1 ELSE 0 END),         'C' = SUM(CASE WHEN ClassName = 'C' THEN 1 ELSE 0 END),         'D' = SUM(CASE WHEN ClassName = 'D' THEN 1 ELSE 0 END),         'F' = SUM(CASE WHEN ClassName = 'F' THEN 1 ELSE 0 END) FROM    (SELECT A.PERSON_ID, B.ClassName         FROM    #PERSON_POINT A, #CLASS B         WHERE   A.Score >= B.fromScore AND A.Score <= B.toScore) A

여러가지 방식이 있습니다. SUM을 사용해서 집계 할 수도 있고, COUNT를 사용해서 집계할 수도 있고 서브쿼리에서 GROUP BY를 해서 집계 할 수도 있고, 서브쿼리를 사용하지 않고도 해결 할 수 있을 수 있고요.

일단 위의 쿼리를 실행하면 결과는 다음과 같습니다.

A+	A	B+	B	C	D	F
2	0	1	2	0	1	0

차례대로 설명을 해보도록 하겠습니다.

우선 서브 쿼리가 파생된 테이블로 있을 때는 파생된 테이블을 만드는 서브쿼리를 제일 먼저 보는게 순서입니다. 가장 먼저 실행된다고 볼 수 있으니까요

우선 다음과 같은 서브쿼리의 내용만 실행시키면

SELECT  A.PERSON_ID, B.ClassName FROM    #PERSON_POINT A, #CLASS B WHERE   A.Score >= B.fromScore AND A.Score <= B.toScore

결과는 다음과 같죠

PERSON_ID	ClassName
00001	D
00002	B+
00003	B
00004	A+
00005	B
00006	A+

여기서 한가지 간단히 짚고 넘어가자면 테이블 앞에 #이 붙은건 해당 세션에 대해서 임시 테이블이라는 겁니다. 해당 세션에서만 사용이 되고 해당 세션이 종료되면 사라지는 임시 테이블이죠

다시 본론으로 돌아가서, 위와 같은 결과를 FROM 절에 괄호를 써서 묶은 다음에 별칭으로 A란 별칭을 주었습니다. 가장 중요한건 서브쿼리를 사용해서 파생된 테이블을 만들게 되면 별칭을 꼭 주어야 한 다는 겁니다. 별칭을 줄 때 FROM절에 대해서는 한 가지 제약이 있는데 A = (서브쿼리) 이런식으로 별칭을 앞에 줄 수는 없습니다. (서브쿼리) AS A 또는, (서브쿼리) A 이런식으로 뒤쪽에 별칭을 주어야 합니다. SELECT절에는 A = (서브쿼리) 이런식이 가능하죠. 이건 중요한게 아니고, A란 별칭을 주었으니까 A란 테이블이 위와 같은 결과를 가지고 있다고 생각하시면 됩니다. 물론, 물리적으로(실제 저장공간(디스크등…)) 저장되어 있는 것은 아니지만, 가상적으로 테이블이 만들어졌다고 볼 수 있죠 위의 총 6건을 가지고 있는 테이블이 말이죠

그럼 이젠, 서브쿼리는 해석했으니까 위의 결과를 가지고 CASE와 SUM문장만 분석을 하면 되겠군요.

위문장은 새로운 학점이 추가되었을 때에 대한 확장성은 없지만, 예를 보여주기 위한 거니까 이해해 주시기 바랍니다.

위의 결과의 제일 처음 데이터부터 차례대로 생각을 해보도록 하죠

우선 PERSON_ID가 00001인 데이터가 가장 상위의 SELECT절에 왔다고 생각을 해봅시다. 00001은 등급이 D로군요 그럼 가장 상위 SELECT절에서 첫번째 CASE A+일경우에 만족하지 못하므로 0값을 돌려줍니다.

이런식으로 모든 CASE 절에 0값을 돌려주다가 CASE D일 경우에는 만족하므로 해당 값에는 1을 돌려줍니다.

이렇게 되면 D컬럼에만 1이란 값이 있고 나머지는 모두 0입니다. 그 다음 2번 데이터는 등급이 B+입니다. 그러므로 1번 사람과 동일하게 모든 CASE문에 걸쳐서 B+에는 1을 나머지에는 0을 줍니다. 지금까지 B+과 D만 1이고 나머지는 0입니다. 이런 식으로 6번 학생까지 CASE문에 따라 집계를 합니다. 해당 사항을 SUM하게 되면 각 등급별로 인원 수를 구할 수 있죠. COUNT같은 함수를 사용해도 가능합니다. 약간의 차이가 있겠지만요.

지금 보신 쿼리문이 바로 서브쿼리를 파생된 테이블로 사용하는 겁니다. 파생된 테이블을 만들때에는 FROM절 다음에 괄호로 묶어 처리하며, 꼭 별칭을 붙여주어야 하면, 어떤 데이터 형태든 상관이 없습니다. 데이터가 여러건에 여러 컬럼이 와도 상관이 없습니다.

하지만, WHERE절이나, SELECT절에 올때는 약간의 제약이 따릅니다. SELECT절에 올때는 단일 컬럼에 단일 로우의 결과만 반환을 해야 합니다. WHERE절에 올 때는 단일 컬럼에 여러 로우를 반환 할 수 있습니다. 하지만 여러 로우를 반환 할 경우는 해당 서브쿼리 앞에 IN 연산자는 EXISTS 같은 연산으로 풀어야만 합니다. 그리고, SELECT나 WHERE절에는 단일 컬럼으로 와야 된다 했는데, 여기서 col1 + col2 이런 식으로도 가능합니다. 두개의 컬럼이 합쳐져서 하나의 컬럼이 되는 것이므로 단일컬럼으로 볼 수 있기 때문이죠

여기서 SELECT를 사용한 순위를 구하는 문제를 한번 풀어보도록 하겠습니다.

문제9-4 PERSON내의 내용을 가장 높은 점수부터 순위를 구해서 보여주세요 쿼리9-4 SELECT  PERSON_ID, Score,         RANK = (SELECT COUNT(*) FROM #PERSON_POINT WHERE Score >= A.Score) FROM    #PERSON_POINT A ORDER   BY Score DESC

예전에도 셀프 조인으로 해서 순위를 구하는 문제를 해결 했었죠. 하지만 셀프 조인은 데이터를 유심히 관찰할 필요가 있습니다. 하지만, 이렇게 서브쿼리를 보면 문장이 셀프 조인보다는 명확하죠.

보기 쉽고요. 그렇지만, 성능상에는 셀프조인이 더 낳을 수도 있습니다. 물론, 어떤 인덱스가 걸려 있어서 어떤 실행계획이 세워지느냐에 따라 달라지는 거지만, 서브쿼리가 보기 좋고 쓰기 쉬우므로 많이 사용하게 됩니다.

계속 SQL을 다루시게 되는 분들이라면 서브쿼리를 많이 사용하시게 될겁니다.

간단히 설명을 드리도록 하겠습니다.

우선 SELECT * FROM #PERSON_POINT A ORDER BY Score DESC를 하게 되면 결과가 다음과 같습니다.

PERSON_ID	Score
00006	92
00004	90
00002	70
00005	67
00003	65
00001	45

셀프조인의 해법으로 할 때도 설명드렸듯이 자기 점수보다 높은 사람이 몇 명이었는지 세며는 등수가 됩니다.

이걸 서브쿼리로 풀고 있죠

서브쿼리에 보면 WHERE Score >= A.Score 이 부분이 있습니다. A.Score는 외부 쿼리의 점수입니다.

그럼 첫번째 가장 위에 있는 사람 ID가 6번인 사람이 왔을 때 자기 점수보다 높거나 같은 사람들을 뽑아냅니다. 6번의 점수보다 높거나 같은 사람은 자기 자신밖에 없으므로 한 건이 나오고 한 건의 데이터를 세면 1이므로 이것이 바로 등수가 됩니다. 이것이 전형적으로 순위를 구하는 방법입니다. 자기보다 높은 사람이 몇 명인지 세거나 자기보다 낮은 사람이 몇 명인지 세거나 하는 거지요.

이런 식으로 6건의 데이터가 모두 서브쿼리에 자기의 점수를 던져서 한번씩 왔다갔다 하면 등수가 나오죠. 일반적으로 프로그래밍 언어의 함수라고 볼 수도 있겠죠. 외부에서 어떤 값을 주었을 때 해당 값에 알맞은 값이 나오는 거죠, 이번에는 해당 점수의 등급을 서브쿼리로 구해보도록 하죠

쿼리9-5 SELECT  PERSON_ID, Score,         CLASS  = (SELECT MAX(ClassName) FROM #CLASS                        WHERE   FromScore <= A.Score AND toScore >= A.Score) FROM    #PERSON_POINT A ORDER   BY Score DESC

위의 문장도 마찬가지로 우선 SELECT * FROM #PERSON_POINT ORDER BY Score DESC 하셔서 결과를 확인해 보시기 바랍니다. 그 다음 제일 위에 데이터부터 차례대로 서브쿼리에 넣어보신다면 결과가 어떻게 나오는지 아실 수 있을 겁니다.

여기서 한가지 유심히 보셔야 할 것은 서브쿼리 안에 MAX(ClassName)을 했다는 겁니다. 설명드렸듯이 SELECT절에 나오는 값은 단일 컬럼에 단일 로우여야 합니다. 그러므로 MAX를 써서 최대값을 빼내는 겁니다. 사실상 해당 영역에 걸친 데이터는 한 건이므로 MAX를 생략해도 실행은 됩니다. 하지만 될 수 있으면 쓰는 것이 좋습니다. 데이터가 두건 이상 나오게 되면 에러가 나기 때문이죠, 더불어 MAX에 ISNULL까지 처리해주면 금상첨화죠 ISNULL(MAX(ClassName),’’) 이런식으로요, 이건 NULL값이 나와서 예상치 못한 에러가 발생하는걸 막는 것이죠. 물론 이건 죽었다 깨어나도 데이터 한건이란 확신이 있으시다면 안 쓰셔도 상관없지요.^^ 그런데 제가 통상 써와서 그런지 쓰는 것을 권해드리고 싶네요.

결과를 한번 서브쿼리 안으로 한 건씩 던져보도록 하죠 첫 번째 사람은 아이디가 6번에 점수가 92죠. 서브쿼리에서 외부에서 받는 변수는 A.Score입니다. 그러므로 92란 점수를 외부에서 가져오겠죠. 그렇게 되면 6번인 사람 데이터에 대한 서브쿼리는 다음과 같다 할 수 있죠 SELECT MAX(ClassName) FROM #CLASS WHERE FromScore <= 92 AND toScore >= 92 인거와 같죠. 그렇다면 #CLASS 테이블에서 시작점수가 92보다 작거나 같으면서 끝점수가 92점보다 크거나 같은 것은 A+ 등급입니다. 그러므로 A+등급을 리턴하게 되죠

이런식으로 6명의 각각의 점수를 가지고 서브쿼리를 풀어나가게 되는 것이죠. 실제로 SQL Server는 해당 쿼리를 조인으로 해결하게 되어 있습니다. 내부적으로 조인에는 세가지가 있는데 어떤 조인을 사용하는냐에 따라 쿼리에 비용이 달라지게 되죠. 저도 확신은 못하지만 서브쿼리는 루프 조인을 하므로 성능상에서 머지 조인이나, 해시보다 좋지 않을 수 있을 겁니다. 모~~~ ^^ 이 부분은 나중에 여러가지로 설명을 해보도록 하고 넘어가겠습니다.

지금까지 SELECT절에 서브쿼리를 쓰는데 외부에서 꼭 값을 받아서 처리를 했습니다. 하지만 이렇게 외부에 값을 받지 않아도 됩니다. 만약에 PERSON_POINT테이블을 조회하는데 최고 점수자의 점수를 모든 레코드의 옆에 보여주고 싶을 때가 있을 수 있습니다. 다음의 서브 쿼리를 사용할 수도 있죠.

SELECT  PERSON_ID, Score, TOPScore = (SELECT MAX(Score) FROM #PERSON_POINT) FROM    #PERSON_POINT A ORDER   BY Score DESC

이 방법은 이 9장의 맨처음에 앴던 FROM절에 (SELECT TOP 1 Score FROM #PERSON_POINT B ORDER By Score DESC) B 이 파생된 테이블을 사용했던 쿼리와 동일하다 할 수 있죠. FROM절의 TOP 1을 사용 했던 방법보다 훨씬 보기 좋죠. 물론 FROM절에 MAX(Score)를 해서 파생된 테이블을 만들어도 되겠죠

각 방법에 따라 성능이 틀려질 수 있지만, 우선은 서브쿼리의 여러 용도를 알기 위한 것에 초점을 맞추고 있으므로 이런 방법도 있구나 알고 넘어가셨음 합니다.

이렇게 외부 쿼리와 상관없이 단독적으로 값을 구해내는 서브쿼리를 그냥 서브쿼리라 하고 예전처럼 외부쿼리의 값에 영향을 받는 것을 상관 서브 쿼리라고 합니다. 여기서 한가지 생각해 볼 문제는 그냥 서브 쿼리를 쓸 때는 독립적으로 사용할 수 있다는 겁니다. 그러므로 성능에 있어서 상관 서브 쿼리보다 좋을 수 있는 가능성이 높습니다. 옵티마이져가 알아서 유용한 실행 계획을 만드니까요, 하지만, 상관 서브 쿼리를 사용하게 되면 외부의 내용이 결정되어야만 내부의 값이 변경되는 것이므로 좋지 않은 성능을 낼 가능성이 있다고 할 수도 있죠. 물론 절대란 건 없습니다. 상황과 환경에 따라 모든게 가변적이니까요.

이번에는 WHERE절에 서브쿼리를 사용하도록 하겠습니다.

우선 다음의 리스트를 실행 시켜 #PERSON테이블을 만들도록 하겠습니다.

CREATE  TABLE #PERSON (       PERSON_ID CHAR(5) NOT NULL PRIMARY KEY,         PERSON_NAME CHAR(20) NOT NULL DEFAULT '' ) go INSERT #PERSON VALUES('00001','AAA') INSERT #PERSON VALUES('00002','BBB') INSERT #PERSON VALUES('00003','CCC') INSERT #PERSON VALUES('00004','DDD') INSERT #PERSON VALUES('00005','EEE') INSERT #PERSON VALUES('00007','ZZZ')

위에 테이블은 #PERSON_POINT테이블과 PERSON_ID로 관계가 맺어집니다. 그런데 보면 PERSON_POINT에는 6번 학생이 있는데 #PERSON테이블에는 6번 사람이 없습니다. 반대로 #PERSON테이블에는 7번 사람이 있는데 #PERSON_POINT테이블에는 7번 사람이 없습니다. 이런 경우 데이터 무결성이 깨진거죠. 물론, 두 테이블상에 관계를 맺지 않았으니까 무결성이 깨질 수 있죠.

그럼 새로운 테이블과 PERSON_POINT테이블을 가지고 WHERE절의 서브쿼리를 만들어보도록 하겠습니다.

우선, PERSON테이블에서 PERSON_POINT에 있는 데이터들만 찾아내려고 합니다.

아마도 7번 데이터가 빠지고 나와야 겠죠

쿼리9-6 SELECT  * FROM    #PERSON WHERE   PERSON_ID IN (SELECT PERSON_ID FROM #PERSON_POINT)

위와 같은 쿼리를 만들 수 있습니다. 이 방법 말고도 여러 가지 방법이 있을 수 있죠. 간단히 조인으로 해결해도 되고요, 우선 위의 쿼리에 대해 설명을 하자면 우선 IN 다음에 나온 SELECT PERSON_ID FROM #PERSON_POINT를 실행 시키면 PERSON_ID만 열거가 되죠. #PERSON에서 해당 ID가 있는 사람들만 가져오게 되는 거죠. 반대로 NOT IN을 해서 한번 해보시면 반대로 #PERSON에만 있는 데이터를 뽑을 수 있겠죠. 위와 같은 방법으로 EXISTS란 방법이 있습니다

쿼리9-7 SELECT  * FROM    #PERSON A WHERE   EXISTS (SELECT PERSON_ID FROM #PERSON_POINT                               WHERE PERSON_ID = A.PERSON_ID)

EXISTS란 존재여부를 묻는 겁니다. 해당 데이터가 존재하느냐를 묻는거지요. 일반적으로 EXISTS가 IN보다는 좋은 성능을 낼 가능성이 높습니다. 왜냐면 존재하는지 확인이 되면 해당 내용을 바로 빠져나오니까요. 하지만 역시 절대란건 없습니다. EXISTS가 더 나쁠 경우도 있겠죠.

설명을 드리자면 EXISTS에는 조건을 주는 컬럼이 없습니다. 단지 서브쿼리 안에서 외부쿼리로부터 값을 받아와야 한다는 겁니다. 보시면 서브쿼리 안에서 PERSON_ID = A.PERSON_ID란 조건이 있습니다. 이 얘긴 곧 #PERSON 의 테이블의 PERSON_ID를 하나씩 서브쿼리로 던져서 있느냐 없느냐를 확인을 해서 있으면 결과로 곧바로 보여준다는 얘기가 되는거죠. IN을 사용할때는 서브쿼리의 내용에서 해당 #PERSON_POINT 테이블의 PERSON_ID를 모두 읽은 다음 처리를 하게 되고요.

EXISTS 앞에도 NOT 연산자를 넣으면 반대의 경우를 볼 수 있겠죠