[MySQL] 서브쿼리(SUBQUERY)

SUBQUERY 란?

SUBQUERY(서브쿼리): 쿼리문을 작성할 때, 그 안에 포함되는 다른 쿼리문. 실행되는 쿼리에 중첩으로 위치해, 정보를 전달한다.

SELECT [컬럼]
FROM [테이블]
WHERE [컬럼] IN (
	SELECT [컬럼]
    	FROM [테이블]
    	WHERE [조건]
);

서브쿼리를 포함하고 있는 쿼리를 외부쿼리(outer query)라고 부르며, 서브쿼리는(inner query)라고도 부른다.

서브쿼리는 위의 예시처럼 소괄호()로 묶어서 표현한다.

서브쿼리는 메인쿼리의 컬럼을 사용할 수 있지만, 메인 쿼리는 서브 쿼리의 컬럼을 사용할 수 없다.

 

서브쿼리의 결과는 개별 값이나 레코드 리스트이다. 그리고 서브쿼리의 결과를 하나의 컬럼으로 사용할 수 있다.

 

서브쿼리 사용이 가능 한 곳

MySQL에서 서브쿼리를 포함할 수 있는 외부쿼리는 SELECT, INSERT, UPDATE, DELETE, SET, DO 문이 있다.

이러한 서브쿼리는 또 다시 다른 서브 쿼리 안에 포함될 수 있다.

• SELECT
• FROM
• WHERE
• HAVING
• ORDER BY
• INSERT문의 VALUES
• UPDATE문의 SET

 

서브쿼리의 특징

서브쿼리를 사용하면 다음과 같은 장점을 얻을 수 있다.

• 서브쿼리는 쿼리를 구조화 시키므로, 쿼리의 각 부분을 명확히 구분할 수 있게 해준다.
• 서브쿼리는 복잡한 JOIN이나 UNION과 같은 동작을 수행할 수 있는 또 다른 방법을 제공한다.
• 서브쿼리는 복잡한 JOIN이나 UNION보다 읽게 편해 가독성이 좋다.

 

서브쿼리와 함께 사용하는 연산자

서브쿼리와 함께 활용 가능한 쿼리는 위에 명시된 쿼리문 말고도 IN, NOT IN, EXISTS, ANY, ALL 가 있다.

 

IN, NOT IN

IN은 특정한 값이 서브쿼리에 있는지 확인할 수 있다. 다음 쿼리는 customers 테이블에서 'CustomerId' 의 값이 서브쿼리에서 돌려받는 값에 속한 결과들만 조회하고 있다.

SELECT *
FROM customers
WHERE CustomerId IN (
	SELECT CustomerId 
    	FROM customers 
    	WHERE CustomerId < 10
    );

만약 IN 대신 NOT IN 을 사용한다면, 서브쿼리에서 조회된 10 미만을 제외한(10을 초과하는) 레코드를 조회한다.

 

EXISTS

EXISTS 또는 NOT EXISTS는 돌려받은 서브쿼리에 존재하는 레코드를 확인한다. 만약 조회하려는 레코드가 존재한다면 참(TRUE)을, 그렇지 않은 경우에는 거짓(FALSE)을 리턴한다.
다음 쿼리문은 employees 테이블에서부터 'EmployeeId' 필드를 조회한다. 이때 서브쿼리로 customers 테이블의 'SupportRepId' 필드값과 employees 테이블의 'EmployeeId' 필드값을 비교해 일치하는 레코드들을 가져온다.

SELECT EmployeeId
FROM employees e
WHERE EXISTS (
	SELECT 1
	FROM customers c
	WHERE c.SupportRepId = e.EmployeeId
	)
ORDER BY EmployeeId

 

ANY

여러개의 비교값 중 하나라도 만족하면 true를 반환한다. IN과 다른점은 비교 연산자를 사용한다는 점이다.(자바의 OR)

• > ANY : 최소값보다 크면
• >= ANY : 최소값보다 크거나 같으면
• < ANY : 최대값보다 작으면
• <= ANY : 최대값보다 작거나 같으면
• = ANY : IN과 같은 기능
• = ANY : NOT IN과 같은 기능

# 서브 쿼리의 AGE 중 최소값보다 큰 AGE를 가진 데이터 조회
SELECT *
FROM TEST
WHERE AGE > ANY(SELECT AGE FROM TEST WHERE ADDR='서울');

 

