MySQL 총 정리10: 서브쿼리- 스칼라, 인라인 뷰, 중첩

 

---

 

[ 목차 ]
1. 스칼라 서브 쿼리
2. 인라인 뷰
3. 중첩 서브쿼리
• 비연관 서브쿼리
• 연관 서브쿼리

 

자, 위 그림에서 알 수 있듯이, 서브쿼리는 쿼리 안에 포함된 또 다른 쿼리로, 메인쿼리(가장 바깥쪽의 SELECT 문)를 보조하는 하위 쿼리를 의미한다. 서브쿼리를 구성하는 SELECT 문은 괄호로 둘러싸여 있으며, 다양한 위치에서 사용될 수 있다. 예를 들어:

• SELECT 절에서 특정 값을 계산하여 반환하거나,
• FROM 절에서 마치 테이블처럼 사용되어 서브쿼리의 결과를 임시 테이블로 취급하거나,
• WHERE 절에서 조건을 지정하는 데 사용될 수 있다.

 

쿼리의 결과, 즉 SELECT의 결과는 일반적으로 테이블이다. 따라서 테이블이 위치할 수 있는 곳에는 서브쿼리도 위치할 수 있다. 추가로 SQL에서는 편의성을 위해 예외적으로 컬럼이 위치하는 곳에도 서브쿼리를 사용할 수 있도록 허용하고 있는데, 이 때 서브쿼리는 단일 값을 반환해야 한다.

 

서브쿼리는 하나일 수도, 여러 개 일수도 있다.

 

 

서브 쿼리의 종류

▶위치에 따른 서브 쿼리 분류

종류	설명
스칼라 서브쿼리 (Scalar Subquery)	SELECT 절에 위치하며 메인 쿼리의 각 행당 정확히 하나의 값을 반환 (단일 행, 단일 열 반환)
인라인 뷰 (Inline View)	FROM 절에 위치하며 하나의 테이블처럼 사용된다. 뷰(View)처럼, 쿼리 내에서 임시적으로 생성된 결과를 테이블처럼 사용할 수 있게 해준다. 즉, 쿼리를 실행할 때만 존재하며 데이터베이스에 저장되지 않는다.
중첩 서브쿼리 (Nested Subquery)	WHERE 절이나 HAVING 절과 같은 조건절에서 쓰이는 서브쿼리

 

 

▶동작 방식에 따른 서브 쿼리 분류

종류	설명
연관 서브쿼리 (Correlated Subquery)	메인쿼리와 서브쿼리 간에 관계성 ⭕
비연관 서브쿼리 (Un-Correlated Subquery )	메인쿼리와 서브쿼리 간에 관계성 ❌

 

 

---

 

1. 스칼라 서브 쿼리(Scalar Subquery)

◈ 스칼라 서브쿼리는 SELECT 절에 위치하며 단일값(단일 행과 단일 열)을 반환하는 서브쿼리다.

1) 예시

작품의 정보가 담긴 paintings 테이블과 작품의 작가에 대한 정보가 담겨있는 artists 테이블이 있다.

각 작품의 번호, 이름, 가격과 그 작품에 대한 작가 정보를 가져오는 SELECT문을 서브쿼리를 사용해 작성해보자.

# 스칼라 서브쿼리
SELECT p.id, p.name, p.price, 
       (SELECT a.name 
        FROM artists a  
        WHERE a.id = p.artist_id) AS artist_name   -- 'AS 생략 가능'
FROM paintings p;

각 작품에 대해 행마다 해당 작품의 작가 이름이 정확히 하나씩 반환되었다. 이처럼 메인 쿼리의 각 행에 대해 하나의 단일 값을 반환하는 것이 바로 스칼라 서브쿼리다. 위의 결과를 보면 생각나는 한 연산자가 있을텐데 바로 OUTER JOIN일 것이다. 외부 테이블에서 관련 데이터를 가져오는 경우, OUTER JOIN을 사용하면 두 테이블을 결합해 동일한 정보를 얻을 수 있다. 다만, 스칼라 서브쿼리와 JOIN은 동작 방식이 다르니 사용 목적에 따라 적절한 방법을 선택해 주어야 한다.

