[SQL] 서브쿼리의 개념과 종류

1. 서브쿼리

하나의 쿼리 안에 존재하는 또 다른 쿼리. 

 

 

서브쿼리는 위치에 따라 다음과 같이 나눌 수 있다. 

 

select 절	스칼라 서브쿼리(Scalar Subquery)
from 절	인라인 뷰(inline view)
where 절, Having 절	중첩 서브쿼리(nested subquery)

 

• 서브쿼리는 order by 절, insert 문의 value 절 등에 사용이 가능하다. 
• 다중 행 서브쿼리의 경우 = 조건과 함께 사용할 수 없다.
• 다중 컬럼 서브쿼리의 경우 in 절과 함께 사용할 수 있다. 

 

(1) 스칼라 서브쿼리

주로 select 절에 위치하지만 컬럼이 올 수 있는 대부분 위치에 사용할 수 있다. 컬럼 대신 사용되므로 반드시 하나의 값만을 반환해야 하며 그렇지 않은 경우 에러를 발생시킨다.

select, update 문의 set 절, order by 절에 위치할 수 있다. 

 

다음은 salaries 테이블에서 각 직원의 평균 급여를 구하는 예시 쿼리:

SELECT 
    emp_no, 
    salary,
    (SELECT AVG(salary) 
     FROM salaries s2 
     WHERE s2.emp_no = s1.emp_no) AS avg_salary
FROM 
    salaries s1
GROUP BY 
    emp_no, salary;

 

스칼라 서브쿼리를 이용해 salaries 테이블에 없는 평균 급여를 출력할 수 있다. 

 

(2) 인라인 뷰

from 절 등 테이블 명이 올 수 있는 위치에 사용 가능.

 

SELECT M.PRODUCT_CODE,
	   S.PRODUCT_NAME,
       S.PRICE,
       M.MEMBER_ID,
       M.CONTENT
FROM PRODUCT_REVIEW M,
	 (SELECT PRODUCT_CODE,
     		 PRODUCT_NAME,
             PRICE
      FROM PRODUCT) S
WHERE M.PRODUCT_CODE=S.PRODUCT_CODE;

 

 

(3) 중첩 서브쿼리(Nested Subquery)

where 절과 having 절에 사용할 수 있다. 중첩 서브쿼리는 메인 쿼리와의 관계에 따라 다음과 같이 나눌 수 있다. 

비연관 서브쿼리	메인 쿼리와 관계를 맺고 있지 않음
연관 서브쿼리	메인 쿼리와 관계를 맺고 있음

 

1. 비연관 서브쿼리: 서브 쿼리 내에 메인 쿼리의 컬럼이 존재하지 않음.

SELECT 
    first_name, 
    last_name,
    salary
FROM 
    employees
WHERE 
    salary > (
        SELECT 
            AVG(salary) 
        FROM 
            employees
    );

 

 

 

2. 연관 서브쿼리 : 서브 쿼리 내에 메인 쿼리의 컬럼 존재. 

 

SELECT 
    first_name, 
    last_name, 
    salary
FROM 
    employees e
WHERE 
    salary > (
        SELECT 
            AVG(salary)
        FROM 
            employees
        WHERE 
            department_id = e.department_id
    );

 

e.department_id 처럼 메인 쿼리의 컬럼이 존재한다. 

 

중첩 서브쿼리는 반환하는 데이터 형태에 따라 다음과 같이 나눌 수 있다. 

단일 행 서브쿼리	- 서브쿼리가 1건 이하의 데이터를 반환 - 단일 행 비교 연산자와 함께 사용 예 ) =, <,>,<=,>=,<>
다중 행 서브쿼리	- 서브 쿼리가 여러 건의 데이터를 반환 - 다중 행 비교 연산자와 함게 사용 예 ) in, all, any, some, exists
다중컬럼 서브쿼리	서브쿼리가 여러 컬럼의 데이터를 반환

 

 

1. 단일 행 서브쿼리 : 항상 1건 이하의 결과만 반환

select * from product
where price=(select max(price) from product);

 

가장 가격이 높은 상품의 정보를 조회

 

2. 다중 행 서브쿼리 : 2건 이상의 행을 반환

select * from product
where product_code in (select product_code from product_review);

 

리뷰가 있는 상품의 정보를 조회

2건 이상의 행, 즉 다중 행을 반환할 때는 = 조건과 같이 쓸 수 없다는 점을 명심하자. 

다중 행을 반환하는 서브쿼리는 in절과 사용가능하다. 

 

 

3. 다중컬럼 서브쿼리

 

SELECT *
FROM EMPLOYEES
WHERE (JOB_ID,SALARY) IN (SELECT JOB_IDKMAX_SALARY
                          FROM JOBS
                          WHERE MAX_SALARY=10000);

 

위 쿼리는 max_salary가 10000인 직업을 가지고 있으며 실제 salary가 max_salary 와 일치하는 직원의 정보를 조회한다.