ALL

전체 값을 비교해서 모두 만족해야 True를 반환한다 (자바의 AND)

• > ALL : 최대값보다 크면
• >= ALL : 최대값보다 크거나 같으면
• < ALL : 최소값보다 작으면
• <= ALL : 최소값보다 작거나 같으면
• = ALL : 서브 쿼리의 결과가 1건이면 괜찮지만 여러 건이면 오류가 발생
• != ALL : 서브 쿼리의 결과가 1건이면 괜찮지만 여러 건이면 오류가 발생

# 서브 쿼리의 AGE 중 최대값보다 큰 AGE를 가진 데이터 조회
SELECT *
FROM TEST
WHERE AGE > ALL(SELECT AGE FROM TEST WHERE ADDR='서울');

 

서브쿼리의 위치에 따른 이름

SELECT [컬럼], (SELECT ...) -- 스칼라 서브쿼리(Scalar Sub Query): SELECT 문에 나타나는 서브쿼리, 컬럼처럼 사용
FROM (SELECT ...) -- 인라인 뷰(Inline View): FROM 문에 나타나는 서브쿼리, 테이블처럼 사용
WHERE [컬럼] = (SELECT ...) -- 중첩 서브 쿼리(Nested Sub Query): WHERE문에 나타나는 서브쿼리, 변수처럼 사용

스칼라 서브쿼리:

• 하나의 레코드만 리턴 가능하며, 두개 이상의 레코드는 리턴할 수 없다.
• 일치하는 데이터가 없더라도 NULL값을 리턴할 수 있다.

-- 정대리 급여와 테이블 전체 평균 급여를 구하시오.
SELECT name, salary, (
  SELECT ROUND(AVG(salary),-1)
  FROM employee) AS '평균급여'
FROM employee
WHERE name = '정대리';

인라인 뷰:

• 서브쿼리가 FROM절에 사용되는 경우 무조건 AS 별칭을 지정해주어야 한다.(테이블처럼 사용해야 하니까!)
• 동적으로 생성된 테이블로 사용가능하다. 임시적이기 때문에 저장되지는 않는다.

-- 직급이 사원인 사람들의 이름과 급여를 구하시오.
SELECT EX1.name,EX1.salary
FROM (
  SELECT *
  FROM employee AS Ii
  WHERE Ii.office_worker='사원') EX1;

중접 서브 쿼리:

• 단일행, 복수(다중)행, 다중 컬럼 이와 같이 종류가 3가지로 나눠져있다.
• 단일 행: 말그대로 단일 레코드를 리턴한다.
• 복수(다중)행: IN, ANY, ALL, EXISTS등의 연사자로 얻은 서브쿼리 결과를 여러개의 행으로 리턴한다.
• 다중 컬럼: 서브쿼리의 실행 결과로 여러 컬럼을 반환한다. 서브쿼리와 메인쿼리에서 비교하고자 하는 컬럼 개수와 컬럼의 위치가 동일해야 한다.

 

DML에서의 서브쿼리

INSERT문 서브쿼리

-- 테이블2의 정보를 뽑아서 그 데이터를 테이블1에 넣어준다.
-- value()들어갈 자리를 서브쿼리로 대체 했다.
INSERT INTO table1 (SELECT * FROM table2);

DELETE문 서브쿼리

-- 인턴의 정보를 구해와서 삭제한다. 
DELETE FROM employee 
WHERE id = (SELECT id FROM employee where office_worker = '인턴' );

UPDATE문 서브쿼리

-- 인턴에 정보를 구해와서 급여를 10만원 인상한다.
UPDATE employee SET salary=(salary+100000)
WHERE id = (SELECT id FROM employee where office_worker = '인턴' );

 

서브쿼리와 JOIN

서브쿼리의 쓰임새를 보면 JOIN에 대체하여 사용할 수 있을 것 같다. 하지만 이는 권장하지 않는 방법이다. 필요에 따라 둘 중 하나만이 유일한 해결방법일 수 있지만, JOIN을 사용할 수 있을 때, 서브쿼리 대신에 JOIN을 사용하도록 하자.

 

그 이유는 서브쿼리는 가독성이 좋지만 서능이 조인에 비해 매우 좋지 않다. 따라서 최신 MySQL은 사용자가 서브쿼리문을 사용하면 자체적으로 조인문으로 변환하여 실행시킨다고 한다.