서브쿼리(Sub Query)

서브쿼리는 알려지지 않은 기준을 이용한 검색을 위해 사용한다.
서브쿼리를 이용하면 다른 쿼리의 결과에 대한 메인쿼리의 일부로 수행하여 복잡한 쿼리를 구성할 수 있다. 구문은 간단하며 두 개의 SELECT 문을 포함한다.

 

SQL문에서 서브쿼리를 사용할 수 있는 절

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

 

조인과 서브쿼리 차이점

• 조인은 조인에 참여하는 모든 테이블이 대등한 관계에 있기 때문에 조인에 참여하는 모든 테이블의 칼럼을 어느 위치에서라도 자유롭게 사용할 수 있다.
  • 조직(1)과 사원(M) 테이블을 조인하면 결과는 사원 레벨(M)의 집합이 생성된다.
  • M:N 관계의 테이블을 조인하면 MN(= M * N) 레벨의 집합이 결과로서 생성된다.
  • 1:1 관계의 테이블을 조인하면 1=(1 * 1) 레벨의 집합이 생성된다.
  • 서브쿼리는 서브쿼리 레벨과는 상관없이 항상 메인쿼리 레벨로 결과 집합이 생성된다.

 

• 서브쿼리는 메인쿼리의 칼럼을 모두 사용할 수 있지만 메인쿼리는 서브쿼리의 칼럼을 사용할 수 없다.
  • 따라서 질의 결과에 서브쿼리 칼럼을 표시해야 한다면 조인 방식으로 변환하거나 함수, 스칼라 서브쿼리(Scalar Subquery) 등을 사용해야 한다.
  • 서브쿼리는 서브쿼리 레벨과는 상관없이 항상 메인쿼리 레벨로 결과 집합이 생성된다.

 

• SQL문에서 서브쿼리 방식을 사용해야 할 때 잘못 판단하여 조인 방식을 사용하는 경우가 있다.
  • 결과는 조직 레벨(1)이고 사원 테이블(M)에 체크해야 할 조건이 존재하는 경우
  • 이런 상황에서 SQL문을 작성할 대 조인을 사용한다면 결과 집합은 사원(M) 레벨이 될 것이다.
    • 이렇게 되면 원하는 결과가 아니기 대문에 DISTINCT를 추가해서 결과를 다시 조직(1) 레벨로 만들어야 한다.
  • 따라서 조인 방식이 아니라 서브쿼리 방식을 사용해야 한다.
    • 메이쿼리로 조직을 사용하고 서브쿼리로 사원 테이블을 사용하면 결과 집합은 조직 레벨이 되어 원하는 결과를 얻을 수 있다.

 

서브쿼리 사용 시 주의사항

• 서브쿼리를 괄호로 감싸서 사용한다.
• 서브쿼리는 단일 행(Single Row) 또는 복수 행(Multiple Row) 비교 연산자와 함께 사용 가능하다.
  • 단일 행 비교 연산자는 서브쿼리의 결과가 반드시 1건 이하여야 한다.
  • 복수 행 비교 연산자는 서크쿼리의 결과 건수와 상관 없다.
• 서브쿼리에서 ORDER BY를 사용하지 못한다.
  • ORDER BY 절은 전체 SELECT 절에서 오직 한 개만 올 수 있기 때문에 ORDER BY 절은 메인쿼리의 마지막 문자에 위치해야 한다.

 

서브쿼리 분류

 

단일 행(Single Row) 서브쿼리

-- 평균 등수보다 높은 학생들 조회
SELECT student, grade
FROM test_scores
WHERE grade > (SELECT AVG(grade) FROM test_scores);

• 서브쿼리의 실행 결과가 반드시 1건 이하인 서브쿼리를 의미한다.
• 단일 행 서브쿼리는 단일 행 비교 연산자와 함께 사용 가능하다.
• 단일 행 비교 연산자: =, <, <=, >, >=, <>

 

연관(Correlated) 서브쿼리

SELECT ename, sal, deptno
FROM emp m
WHERE sal > (SELECT AVG(s.sal)
			FROM emp s
            WHERE m.deptno = s.deptno);

• 메인 서브쿼리에서 emp m을 서브쿼리에 전달한다.
• 서브쿼리에서 emp m과 같은 부서인 emp s의 평균 급여를 계산하여 메인 쿼리에 전달한다.
• 메인쿼리에서 emp m의 급여와 서브쿼리에서 전달받은 급여를 비교한다.

 

연관 서브쿼리의 특징

• 메인쿼리의 컬럼이 서브쿼리에서 사용된 쿼리이다.
• 일반적으로 서브쿼리가 먼저 수행되지만, 연관 서브쿼리의 경우에는 메인 쿼리의 컬럼이 필요하기 때문에
  • 메인쿼리가 먼저 수행되고, 그 후에 서브쿼리가 수행된다.
  • 테이블의 별칭을 이용하여 메인 쿼리에서 서브쿼리로 정보를 전달한다.
  • 서브쿼리가 메인쿼리의 값을 이용하고, 그 후에 서브쿼리의 결과를 메인쿼리가 이용한다.
• 서브쿼리에서 메인쿼리의 컬럼과 서브쿼리의 컬럼 간 비교가 이루어진다.
  • 메인쿼리에서는 서브쿼리의 컬럼 사용이 불가하다.

 

다중 행(Multiple Row) 서브쿼리

