9-1.서브쿼리

1.단일행 서브쿼리

서브쿼리(SubQuery)는 쿼리문 안에 또 다른 쿼리문이 포함된 구문이다. 단발적인 질문이 아닌 복합적이고 단계적인 질문을 할 때는 여러 개의 쿼리를 중첩해서 사용한다. 리턴하는 행과 열의 개수에 따라 다음과 같이 분류한다.

l 단일행 서브쿼리 : 하나의 값만 리턴한다. 스칼라값 하나를 리턴한다.

l 다중행 서브쿼리 : 여러 개의 행을 리턴한다.

l 다중행 열 서브쿼리 : 여러 개의 열로 구성된 여러 개의 행인 테이블을 리턴한다.

또 외부쿼리와의 관계에 따라 독립 서브쿼리와 연관 서브쿼리로도 구분한다. 서브쿼리부터 난이도가 약간 올라가지만 이해하지 못할 정도로 어렵지는 않으며 나름 재미도 있다. 순서대로 차근 차근 알아보자. 실습을 통해 왜 이런 구문이 필요한지 이해하면 구문도 자연스럽게 익힐 수 있다.

SELECT 명령은 DB 엔진에게 정보를 요구하는 질문이다. SELECT로 할 수 있는 질문은 아주 짧은 단문만 가능하며 FROM 절이 하나밖에 없어 한 테이블에 있는 정보만 조사할 수 있다. 일상 생활에서 흔히 하는 질문을 예로 들어 보면 SELECT로 할 수 있는 질문은 다음 수준 정도밖에 안된다.

너 이름이 뭐니?

너 어느 학교 다니니?

그러나 실생활의 질문은 이보다 복잡해 여러 단계의 질문을 한꺼번에 하는 경우가 많다. 두 개 이상의 문장이 겹쳐 있는 복문이다. 장난하는 것 같지만 우리는 실제로 이보다 복잡한 형태의 질문을 주고 받는다.

너가 다니는 학교의 교장 선생님의 이름은?

담임 선생님의 셋째 딸의 생일은?

실생활을 전산화한 DB에서도 이 정도 수준의 쿼리를 실행할 수 있어야 하며 그 방법이 바로 서브쿼리이다. 집계 함수편에서 최대 인구수를 가진 도시를 구하는 문제를 소개한 적이 있는데 이제 정답을 연구해 보자. 문제의 쿼리는 다음과 같다.

SELECT MAX(popu), name FROM tCity;

인구의 최대수는 MAX(popu)로 구할 수 있고 도시의 이름은 name 필드를 읽으면 된다. 그러나 MAX 함수의 결과는 집계한 하나의 값이고 name 필드는 도시명 여러 개여서 이 둘을 같이 출력할 수는 없다. 최대값은 하나니까 한 칸에 출력하고 도시명은 여러 개니까 각각 나열하면 어떨까?

MAX(popu)	name
974	서울
	부산
	오산
	청주
	전주
	순천
	춘천
	홍천

이 출력이 그럴듯해 보이지만 이건 테이블 모양이 아니다. 딱 하나의 집계값과 도시 목록을 네모 반듯한 모양으로 출력할 수 없다. 일반 필드끼리나 집계 함수끼리는 행 수가 맞아 필드 목록에 같이 쓸 수 있지만 집계 함수와 일반 필드를 같이 쓸 수는 없다.

또한 이 출력 결과는 애초의 질문과도 맞지 않다. MAX(popu)는 최대 인구수를 집계한 것이 맞지만 name 도시중 어떤 도시가 그 인구수를 가진 것인지 이 도표만으로는 알 수 없다. 왼쪽 열과 오른쪽 열의 연관성이 전혀 없다. 위 쿼리문은 말도 안되고 실용성도 없으며 그래서 에러 처리된다. 그렇다면 다음 명령은 어떨까?

SELECT name FROM tCity WHERE popu = MAX(popu);

