서브쿼리에 대해 알아보자 1편 (feat.단일행, 다중열, 다중행 서브쿼리)

안녕하세요, 끙정입니다.

 

오늘부터는 서브쿼리에 대해서 알아보겠습니다.

서브쿼리부터는 이제 쿼리가 다소 길어지고 복잡해질 수 있습니다.

그러나 조금씩 이해를 하고 쿼리를 바라본다면,

서브쿼리도 금방 익숙해질 수 있습니다.

 

서브쿼리는 쿼리문 안에 또 다른 쿼리문이 포함된 구문입니다.

단발적인 질문이 아닌 복합적이고 단계적인 질문을 할 때는 여러 개의 쿼리를 중첩해서 사용합니다.

리턴하는 행과 열의 개수에 따라 다음과 같이 분류합니다.

 

1) 단일행 서브쿼리: 하나의 값만 리턴. 스칼라값 하나를 리턴.

2) 다중행 서브쿼리: 여러 개의 행을 리턴.

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

 

또한 외부쿼리와의 관계에 따라 독립 서브쿼리와 연관 서브쿼리로도 구분합니다.

 

1. 단일행 서브쿼리

SELECT로 할 수 있는 질문은 아주 짧은 단문만 가능하며,

FROM 절이 하나 밖에 없어 한 테이블에 있는 정보만 조사할 수 있습니다.

인간의 언어로 표현하면 다음과 비슷합니다.

 

1) 당신의 이름은 무엇입니까?

2) 당신은 어느 학교를 다닙니까?

 

그러나 실생활에서의 질문은 이보다 더 복잡합니다.

예를 들면 다음과 같습니다.

 

1) 당신이 다니는 학교의 교장 선생님의 이름은?

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

 

이러한 질문은 단일 쿼리만으로는 출력을 할 수 없습니다.

예로, 최대 인구수를 가진 도시를 구하는 문제를 쿼리로 풀어보겠습니다.

 

/* MAX를 통해 popu와 name을 출력하면 될까? */
SELECT MAX(popu), name FROM tCity;

/* WHERE 조건으로 MAX(popu)를 출력하면 될까? */
SELECT name FROM tCity WHERE = MAX(popu);

 

그러나 모두 에러가 뜹니다.

 

두 쿼리 모두 에러가 뜹니다.

첫 번째 쿼리는 MAX(popu) 는 단일값이지만, name은 다중값이기 때문에 테이블로 출력될 수 없습니다.

두 번째 쿼리는 MAX(popu) 가 어떤 그룹에서의 popu에 대한 MAX 값인지 전혀 알지 못하기 때문에 에러가 뜹니다.

 

우리는 위의 결과를 얻기 위해 아래와 같은 로직을 거쳐야 합니다.

일단 tCity 테이블에서 popu의 MAX 값을 찾고,

그 값을 조건으로 가지는 도시의 name을 출력합니다.

즉, 아래와 같습니다.

 

/* tCity에서 popu가 가장 큰 값을 구한다. */
SELECT MAX(popu) FROM tCity;

/* tCity 에서 popu가 2000인 값(MAX(popu))을 찾는다 */
SELECT name FROM tCity WHERE popu = 2000;

/* tCity에서 popu가 MAX인 값을 가지고 있는 name 을 출력한다. */
SELECT name FROM tCity WHERE popu = (SELECT MAX(popu) FROM tCity);

 

 

 

2. 서브쿼리 중첩

서브쿼리는 독립적인 하나의 명령이기 때문에 외부쿼리와는 다른 테이블을 읽을 수도 있습니다.

두 쿼리문의 FROM 절에 각각 다른 테이블을 지정하는 것이지요.

 

만일 청바지의 배송비가 얼마인지 확인해야 할 때,

청바지는 상품 테이블에 있고, 배송비는 유형 테이블에 있다면 두 테이블을 읽어야 합니다.

다음과 같은 로직을 거치면 됩니다.

 

/* tItem에서 item이 '청바지'인 category 값을 출력 */
SELECT category FROM tItem WHERE item = '청바지';

/* tCategory에서 category가 '패션'인 delivery 값을 출력 */
SELECT delivery FROM tCategory WHERE category = '패션';

/* 위 두 쿼리를 하나의 쿼리로 출력 */
SELECT delivery
FROM tCategory
WHERE category = (SELECT category
		  FROM tItem
                  WHERE item = '청바지')

 

처음에는 다소 복잡하고 헷갈릴 수 있지만,

로직을 따라가다보면 금방 익숙해질 수 있습니다.

 

서브쿼리는 중첩 횟수에 제약이 없어서 서브쿼리내에 또 다른 서브쿼리를 포함할 수 있습니다.

7만원짜리 상품을 구입한 사람의 나이를 구해보겠습니다.

먼저 7만원짜리 상품을 출력하고,

해당 상품을 구입한 사람을 출력하고,

해당 사람의 나이를 출력합니다.

 

SELECT item FROM tItem WHERE price = 70000;

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

SELECT age
FROM tMember
WHERE member = (SELECT member
		FROM tOrder
            	WHERE item = (SELECT item
                	      FROM tItem
                              WHERE price = 70000));

 

3. 다중행 서브쿼리

서브쿼리의 결과가 하나뿐인 유형을 단일행 서브쿼리라고 합니다.

단일값이므로 조건절에서 =, <, > 등의 비교 연산자와 함께 사용할 수 있습니다.

비교 연산자는 필드와 값을 비교하는 것이어서 우변이 반드시 하나의 확정된 값이어야 합니다.

 