-- 2023-01-03일에 렌탈한 영화 제목 조회
SELECT film_id, title -- 영화 제목 조회
FROM film
WHERE film_id IN
(SELECT film_id
FROM rental
WHeRE rental_date BETWEEN '2023-01-03' AND '2023-01-04');

• 서브쿼리의 실행 결과가 여러 건인 서브쿼리를 의미한다.
• 다중행 서브쿼리는 다중행 비교 연산자와 함께 사용된다.
• 다중행 비교 연산자: IN, ALL, ANY, SOME, EXISTS
  • IN (서브쿼리): 서브쿼리의 결과에 존재하는 임의의 값과 동일한 조건을 의미한다.
  • 비교연산자 ALL (서브쿼리): 서브쿼리의 결과에 존재하는 모든 값을 만족하는 조건을 의미한다.
    • 서브쿼리 결과의 최대값보다 커야 조건을 만족한다.
  • 비교연산자 ANY (서브쿼리): 서브쿼리의 결과에 존재하는 어느 하나의 값이라도 만족하는 조건을 의미한다.
    • 서브쿼리의 값들 중 어떤 값이라도 만족하면 되므로, 서브쿼리의 결과의 최솟값 보다 크면 조건을 만족한다 
  • EXISTS: 서브쿼리의 결과를 만족하는 값이 존재하는지 여부를 확인하는 조건을 의미한다.
    • 여러 건이더라도 1건만 찾으면 더 이상 검색하지 않고 메인쿼리의 결과를 반환한다.
    • 즉, EXISTS 연산자는 서브쿼리에 행이 있는지 테스트하는데 사용된다.

 

EXISTS 예시

SELECT player_name, height, back_no
FROM player
WHERE EXISTS (SELECT height
            FROM player
            WHERE back_no = 15);
            
-- 서브쿼리에 상수를 넣더라도 같은 결과를 반환한다.
SELECT player_name, height, back_no
FROM player
WHERE EXISTS (SELECT 1
            FROM player
            WHERE back_no = 15);

 

다중 행 다중 컬럼(Multi Column) 서브쿼리

SELECT team_id, player_name, position, back_no, height
FROM player
WHERE (team_id, height) IN (SELECT team_id, MIN(height)
							FROM   player
                            GROUP BY team_id)
ORDER BY team_id, player_name;

• 서브쿼리의 실행 결과로 여러 칼럼을 반환한다.
• 메인쿼리의 조건절에 여러 칼럼을 동시에 비교할 수 있다.
• 서브쿼리와 메인쿼리에서 비교하고자 하는 칼럼 개수와 칼럼의 위치가 동일해야 한다.
• 위 쿼리는 소속팀 별로 키가 가장 작은 사람들의 정보를 출력하는 쿼리이지만 꼭 팀별로 한 명씩 출력되는 것이 아니라 팀 별로 MIN(height)를 만족하는 모든 사람들이 출력되는 점을 주의해야 한다.

 

기타 서브쿼리

 

스칼라 서브쿼리

-- 고객마다 총구매액 조회
SELECT id, (SELECT SUM(cost_amount) AS sum_cost
			FROM orders o
            WHERE o.customer_id = c.id)
FROM customers c;

SELECT c.id, SUM(cost_amount) AS sum_cost
FROM customers c, orders c
WHERE c.id = o.customer_id
GROUP BY c.id;

하나의 값을 반환하는 서브쿼리로, 단일 행, 단일 칼럼이므로 하나의 값을 반환한다는 점에서 함수(Function)의 특성을 가진다. 공집합을 반환하는 경우 NULL로 대응되고, 컬럼이 올 수 있는 대부분의 곳(SELECT, WHERE, 함수 인자, ORDER BY, CASE, HAVING 절 등..)에서 사용 가능하다.

Scalar는 '한 번에 한 가지만 처리하는' 이라는 뜻을 가지고 있듯이 스칼라 서브쿼리에 의해 나오는 결과는 하나의 행이어야 한다. 만약 해당 스칼라 서브쿼리가 2개 이상의 레코드를 조회한다면 에러가 발생한다.

 

FROM 절에서 서브쿼리

-- 양파를 구매한 고객들을 서브쿼리를 이용해 조회
SELECT c.name, o.bought_at
FROM (SELECT customer_id, created_at AS bought_at
FROM orders
WHERE product_name = '양파') o, customers c
WHERE o.customer_id = c.id;

• FROM 절에서 사용되는 서브쿼리를 인라인 뷰(Inline View)라고 한다.
  • 서브쿼리의 결과가 실행 시 동적으로 생성된 테이블인 것처럼 사용할 수 있다.
  • 인라인 뷰는 SQL문이 실행될 때만 임시적으로 생성되는 동적인 뷰이기 때문에 데이터베이스에 해당 정보가 저장되지 않는다.
    • 그래서 일반적인 뷰를 정적 뷰(Static View)라 하고, 인라인 뷰를 동적 뷰(Dynamic View)라고도 한다.
  • 인라인 뷰는 테이블 명이 올 수 있는 곳에 사용할 수 있고, 이를 사용하는 것은 조인 방식을 사용하는 것과 같다.
    • 따라서 인라인 뷰의 칼럼은 SQL 문에서 자유롭게 참조할 수 있다.

 

참조

https://dataonair.or.kr/db-tech-reference/d-guide/sql/?mod=document&uid=349