최대 인구수와 같은 인구를 가진 도시명을 출력했다. 될 것 같지만 이 문장도 에러이다. 집계는 그룹에 대해 실행하는 연산인데 조건절에 그룹이 지정되어 있지 않아 WHERE 절에 쓸 수 없다. MAX(popu)가 도대체 어떤 그룹에 대한 최대값인지 분명하지 않다.

위 두 문장은 인간적으로는 가능할 것처럼 보이지만 기계적으로는 모호함이 있어 둘 다 동작하지 않는다. 기계가 이 명령을 알아 들으려면 문법적으로 가능한 질문을 순서대로 해야 한다. 먼저 최대 인구수를 구해 보자.

SELECT MAX(popu) FROM tCity;

전체 도시의 인구중 가장 큰 값을 조사했다. 도시 목록 전체를 하나의 그룹으로 보고 그 중 최대값을 찾았으니 문법적으로 타당하며 974의 결과를 리턴한다. 최대 인구수를 알았으니 이제 이 인구수를 가진 도시를 찾으면 된다.

SELECT name FROM tCity WHERE popu = 974;

결과는 '서울'이다. 최대 인구수를 조사하고 이 수로부터 도시의 이름을 구해야 하니 두 번의 쿼리가 필요하다. 어쨌든 목적은 달성했지만 사람이 첫 번째 쿼리의 결과를 확인한 후 두 번째 쿼리의 조건문에 직접 기입하는 식이라 불편하다. 두 개의 쿼리를 하나로 묶어 실행할 수 있다면 이 문제를 쉽게 풀 수 있다.

이럴 때 쓰는 것이 서브쿼리이다. 서브쿼리는 다른 쿼리문안에 내장되어 있는 SELECT 문이며 연속적으로 실행할 쿼리를 하나로 합친다. 이때 서브쿼리를 감싸는 전체 쿼리를 외부쿼리라고 부른다. 외부쿼리와 구분하고 실행 순서를 명확히 지정하기 위해 서브쿼리를 괄호로 감싼다. 서브쿼리로 최대 인구수를 가진 도시명을 구해 보자.

SELECT name FROM tCity WHERE popu = (SELECT MAX(popu) FROM tCity);

한방에 '서울'을 구한다. DB 엔진은 괄호 안의 서브쿼리를 먼저 실행하여 최대 인구수를 구한다. 그리고 서브쿼리를 포함하고 있는 외부쿼리를 실행하여 popu가 서브쿼리가 구한 최대 인구수와 같은 도시를 조사하여 출력한다. 마치 일반 언어에서 함수 호출을 중첩하는 것과 같다.

위 문장이 서브쿼리의 가장 일반적이고 전형적인 사용예이다. 최대 인구수는? 그 도시는? 두 개의 질문을 하나로 합쳐 한꺼번에 실행한다고 생각하면 쉽다. 연습삼아 비슷한 유형의 문제를 하나 더 풀어 보자. 상품 목록인 tItem에서 재고량이 가장 많은 상품을 조사해 보자. 먼저 최대 재고량을 찾는다.

SELECT MAX(num) FROM tItem;

tItem 테이블의 num 필드 중 최대값은 80임을 알 수 있다. 이제 이 최대량으로부터 num 필드가 80개인 상품을 조사한다.

SELECT item FROM tItem WHERE num = 80;

결과는 청바지이다. 두 명령을 하나로 합쳐 보자. 최대 재고량인 80 자리에 이 값을 조사하는 서브쿼리를 작성하여 두 쿼리를 하나로 합친다. DB 엔진은 두 명령을 순서대로 실행하여 하나의 결과값 청바지를 출력한다.

SELECT item FROM tItem WHERE num = (SELECT MAX(num) FROM tItem);

단일행 서브쿼리는 하나의 결과만 리턴하며 주로 WHERE, HAVING 등의 조건절에 사용한다. 복합 질문의 앞쪽 질문에 해당하는 값을 서브쿼리로 먼저 조사해 놓고 외부쿼리에서 그 결과값을 사용하는 식이다.

