[SQL] 11장. 서브쿼리 사용하기

[손에 잡히는 10분 SQL - 벤 포터 지음, 박남혜 옮김] 책의 학습 후 정리자료입니다.

---

📍 서브쿼리 이해하기

• SELECT 문은 SQL 쿼리 중 하나이다.
• 지금까지 사용했던 모든 SELECT 문은 개별 데이터베이스 테이블에서 데이터를 가져오는 단일 명령문이었다.
• SQL 문을 이용하면 서브쿼리를 만들 수 있다.
• 서브쿼리는 쿼리 안에 있는 쿼리이다.

📒 쿼리

• 모든 SQL 명령문. 일반적으로 쿼리라는 용어는 보통 SELECT 문을 지칭할 때 사용한다.

---

📍 서브쿼리로 필터링하기

• 관계형 테이블에서 여러 테이블에 분리되어 저장된 정보를 1개의 SQL 문을 통해 가져오기 위해 서브쿼리를 사용한다.

🧷 서브쿼리를 활용한 예시

• Orders 테이블에는 주문번호, 고객 ID, 주문 날짜가 저장되어 있다.
• OrderItems 테이블에는 각 주문에 대한 상세정보가 저장되어 있다.
• Customers 테이블에는 실제 고객 정보가 저장되어 있다.

• 만약 RGAN01 제품을 구매한 고객의 목록을 출력해야 한다면 아래와 같은 순서로 쿼리를 진행해야 한다.
  • RGAN01 을 주문한 주문 번호를 가져온다.
  • 이전 단계에서 가져온 주문 번호로 고객 ID를 가져온다.
  • 이전 단계에서 가져온 고객 ID로 고객의 상세 정보를 가져온다.
• 위의 3단계를 별도의 쿼리로 수행할 수 있지만, 그렇게 하면 첫 SELECT 문에서 가져온 결과를 두 번째 SELECT 문의 WHERE 절에서 사용해야 하고, 두 번째 SELECT 문에서 가져온 결과를 세 번째 SELECT 문의 WHERE 절에서 각각 사용해야 한다.
• 이때 서브쿼리를 이용하면, 이 3개의 쿼리를 합쳐서 하나의 문장으로 만들 수 있다.

🧷 실제 동작 환경

1. RGAN01 을 주문한 주문 번호를 가져온다.

SELECT order_num
FROM OrderItems
WHERE prod_id = 'RGAN01';

2. 이전 단계에서 가져온 주문 번호로 고객 ID를 가져온다.

SELECT cust_id
FROM Orders
WHERE order_num IN (20007,20008);

3. (1) 과 (2)를 결합

SELECT cust_id
FROM Orders
WHERE order_num IN ( SELECT order_num
                     FROM OrderItems
                     WHERE prod_id = 'RGAN01')

4. 이전 단계에서 가져온 고객 ID로 고객의 상세 정보를 가져온다.

SELECT cust_name, cust_contact
FROM Customers
WHERE cust_id In (1000000004, 1000000005);

5. (2) 와 (3)을 결합

SELECT cust_name, cust_contact
FROM Customers
WHERE cust_id In (SELECT cust_id
                  FROM Orders
                  WHERE order_num IN ( SELECT order_num
                                       FROM OrderItems
                                       WHERE prod_id = 'RGAN01'))

• 예제에서 보는 것처럼 WHERE 절에 서브쿼리를 사용하면 매우 강력하고 유연한 SQL 문을 작성할 수 있다.
• 사용할 수 있는 서브쿼리의 수에는 제한이 없지만, 너무 많은 서브쿼리를 사용하면 성능이 저하될 수 있다는 점에 주의하자.

❗️ 하나의 열만 검색 가능

• 서브쿼리는 하나의 열만 검색할 수 있다. 여러 개의 열을 서브쿼리로 검색하면 에러가 발생한다.

---

📍 계산 필드로 서브쿼리 사용하기

• 서브쿼리를 사용하는 또 다른 방법은 계산 필드를 생성하는 것이다.

🧷 서브쿼리를 사용하기 위해 계산 필드를 생성하는 예시

• 주문 수량을 Customers 테이블에 있는 고객별로 출력하고자 한다.
• Orders 테이블에는 주문 정보, 고객 ID가 함께 저장되어 있다.
• 이 정보를 가져오려면 아래와 같은 순서대로 진행해야 한다.
  • Customers 테이블에서 고객 목록을 가져온다.
  • Orders 테이블에서 각각의 고객이 주문한 수를 센다.

🧷 실제 동작 환경

