서브쿼리란 뭘까?

글을 작성하는 목적

저는 서브쿼리에 대해 쿼리안에 SELECT문이 하나더 있다? 정도만 알고있습니다. 서브쿼리에 대해 누군가 물어보면 자신있게 대답할 수 없을것같아 이 글을 작성하게 되었습니다.

학습의 목표는 다음의 질문들에 대해 대답하기입니다. 하나하나 질문들을 대답하고, 꼬리질문들에 대해 더 생각을 하는 방식으로 진행하려 합니다.

• 서브쿼리란 무엇인가요?
• 왜 서브쿼리를 사용하는 것인가요?
• 서브쿼리는 어떻게 사용하나요?
• 서브쿼리의 종류는 어떤것이 있나요?
• 상관서브 쿼리란 무엇인가요?
• 서브쿼리의 특징은 무엇인가요?

서브쿼리란 무엇인가요?

서브쿼리란 다른 쿼리내에 중첩된 쿼리입니다. 외부쿼리에서 소괄호에 감싸진 SELECT문이 서브쿼리가 됩니다.

• 외부쿼리란 무엇인가요?
  • 외부쿼리는 메인쿼리로 서브쿼리를 가지고있는 바깥의 쿼리를 뜻합니다.

왜 서브쿼리를 사용하는 것인가요?

데이터베이스를 사용할때 때로는 더 복잡하고 세밀한 쿼리를 사용하고 싶을때가 있습니다. 예를들면 전체 평균 급여보다 높은 급여를 받는 직원을 찾는 경우를 생각할 수 있습니다.

SELECT id, salary FROM employees WHERE salary > AVG(salary)

아래와 같은 쿼리는 불가능합니다. 왜냐하면 SQL에서 WHERE절 내에서 집계함수를 사용하는것이 허용되지 않기 때문입니다.

그래서 다음과 같이 서브쿼리를 작성해야합니다. 서브쿼리를 사용하면 복잡한 조건을 만족하는 데이터를 찾거나 특정 계산을 수행한결과(아래와 같은)를 기준으로 데이터를 필터링 할 수 있습니다.

SELECT id, salary FROM employees
WHERE salary > (
    SELECT AVG(salary) FROM employees
)

서브쿼리는 어떻게 사용하나요?

SELECT문의 서브쿼리

• select절의 서브쿼리

SELECT id, salary,
       (
           SELECT AVG(salary)
           FROM employees
       ) AS avg_salary
FROM employees

조회하는 모든행에 서브쿼리로 얻은 모든 직원의 평균급여를 추가해 표시합니다.

• from 절에서의 서브쿼리

SELECT E.id, E.salary, AvgSal.avg_salary
FROM employees E,
     (SELECT AVG(salary) AS avg_salary FROM employees) AvgSal

조회하는 모든행에 서브쿼리로 얻은 모든 직원의 평균급여를 추가해 표시합니다.

• where 절에서의 서브쿼리

SELECT id, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary) FROM employees
)

평균급여보다 높은 직원을 조회합니다.

• join 절에서의 서브쿼리

SELECT U.id, U.name, O.order_id, O.order_date
FROM users U
JOIN (
    SELECT user_id, order_id, order_date
    FROM orders
    WHERE order_date > '2023-02-01'
) O ON U.id = O.user_id

특정 조건에 맞는 데이터를 다른 테이블과 조인할때 사용합니다. 최근에 가입한 사용자의 정보를 조회합니다.

• having 절에서의 서브쿼리

SELECT department_id, AVG(salary) AS avg_department_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > (
    SELECT AVG(salary) FROM employees
)

평균 급여가 전체직원의 평균 급여보다 높은 부서를 조회합니다.

 

INSERT문 에서의 서브쿼리

INSERT INTO recent_users (id, name, join_date)
SELECT id, name, join_date
FROM users
WHERE join_date >= '2023-02-01'

INSERT문에서 서브쿼리를 사용해 이미 존재하는 테이블에서 데이터를 선택해 새 테이블에 삽입할 수 있습니다. 최근에 가입한 유저들을 테이블에 삽입합니다.

UPDATE절의 서브쿼리

UPDATE employees
SET salary = salary * 1.10
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
)

업데이트 해야하는 데이터를 선택할 수 있습니다. 평균급여보다 높은 급여를 받는 직원들의 급여를 인상합니다.

DELETE 절의 서브쿼리

DELETE FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
)

서브쿼리를 사용해 삭제할 데이터를 선택할 수 있습니다. 평균보다 높은 급여를 받는 직원들을 해고합니다.

서브쿼리의 종류는 어떤것이 있나요?

• 단일 행 서브쿼리

SELECT * FROM employees
WHERE salary = (
    SELECT MAX(salary)
    FROM employees
)

단일 행 서브쿼리는 하나의 행만 반환합니다. 이때 >, =, <, 등 비교연산자와 함께 사용될 수 있습니다.

• 다중 행 서브쿼리

SELECT * FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE location_id = '1000'
)

다중 행 서브쿼리는 여러개의 행을 반환하게 됩니다.

상관 서브쿼리란 무엇인가요?

서브쿼리가 메인쿼리의 각 행에 대해 개별적으로 실행되며, 메인 쿼리의 컬럼 값을 사용해 서브쿼리의 조건을 평가하는 쿼리입니다. 결국 메인쿼리와 현재 처리중인 행이 상관관계가 있으면 메인쿼리의 결과에 따라 서브쿼리의 실행결과가 달라지게 되어 모든 행에 대해 서브쿼리를 실행해야합니다.

SELECT e.id, e.name, e.salary, e.department_id
FROM employees e
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e.department_id
)

서브쿼리의 특징은 무엇인가요?

• 조인 vs 서브쿼리
  • 대신 간단한 쿼리의 경우, 일반적으로 서브쿼리가 join보다 더 읽기 쉬운 쿼리가 됩니다.
  • 일반적으로 join은 서브쿼리보다 빠릅니다. 왜냐하면 대부분의 RDBMS는 join쿼리에 대해 더 나은 실행계획을 만들 수 있기 때문입니다.
• 집계 데이터로 필터링
  • 서브쿼리는 WHERE절에서 개별행과 전체 데이터의 평균을 비교하는 것과 같이 집계함수를 사용하는 경우 유용하게 사용할 수 있습니다.

참고자료

https://www.dbvis.com/thetable/the-complete-guide-to-sql-subqueries/

https://medium.com/@connect2yh/서브쿼리-써-말아-6301d250e98a