연습 문제

1.tStaff에서 성취도가 제일 높은 직원을 조사하라.

2.tStaff에서 평균 이상의 월급을 받는 직원 목록을 출력하라.

3.tEmployee와 tProject에서 노조 협상건을 맡은 직원의 이름과 월급을 구하라.

4.tMember에서 나이가 가장 많은 회원의 이름과 주소를 조사하라.

2.서브쿼리 중첩

앞에서 실습한 두 개의 서브쿼리는 같은 테이블 내의 값을 조사하는 단순한 형태였다. 서브쿼리는 독립적인 하나의 명령이기 때문에 외부쿼리와는 다른 테이블을 읽을 수도 있다. 어차피 순차적으로 실행되므로 두 쿼리문의 FROM 절에 각각 다른 테이블을 지정해도 상관없다.

그래서 더 복잡한 형태의 질문도 가능하다. 이번에는 청바지의 배송비는 얼마인지 조사해 보자. 청바지는 상품 테이블에 있지만 배송비는 유형 테이블에 있어 두 테이블을 읽어야 한다. 먼저 tItem 테이블에서 청바지는 어떤 유형의 상품인지 조사한다.

SELECT category FROM tItem WHERE item= '청바지';

청바지는 '패션' 유형이다. 다음은 패션 유형의 배송비를 조사한다. tCategory 테이블에서 앞 쿼리의 결과로 나온 '패션' 유형의 배송비를 조사한다.

SELECT delivery FROM tCategory WHERE category = '패션';

2000원이다. 원하는 답을 구하는 쿼리문을 다 작성했으므로 이제 둘을 하나로 합친다. WHERE 절의 '패션' 자리에 상수 대신 '패션'이라는 결과를 만들어 내는 서브쿼리를 작성하고 괄호로 둘러 싼다.

SELECT delivery FROM tCategory WHERE category = (SELECT category FROM tItem

WHERE item = '청바지');

좀 익숙해지면 단계를 거치지 않고도 이 질문을 한 번에 할 수 있다. DB 엔진이 서브쿼리를 먼저 실행하고 그 결과를 외부쿼리에 쓰듯이 우리의 사고 흐름도 이 순서대로 명령문을 하나씩 작성하면 된다. 이 질문을 말로 표현해 보면 '청바지의 유형을 조사하고 그 유형의 배송비를 출력하라'이다.

서브쿼리의 중첩 횟수에는 제약이 없어 서브쿼리내에 또 다른 서브쿼리를 포함할 수 있다. 서브쿼리의 중첩을 사용하면 얼마든지 복잡한 질문도 할 수 있는데 단계별로 '7만원짜리 상품을 구입한 사람의 나이'를 구해 보자. 먼저 7만원짜리 상품이 무엇인지 조사한다.

SELECT item FROM tItem WHERE price = 70000;

상품 정보는 tItem 테이블에 있다. 이 테이블에서 70000원짜리 상품을 조사해 보면 전자담배이다. 다음은 이 상품을 구입한 사람이 누구인지 조사한다. 구입 내역은 tOrder 테이블에 있다.

SELECT member FROM tOrder WHERE item =

(SELECT item FROM tItem WHERE price = 70000);

앞 단계에서 구한 상품명을 tOrder 테이블의 item 필드와 비교하여 member 필드를 읽으면 방자가 구입했음을 알 수 있다. 마지막으로 방자의 나이를 tMember에서 읽는다.

SELECT age FROM tMember WHERE member =

(SELECT member FROM tOrder WHERE item =

(SELECT item FROM tItem WHERE price = 70000));

28세로 조사된다. 다행히 미성년자는 아니어서 성인용품을 구입할 수 있다. 가장 안쪽에 있는 쿼리부터 순서대로 실행하여 최종적으로 원하는 값을 구해 출력한다.