1. Customers 테이블에서 고객 목록을 가져온다.

SELECT cust_name
FROM Customers;

2. Orders 테이블에서 각각의 고객이 주문한 수를 센다.

SELECT cust_id, COUNT(*) as orders
FROM Orders
GROUP BY cust_id

3. (1)과 (2)를 결합한다.

SELECT cust_name,
       (SELECT COUNT(*)
        FROM Orders
        WHERE Orders.cust_id = Customers.cust_id) AS orders
FROM Customers
ORDER BY cust_name

• Orders.cust_id = Customers.cust_id 는 Orders 테이블에 있는 cust_id와 Customers 테이블의 cust_id 정보가 일치하는지 비교한다.

❗️ 완전한 열 이름(Fully Qualified Column Name)

• DBMS가 우리의 의도를 제대로 해석하지 못해 잘못된 결과를 가져올 수도 있고, 가끔은 열 이름이 중복되어 실제로 DBMS가 에러를 내는 경우도 있다.
• 예를 들어 WHERE 절이나 ORDER BY 절에서 지정한 열이 여러 테이블에 동시에 존재하는 경우 에러가 발생한다.
• SELECT 문에서 2개 이상의 테이블을 사용한다면 완전한 열 이름을 사용하는 것이 모호함을 피하는 좋은 방법이다.

💡 서브쿼리가 항상 최선의 선택은 아니다.

• 이 장의 예제 코드는 설명한 것처럼 동작하지만, 이런 식의 데이터 검색이 효율적인 것은 아니다.
• 조인(JOIN)을 이용하면 같은 문제를 다른 방식으로 해결할 수 있다.

---

📍 도전 과제

1. 서브쿼리를 사용하여 10 또는 그 이상의 가격으로 제품을 구매한 고객 목록을 반환한라. OrderItems 테이블에서 조건에 맞는 주문 번호(order_num)를 가져온 다음, Orders 테이블에서 주문 번호와 일치하는 주문에 대한 고객 ID(cust_id)를 검색한다.

SELECT cust_id
FROM Orders
WHERE order_num IN (SELECT order_num
                    FROM OrderItems
                    WHERE item_price >= 10)

2. BR01 제품이 주문된 날짜를 알아야 한다. 서브쿼리를 이용하여 OrderItems 테이블에서 prod_id 가 BR01 인 주문 항목을 확인한다. 그리고 Orders 테이블에서 각각의 고객 ID(cust_id)와 주문 날짜(order_date)를 가져온 다음 결과를 주문 날짜로 정렬하는 SQL 문을 작성하라.

SELECT cust_id, order_date
FROM Orders
WHERE order_num IN (SELECT order_num
                  FROM OrderItems
                  WHERE prod_id = 'BR01')
ORDER BY order_date;

3. 2번을 수정하여 prod_id 가 BR01을 구매한 모든 고객의 이메일 주소(Customers 테이블에서 cust_email)를 가져오자, 가장 안족에 있는 쿼리는 OrderItems에서 order_num 을 반환하고 중간에 있는 쿼리는 Customers 테이블에서 cust_id 를 반환하는 SELECT 문이 포함된다.

SELECT cust_email
FROM Customers
WHERE cust_id IN (SELECT cust_id
                  FROM Orders
                  WHERE order_num IN (SELECT order_num
                                      FROM OrderItems
                                      WHERE prod_id = 'BR01'))

4. 고객 ID 목록과 각각의 고객이 주문한 수량이 필요하다. 고객 ID(Orders 테이블에서 cust_id)와 서브쿼리를 사용하여 각각의 고객에 대한 총주문량을 total_ordered 로 가져오는 SQL 문을 작성하라. 그 결과를 가장 큰 수부터 적은 순서대로 정렬하라. 이전에는 주문 합계를 계산하기 위해 SUM()을 사용했다는 점을 기억하자.

SELECT cust_id,
      (SELECT SUM(quantity)
       FROM OrderItems
       WHERE Orders.order_num = Orderitems.order_num) AS total_ordered
FROM Orders
ORDER BY total_ordered DESC;

5. Products 테이블에서 모든 제품명(prod_name)과 quant_sold 를 가져오는 SQL 문을 작성하라. 여기서 quant_sold 는 판매된 항목의 총 수량이 담긴 계산 필드다(OrderItems 테이블에서 서브쿼리와 SUM(quantity)를 이용해 검색).

SELECT prod_name,
       (SELECT SUM(order_item) 
        FROM OrderItems
        WHERE (Products.prod_id = OrderItems.prod_id)) AS quant_sold
FROM Products;