[MySQL] 서브쿼리 ( subquery )

이번 글에서는 SELECT 쿼리를 통해 얻은 데이터들을 기반으로 또 SELECT 쿼리를 수행하는 서브쿼리( subquery ) 명령어에 대해 알아보겠습니다.

1. 서브쿼리

서브쿼리란 부모 쿼리 안에 작성하는 내부의 SELECT 쿼리이며, 주로 부모 쿼리의 FROM과 WHERE의 조건으로 사용됩니다.

서브쿼리라고 해서 특별한 문법이 있는 것이 아니라, SELECT를 괄호( )로 묶어서 가독성을 높입니다.

아래는 products 테이블에서 id가 가장 작은 값을 조회한 데이터를 부모 쿼리의 조건절에서 id 값으로 활용하는 예제입니다.

# DELETE FROM items WHERE id = (SELECT MIN(id) FROM products);

• 부모 쿼리 : DELETE FROM items where id=()
• 서브 쿼리 : (SELECT MIN(id) FROM products)

다음은 서브쿼리의 결과를 items 테이블에 추가하는 예제입니다.

# INSERT INTO items (SELECT * FROM products);

• INSERT 하려는 items 테이블의 컬럼들과 서브쿼리의 결과 컬럼들이 같아야 에러가 발생하지 않습니다.
• 이 방식은 데이터의 복사나 이동을 할 때 사용할 수 있습니다.

서브 쿼리는 흔히 사용했던 SELECT 쿼리와 같은 문법으로 사용할 수 있습니다.

다음은 서브쿼리에 정렬을 한 후, 그 데이터를 기반으로 SELECT 쿼리를 수행하는 예제입니다.

# SELECT * FROM (SELECT * FROM products ORDER BY no DESC) as A WHERE A.no <= 3;

• 서브 쿼리의 결과가 하나의 테이블 역할을 할 수 있으므로, 별칭( alias )도 사용할 수 있습니다.

2. 서브 쿼리의 종류 ( 링크 )

서브 쿼리는 사용되는 위치에 따라 종류를 나눠볼 수 있습니다.

1) 서브쿼리 위치에 따른 분류

• 중첩 서브쿼리 ( Nested Subquery )
• WHERE 절에 나타는 서브쿼리
• 인라인 뷰 ( Inline View )
• FROM 절에 나타나는 서브쿼리
• 스칼라 서브쿼리 ( Scalar Subquery )
• SELECT 절에 나타나는 서브쿼리

2) 중첩 서브쿼리 - 서브쿼리 반환에 따른 분류

• 단일 행 서브쿼리 ( Single Row Subquery ) - 예시
• 하나의 컬럼으로 구성된 조회 결과 행 하나를 outer 쿼리에 반환합니다.

• SELECT agent_name, agent_code, phone_no
  FROM agents
  WHERE agent_code = 
    (SELECT agent_code 
     FROM agents
     WHERE agent_name = 'Alex');
  

• 다중 행 서브쿼리 ( Multiple Row Subquery ) - 예시
• IN, ANY, ALL, EXISTS 등의 연산자로 얻은 서브쿼리 결과 여러개의 행을 outer 쿼리에 반환합니다.

• SELECT ord_num,ord_amount,ord_date, cust_code, agent_code
  FROM orders
  WHERE agent_code IN (
    SELECT agent_code FROM agents
    WHERE working_area='Bangalore');

• 다중 컬럼 서브쿼리 ( Multiple Column Subquery ) - 예시
• 서브쿼리 결과 여러개의 행을 outer 쿼리에 반환하는데, WHERE 또는 HAVING ~ IN 연산자 사이에 컬럼 리스트가 괄호로 묶여 있어야 합니다.

• SELECT ord_num, agent_code, ord_date, ord_amount
  FROM orders
  WHERE (agent_code, ord_amount) IN
    (SELECT agent_code, MIN(ord_amount)
     FROM orders 
     GROUP BY agent_code);

3) 서브쿼리가 파싱되는 관점에 따른 분류 - 링크

• Simple Subquery
• 각 테이블에 대해서만 한 번 평가되는 쿼리를 말합니다.

• SELECT SUM (Sales) FROM Store_Information
  WHERE Store_Name IN
    ( SELECT Store_Name FROM Geography
      WHERE Region_Name = 'West');
  

• 상관 서브 쿼리 ( Correlated Subquery )
• 중첩 서브쿼리의 한 종류입니다.
• 상관 서브쿼리와 중첩 서브쿼리와의 차이 - 링크
• Simple Subquery와 달리 테이블 단위가 아닌, 각 행에 대해서 한 번 평가됩니다.

• SELECT SUM (a1.Sales) FROM Store_Information a1
  WHERE a1.Store_Name IN
    ( SELECT Store_Name FROM Geography a2
      WHERE a2.Store_Name = a1.Store_Name);
  

이상으로 서브쿼리에 대해 알아보았습니다.

참고로 일반적으로 join 연산이 서브쿼리 연산보다 성능이 좋기 때문에, 서브쿼리는 join으로 바꿀 수 있으면 성능측정 과정을 통해 바꾸는 것이 좋습니다.

( join 연산과 subquery 연산의 성능 차이 - 링크 )