이 질문에 쿼리가 세 개씩이나 필요한 이유는 상품, 주문, 회원에 대한 정보가 세 개의 테이블에 흩어져 있기 때문이다. 각 테이블을 다음 순서대로 검색한다. 최초 알고 있던 정보는 70000원이라는 가격뿐인데 이 정보로부터 상품을 찾고, 상품으로 사람을 찾고, 사람으로부터 나이를 차례대로 찾았다.

이 예제는 테이블의 내용을 뻔히 알고 있어 별다른 에러 처리는 하지 않았다. 만약 70000원짜리 상품이 없다거나 구입한 사람이 없으면 결과셋은 없다. 반대로 상품이 여러 개이거나 구입한 사람이 둘 이상이라면 에러 처리되는데 이때는 TOP 1을 넣어 첫 구입자를 찾으면 된다.

연습 문제

5.대추를 구입한 회원의 이름과 이 회원의 예치금을 구하라.

6.춘향이가 구입한 상품의 가격을 조사하라.

3.다중행 서브쿼리

서브쿼리의 결과가 하나뿐인 유형을 단일행 서브쿼리라고 한다. 단일값이므로 조건절에서 =, <, > 등의 비교 연산자와 함께 사용할 수 있다. 비교 연산자는 필드와 값을 비교하는 것이어서 우변이 반드시 하나의 확정된 값이어야 한다.

이에 비해 여러 개의 결과를 리턴하는 것을 다중행 서브쿼리라고 한다. 단일값이 아닌 목록을 리턴하기 때문에 값끼리 비교하는 = 연산자와 함께 사용할 수 없다. 다음 쿼리문은 향단이가 구입한 상품의 가격을 조사하는데 문법에는 이상이 없지만 데이터가 조건에 맞지 않아 에러 처리된다.

SELECT price FROM tItem WHERE item =

(SELECT item FROM tOrder WHERE member = '향단');

서브쿼리는 구입 목록인 tOrder 테이블에서 향단이가 뭘 샀는지 조사한다. 안쪽 서브쿼리만 실행해 보면 향단이가 산 것은 대추와 사과 2개이다. = 연산자는 필드값을 두 개의 결과와 비교할 수 없어 다음 에러를 출력한다.

ORA-01427: 단일 행 하위 질의에 2개 이상의 행이 리턴되었습니다.

= 연산자로 비교하려면 조건절의 쿼리문은 반드시 하나의 단일값을 리턴해야 한다. 꼭 조사하려면 결과셋중 하나의 값만 조사하여 비교하면 된다.

SELECT price FROM tItem WHERE item =

(SELECT item FROM tOrder WHERE member = '향단'

ORDER BY item OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY);

구입한 상품 중 최초 하나의 상품만 리턴하면 = 연산자로 비교할 수 있다. 그러나 이것이 과연 원하는 결과였는지는 잘 생각해 봐야 한다. 애초의 질문대로 향단이가 구입한 모든 상품의 가격을 알고 싶다면 = 연산자로 비교해서는 안되며 IN 연산자를 사용해야 한다.

SELECT item, price FROM tItem WHERE item IN

(SELECT item FROM tOrder WHERE member = '향단');

IN은 = 과 달리 여러 개의 값과 비교한다. 서브쿼리가 두 개 이상의 값을 리턴하면 결과셋을 괄호 안에 나열하며 IN 연산자는 이 값을 순서대로 비교한다. 서브쿼리까지 실행한 직후의 외부쿼리는 다음과 같다. 2개의 상품에 대해 이름과 가격을 같이 출력한다.

SELECT item, price FROM tItem WHERE item IN ('대추', '사과');

단일행을 리턴하는 서브쿼리는 = 연산자와 비교하고 다중행을 리턴하는 서브쿼리는 IN 연산자와 비교한다. 물론 IN 연산자를 쓰면 하나든, 여러 개이든 비교할 수 있지만 집계 함수의 결과는 항상 하나여서 이때는 = 연산자를 쓰는 것이 더 쉽다. 결과값이 아예 없는 것은 어떤 연산자로 비교해도 상관없다.