이에 비해 여러 개의 결과를 리턴하는 것을 다중행 서브쿼리라고 합니다.

단일값이 아닌 목록을 리턴하기 때문에 비교하는 = 연산자와 함께 사용할 수 없습니다.

 

아래와 같이 tItem 테이블과 tOrder 테이블이 존재한다고 할 때,

향단이가 산 물건의 가격을 알고 싶다고 가정해보겠습니다.

 

귀찮지만 만들어 보았습니다.

 

위에서 배운대로 해보겠습니다.

 

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

 

그러나 결과는 아래와 같습니다.

 

에러가 발생했다!

 

위에서 배운 것은 단일값 서브쿼리였기 때문에 에러가 뜨지 않고 출력이 잘 되었습니다.

그러나 서브쿼리에서 향단이가 구입한 아이템이 2개 이상이기 때문에,

위 쿼리는 에러가 발생합니다.

WHERE item = '대추', '사과' 라는 구문은 논리적으로 성립될 수 없기 때문입니다.

 

따라서 굳이 = 연산자로 하나의 값만 출력하고자 한다면,

서브쿼리에서 1개의 값만 출력해주거나,

아니면 = 대신에 IN을 사용하는 것이 맞습니다.

 

SELECT item, price FROM tItem WHERE item IN (SELECT item FROM tOrder WHERE member = '향단');

 

 

만일 출력할 값이 아무것도 없다면 에러가 발생하지 않을 것입니다.

왜냐면 = NULL 은 논리적 오류는 아니기 때문입니다.

 

'막눈'이 구매한 아이템이 없다면 WHERE item = NULL 이기 때문에 에러는 발생하지 않는다.

 

4. 다중열 서브쿼리 (오라클, MariaDB Only)

단일행, 다중행 서브쿼리는 결과셋의 컬럼이 하나 밖에 없으며,

그래서 특정값과 비교할 수 있습니다.

이에 비해 다중열 서브쿼리는 결과셋의 컬럼이 여러 개이며 한꺼번에 여러 값과 비교합니다.

오라클과 MariaDB는 다중열 서브쿼리를 잘 지원하지만 SQL Server는 아직 지원하지 않습니다.

 

왜 이러한 서브쿼리가 필요한지 논리적으로 접근해보겠습니다.

 

윤봉길과 같은 부서에서 근무하는 같은 성별의 직원 목록을 조사해보겠습니다.

이 문제를 풀려면 먼저 윤봉길이 어느 부서에 근무하는지, 성별이 무엇인지 조사해야 하며,

그 결과로부터 조건에 맞는 직원을 찾으면 됩니다.

 

우리가 출력해야 할 직원은 강감찬, 성삼문, 안창호, 을지문덕이다.

 

먼저 윤봉길의 부서와 성별을 찾습니다.

 

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

 

 

다음으로 WHERE AND 조건으로 영업부의 남성을 찾습니다.

 

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

 

 

위 두 쿼리를 서브쿼리를 통해 하나의 쿼리로 변환하겠습니다.

 

SELECT * FROM tStaff
WHERE depart = (SELECT depart FROM tStaff WHERE name = '윤봉길')
AND gender = (SELECT gender FROM tStaff WHERE name = '윤봉길');

 

성공은 했지만 복잡하다! 조건이 늘어나면 서브쿼리를 몇개나 써야 하는 거야!

 

원하는 결과를 얻기는 했지만 서브쿼리를 두 번이나 작성하는 번거로움이 있습니다.

이때 필요한 것이 다중열 서브쿼리입니다.

 

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

 

하나의 서브쿼리만으로 동일한 결과를 얻어냈다!

 

위와 같이 WHERE 절에 비교 대상 필드를 괄호 안에 넣고,

서브쿼리의 컬럼과 1:1로 비교하여 두 필드가 모두 일치하는 레코드를 검색합니다.

여러 필드를 한꺼번에 비교할 수 있어 편리합니다.

단, 일괄 비교가 성립하려면 비교 대상과 서브쿼리의 컬럼 개수는 반드시 일치해야 합니다.

 

다음은 더 복잡한 문제를 풀어보겠습니다.

서브쿼리가 다중열이면서 다중행인 결과셋을 리턴하면,

복수 레코드의 복수 필드를 한꺼번에 비교할 수 있습니다.

 

다음 쿼리문은 부서별 최고 월급자의 목록을 조사합니다.

여러 개의 부서가 있고 각 부서명과 최고 월급을 비교해야 합니다.

 

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

 

 

서브쿼리를 통해 GROUP BY depart 로 부서명과 MAX(salary)를 출력하고,

WHERE (depart, salary) IN 조건절을 통해서 다중열, 다중행을 출력합니다.

단 한줄을 통해 각 부서별 최고 월급자의 신상을 파악할 수 있습니다.

 

그렇다면 다중열 서브쿼리를 지원하지 않는 SQL Server는 어떻게 할까요?

여러가지 방법이 있습니다.

 

설명은 다른 포스팅에서 하겠습니다.

 

다중열을 UPDATE 문에도 사용할 수 있습니다.

SET 절의 괄호 안에 갱신 대상 필드를 쓰고 서브쿼리에서 같은 개수의 컬럼을 리턴하면,

1:1로 대입하여 한꺼번에 갱신됩니다.

 

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

 

다중열 서브쿼리로 UPDATE 하였다.

 

 

그만 알아보겠습니다.

 

 

출처

http://www.yes24.com/Product/Goods/101637633

김상형의 SQL 정복 : 소문난 명강의 (무료특별판) - YES24