# Left Outer Join
SELECT p.id, p.name, p.price, a.name
FROM paintings p
LEFT JOIN artists a
ON p.artist_id = a.id;  -- 같은 결과 반환

 

서브쿼리를 만약에 아래와 같이 코드를 작성하면 어떻게 될까?

SELECT a.id, a.name, 
       (SELECT p.name 
        FROM paintings p
        WHERE p.artist_id = a.id ) AS panting_name
FROM artists a;

작가 정보와 작가가 그린 그림을 조회하는 이 SELECT문은 각 작품에 대해 해당 작품의 작가 이름을 반환해야 하는데, 그림을 두 개 이상 그린 작가들(이지현, 정하나)이 존재하기 때문에 작동할 수 없는 코드다.

 

그렇다면 서브쿼리의 결과가 NULL일 수도 있는 경우, SELECT 문의 결과는 어떻게 될까?

확인을 위해 paintings 테이블에 artists 테이블에는 없는 작가가 그린 그림을 추가시켰다. 서브 쿼리의 결과는 어떻게 나올까?

반환같이 없는 경우엔, 쿼리 오류로 이어지지 않고 NULL 값이 반환된다.

 

 

2) 스칼라 서브쿼리의 실행 원리ㆍ순서

• 서브쿼리는 메인쿼리에서 출력되는 각 행에 대해 순차적으로 실행된다.
• 즉, 메인쿼리에서 출력되는 행의 수만큼 서브쿼리는 반복 실행되는 것이다.
  ( ➯ 만약 메인 쿼리의 조건에 맞는 데이터가 10행이라면, 서브쿼리도 10번 실행되어 각 행마다 값을 계산해줌)

SELECT 문이 위와 같을 때 실행 순서를 알아보자.

 

① FROM:

• 가장 먼저 paintings 테이블이 참조되어, 해당 테이블의 모든 행을 가져온다.

② WHERE: 

• paintings 테이블의 각 행이 WHERE 절 조건에 따라 필터링된다.

• 즉, price가 500,000보다 큰 행들만 유지되고 나머지 행들은 제외된다.

③ SELECT: 

• 필터링이 완료된 데이터의 각 행마다 스칼라 서브쿼리가 개별적으로 실행된다.

• 예를 들어, 첫 번째 필터링된 행인 '숲과 나'의 그림 정보에서 p.artist_id 값을 사용하여 서브쿼리가 실행된다.

    •  서브쿼리는 artists 테이블에서 a.id = p.artist_id 조건을 만족하는 행을 찾고, 해당 작가의 이름(a.name)을 반환

• 이어서 두 번째 행도 동일하게 그 행의 p.artist_id 값을 기준으로 서브쿼리가 실행되어 작가 이름을 가져오며 마지막 행까지 같은 방식으로 서브쿼리가 실행된다.

최종적으로 SELECT 절에서 지정한 id, name, price와 서브쿼리로 가져온 artist_name값들이 반환된다.

 

 

---

 

2. 인라인 뷰(Inline View)

◈ 인라인 뷰는 서브쿼리를 FROM 절에 작성하여 일시적인 테이블처럼 사용하는 것을 말한다. 인라인 뷰는 쿼리 내에서만 사용되고 별도로 저장되지 않으며, 메인 쿼리에서 마치 실제 테이블처럼 참조된다.

1) 예시

각 작가의 평균 수입보다 높은 가격인 그림들의 목록을 뽑아보자.

SELECT p.artist_id, p.name, p.price, p2.avg_price
FROM PAINTINGS p,
    (SELECT artist_id, AVG(price) AS avg_price
     FROM PAINTINGS
     GROUP BY artist_id) p2
WHERE p.artist_id = p2.artist_id
  AND p.price > p2.avg_price;

FROM 절에 있는 서브쿼리는 각 작가별로 그림의 평균 가격을 계산하며, 이 계산된 결과는 p2라는 별칭을 가진 임시 테이블의 역할을 한다. 이 임시 테이블(p2)에는 각 artist_id와 그에 해당하는 avg_price가 저장된다. 메인 쿼리는 paintings 테이블의 각 행을 가져오면서, 각 행의 p.price와 임시 테이블에서 가져온 평균 가격(avg_price)을 비교해, 해당 작가의 평균 가격보다 높은 가격을 가진 작품들만 필터링한다.

 

 