SELECT price FROM tItem WHERE item = (SELECT item FROM tOrder WHERE member = '이도령');

이도령이 구입한 상품의 가격을 조사하는 쿼리인데 이도령은 아무것도 구입하지 않았다. 결과가 없으면 NULL을 리턴하며 조건절에서 필드와 NULL을 비교하는 것은 문법적으로 가능하다. 아무 결과셋도 출력하지 않을 뿐 에러는 아니다.

4.다중열 서브쿼리

단일행, 다중행 서브쿼리는 결과셋의 컬럼이 하나밖에 없으며 그래서 특정값과 비교할 수 있다. 이에 비해 다중열 서브쿼리는 결과셋의 컬럼이 여러 개이며 한꺼번에 여러 값과 비교한다. 오라클과 MariaDB는 다중열 서브쿼리를 잘 지원하지만 SQL Server는 아직 지원하지 않는다.

이런 서브쿼리가 왜 필요한지 단계별로 문제를 풀어 보며 연구해 보자. 윤봉길과 같은 부서에 근무하는 같은 성별의 직원 목록을 조사해 보자. 이 문제를 풀려면 먼저 윤봉길이 어느 부서에 근무하고 성별이 무엇인지 조사해야 하며 그 결과로부터 조건에 맞는 직원을 찾으면 된다.

SELECT depart, gender FROM tStaff WHERE name = '윤봉길';

SELECT * FROM tStaff WHERE depart = '영업부' AND gender = '남';

조사해 보니 윤봉길은 영업부에 근무하는 남자 직원이다. 이 조건에 맞는 직원 목록을 조사해서 출력하면 된다. 윤봉길 자신까지 포함하여 4명의 직원이 있다. 굉장히 쉬운 문제이지만 작업을 두 단계로 나누어 사람이 결과를 확인한 후 다시 명령을 내려야 하는 불편함이 있다.

임의의 직원에 대해 똑같은 조사를 하려면 두 번 손이 가야 하니 번거롭다. 이럴 때 조사 후 조건에 맞는 목록 출력까지 한 번에 수행하는 서브쿼리가 필요하다. 다음 서브쿼리의 직원명을 바꾸면 같은 부서, 같은 성별의 직원 목록을 바로 조사할 수 있다. 이번에는 안중근의 동료를 조사해 보자.

SELECT * FROM tStaff WHERE depart =

(SELECT depart FROM tStaff WHERE name = '안중근')

AND gender = (SELECT gender FROM tStaff WHERE name = '안중근');

직관적이고 쉽지만 부서와 성별을 각각의 서브쿼리로 개별 조사하다 보니 총 3번의 쿼리를 수행한다. 다중열 서브쿼리를 사용하면 두 개의 컬럼을 리턴한 후 한꺼번에 비교한다.

SELECT * FROM tStaff WHERE (depart, gender) =

(SELECT depart, gender FROM tStaff WHERE name = '안중근');

WHERE 절에 비교 대상 필드를 괄호 안에 (depart, gender)로 적으면 서브쿼리의 컬럼과 1:1로 비교하여 두 필드가 모두 일치하는 레코드를 검색한다. 여러 필드를 한꺼번에 비교할 수 있어 편리하다. 단, 일괄 비교가 성립하려면 비교 대상과 서브쿼리의 컬럼 개수는 반드시 일치해야 한다.

다음은 더 복잡한 문제를 풀어보자. 서브쿼리가 다중열이면서 다중행인 결과셋을 리턴하면 복수 레코드의 복수 필드를 한꺼번에 비교할 수 있다. 다음 쿼리문은 부서별 최고 월급자의 목록을 조사한다. 여러 개의 부서가 있고 각 부서명과 최고 월급을 비교해야 한다.

SELECT * FROM tStaff WHERE (depart, salary) IN

(SELECT depart, MAX(salary) FROM tStaff GROUP BY depart);

안쪽의 서브쿼리는 부서별로 그룹핑하여 부서명과 최고 월급을 구한다. 부서별로 두 개의 컬럼이 있고 그런 부서가 세 개 있으니 다중열 다중행 결과셋이다. 결국 하나의 테이블인 셈이다.

이제 이 조건에 맞는 직원의 상세 정보를 조사해 보자. 그룹핑할 때는 기준 필드나 집계 함수만 쓸 수 있어 부서별 최고 월급까지만 조사할 수 있고 그 직원이 누구인지는 알 수 없다. 그러나 외부쿼리로 한 번 감싸 부서명과 최고 월급을 조건으로 사용하면 뭐든지 조사할 수 있다.

총무부에서 월급 420만원을 받는 사람이 누구인지 WHERE (depart, salary) 를 서브쿼리의 결과셋과 비교하면 된다. 결과셋이 다중행이므로 반드시 IN 연산자로 비교해야 한다. 단 한 줄의 명령으로 부서별 최고 월급자의 신상 정보까지 다 조사한다.

이 명령은 그룹핑한 후 집계 함수를 사용하기 때문에 동일부서, 동일성별 목록을 조사하는 것과는 달리 두 개의 서브쿼리로 분할할 수 없다. 그룹핑과 집계는 한 번에 해야 하며 그 결과를 일괄 비교해야 하니 다중열 서브쿼리가 필수적이다.

다중열 서브쿼리를 지원하지 않는 SQL Server에서는 이 문제를 풀 수 없을까? 물론 그렇지는 않다. SQL은 유연성이 높아 대안적인 방법이 얼마든지 있다. 다음 두 가지 방법은 어떤 DBMS나 잘 실행된다. 아직 배우지 않은 문법인데 예습하는 셈치고 구경만 해 보자.

SELECT * FROM tStaff S WHERE salary =

(SELECT MAX(salary) FROM tStaff WHERE depart = S.depart);

위 문장은 연관 서브쿼리이며 각 직원의 소속 부서에서 최고 월급을 조사하여 최고 월급자인 경우만 골라낸다. 다음 문장은 조인 문법을 사용하며 더 일반적인 해결책이다.

SELECT T.* FROM tStaff T

INNER JOIN (SELECT depart, MAX(salary) ms FROM tStaff GROUP BY depart) M

ON T.depart = M.depart AND T.salary = M.ms;

그룹핑 집계 결과를 인라인뷰로 만든 후 내부 조인하여 최고 월급자를 골라낸다. 연관 서브쿼리는 다음 절에서, 인라인뷰는 이 장 뒷부분에서 다루며 조인은 다음 장에서 다룬다. 즉, 이 책을 순서대로 읽는 중이라면 아직까지는 이해할 수 없는 명령이다. 이런 복잡한 명령 대신 간편하게 쓸 수 있는 문법이 바로 다중열 서브쿼리이다.

다중열을 UPDATE 문에도 사용할 수 있다. SET 절의 괄호 안에 갱신 대상 필드를 쓰고 서브쿼리에서 같은 개수의 컬럼을 리턴하면 1:1로 대입하여 한꺼번에 갱신한다. 다음 명령은 부산의 면적과 인구를 서울로 복사한다.

UPDATE tCity SET (area, popu) = (SELECT area, popu FROM tCity WHERE name='부산')

WHERE name = '서울';

한 번에 여러 필드의 값을 일괄 갱신할 수 있어 편리하다. 그러나 이 명령이 정상 동작하려면 몇 가지 조건을 맞춰야 한다. SET 절의 컬럼수와 서브쿼리가 리턴하는 컬럼수가 일치해야 하며 서브쿼리는 반드시 1개의 레코드만 리턴해야 한다. 1:1 갱신을 위해 상식적인 조건이다.