2) 인라인 뷰의 실행 원리ㆍ순서

① 인라인 뷰:

• 서브쿼리는 각 작가마다 평균 작품의 가격을 계산한다.

• 계산된 결과는 다중행, 다중컬럼을 가진 일종의 테이블이다. 이 임시 테이블의 별칭을 p2로 지정한다.

② FROM :

• FROM 절에는 paintings 테이블 p와 서브쿼리로부터 받은 임시 테이블 p2가 있다.

• 위 예시 코드와 같이 FROM절에 2개 상의 Table이 있으나 두 테이블 사이에 JOIN 조건이 존재하지 않다면, SQL은 두 테이블의 모든 가능한 조합을 생성한다. (이를 Cartesian Product이라고 함)

➯ paintings 테이블에 7개의 행, p2 테이블에 5개의 행이 존재하니 7 * 5 = 35개의 행의로 구성된 결과가 생성된다.

③ WHERE:

• p.artist_id = p2.artist_id 조건을 적용한다.

• 다음 조건인 p.price > p2.avg_price를 만족하는 행을 필터링한다.

④  SELECT: 

• 최종적으로 SELECT 절에서 선택한 행들만 반환된다.

 

 

---

 

Q. 만약에 JOIN을 명시해줬다면?

SELECT p.artist_id, p.name, p.price, p2.avg_price
FROM paintings p
INNER JOIN (
    SELECT artist_id, AVG(price) AS avg_price
    FROM paintings
    GROUP BY artist_id) p2 
ON p.artist_id = p2.artist_id
WHERE p.price > p2.avg_price;

➯ INNER JOIN 구문을 사용하면 ON 조건을 통해 두 테이블을 결합할 때 필요한 조건을 미리 적용한다.

➯ 그 ON 조건이 적용된 후에 JOIN이 발생하므로, 불필요한 조합이 생성되지 않는다.

 

 

Q. 스칼라 서브쿼리와의 차이

 

➯ 스칼라 서브쿼리는 단일 값을 반환해야 하므로 하나의 컬럼만 선택하며, 메인쿼리의 각 행마다 독립적으로 실행된다. 이 서브쿼리는 메인쿼리의 현재 처리 중인 행의 p.artist_id 값을 사용해 작동하며, 각 p.artist_id에 해당하는 a.name을 반환한다. 정리하자면, 각 행에 대해 스칼라 서브쿼리가 개별적으로 실행되며, 이 때 하나의 값을 반환해 메인쿼리에서 사용한다는 것이다.

➯ 반면, 인라인 뷰는 테이블 형태의 결과를 반환해야 하므로, 이 임시 테이블에는 메인쿼리에서 필요한 컬럼들이 포함되어야 한다. 인라인 뷰는 메인 쿼리가 실행되기 전에 먼저 실행되어 그 결과를 임시 테이블로 제공한다. 그 결과를 메인쿼리에서 조인하여 사용하게 되므로, JOIN 조건으로 사용할 컬럼들을 반드시 서브쿼리의 SELECT 절에 명시해야 한다. 예를 들어, 서브쿼리에서 만약 avg(price)만 SELECT 할 경우, 메인 쿼리가 받는 임시 테이블은 avg_price 컬럼 하나만 가지게 되어 artist_id와의 조인을 할 수 없으므로 에러가 발생하게 된다.

 

📝

※ 스칼라 서브쿼리는 독립적으로 실행할 경우 의미가 없거나 에러가 날 수 있다.

• 예를 들어, 메인 쿼리의 컬럼을 참조하는 스칼라 서브쿼리의 경우 단독으로 실행 시 참조할 메인 쿼리의 값이 없으니 에러가 발생한다.

※ 인라인 뷰는 하나의 결과 테이블을 반환하니 독립적으로 실행해도 결과가 출력된다.

 

 

---

 

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

◈ 중첩 서브쿼리는 WHERE 절이나 HAVING 절과 같은 조건절에서 사용되는 서브쿼리로, 메인 쿼리의 데이터를 필터링하거나 비교할 때 사용된다. 이는 메인 쿼리에서 특정 조건을 만족하는 데이터를 추출하는 데 도움을 준다.

1) 예시

paintings 테이블의 그림 중 가장 비싼 그림을 그린 작가의 이름을 찾아보자.

SELECT a.name AS artist_name
FROM artists a
WHERE a.id = (
    SELECT p.artist_id
    FROM paintings p
    WHERE p.price = (SELECT MAX(price) FROM paintings)
);

서브쿼리에서 가장 비싼 그림을 그린 작가의 ID가 반환되며, 메인쿼리는 artists 테이블에서 서브쿼리로부터 받은 artist_id와 일치하는 작가의 이름을 가져온다.

 

 

2) 중첩 서브쿼리의 실행 원리ㆍ순서

① 가장 안쪽의 서브쿼리:

• paintings 테이블에서 가장 비싼 그림의 가격을 찾는다.

• 결과: 1,350,000

 

② 중첩 서브쿼리:

• paintings 테이블에서 price가 1,350,000인 그림의 artist_id를 찾는다.

• 결과: 1

 

③ FROM :

• paintings 테이블이 참조되어, 해당 테이블의 모든 행을 가져온다.

 

④ WHERE :

• a.id = 1 조건에 따라, artist_id가 1인 작가만 필터링한다.

 

⑤ SELECT:

• id가 1인 작가의 name 컬럼 값을 반환한다.

• 결과: '이지현'

 

 

---

 

➯ 당연한 얘기지만, 서브쿼리가 '=' 연산자와 함께 사용된다면, 서브쿼리는 반드시 하나의 값을 반환해야 한다.

 

SELECT a.name
FROM artists a
WHERE a.id IN (SELECT p.artist_id FROM paintings p WHERE p.price > 500000);

-- 이렇게도 가능
SELECT a.name
FROM artists a
WHERE EXISTS (SELECT 1 FROM paintings p WHERE p.artist_id = a.id AND p.price > 500000);

➯ IN이라던다 다른 연산자가 사용된다면, 여러 행들이 반환되어도 된다.

 

 

---

 

◈ 중첩 서브쿼리는 메인쿼리와 관계가 있는지에 따라 상관 서브쿼리와 비상관 서브쿼리로 나뉜다.

 

비연관 서브쿼리(Un-Correlated subquery)

• 서브쿼리가 메인쿼리와 상관없이 독립적으로 실행되는 서브쿼리다.

• 서브쿼리는 메인쿼리와 관계없이 한 번만 실행되며, 그 결과를 메인쿼리에 제공한다.

• 즉, 비연관 서브쿼리는 메인쿼리보다 먼저 실행되어야 한다.

 

연관 서브쿼리(Correlated subquery)

• 서브쿼리가 메인쿼리의 각 행에 의존하여 실행된다.

• 즉, 서브쿼리는 메인쿼리에서 참조된 값을 사용해 메인쿼리의 각 행에 대해 반복적으로 실행된다.

 

 

---

 

📒[정리]

스칼라 서브쿼리

•  SELECT 절에 위치하는 서브쿼리로 메인쿼리의 각 행에 대해 단일값을 반환한다.
• 출력되는 하나의 값이 없다면 NULL을 반환한다.
• 메인쿼리의 각 행에 대해 서브쿼리가 실행된다.

 

인라인 뷰

•  FROM 절에 위치하는 서브쿼리로 임시 테이블을 반환한다.
• 반환한 테이블을 메인쿼리에 제공한다.

 

중첩 서브쿼리

•  WHERE 절, HAVING 절 등 조건절에서 사용되는 서브쿼리다.
• 메인 쿼리의 조건을 결정하기 위해 서브쿼리의 결과를 비교한다.
• 서브쿼리는 메인쿼리의 조건을 평가하는 데 사용되며,
• 서브쿼리의 결과는 단일 값, 다중 값, 혹은 테이블이 될 수 있다.

• 비연관 서브쿼리일 경우, 메인쿼리보다 먼저 실행된다.
• 연관 서브쿼리는 메인쿼리의 각 행에 대해 반복적으로 실행된다.

 

---