Subquery (서브쿼리) - MySQL

Subquery (서브쿼리) - 한 쿼리 내에 포함된 또 하나의 쿼리

 

- 왜 사용하는 가?

• 복잡한 문제를 잘게 나누고, 중간 결과를 활용하기 위해
• 더 읽기 쉽고 간결하게 표현하기 위해

 

- 서브쿼리의 종류

 

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

• 단 하나의 값(스칼라 값)을 반환하는 서브쿼리
• 보통 SELECT, WHERE, HAVING, SET, CASE 등의 절에서 사용됨
• 주로 특정 기준값(평균, 최대, 최소 등)에 따라 비교할 때 사용

- 예시

-- 평균 가격보다 높은 상품 찾기
SELECT product_name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products)

 

 

2. 인라인 뷰 (Inline View) - FROM절 서브쿼리

• FROM절 안에서 서브쿼리를 마치 테이블처럼 사용하는 방식
• 집계된 결과나 정제된 데이터셋을 기반으로 다시 쿼리할 수 있음
• 중간 가공이 필요한 경우, 정제된 결과를 기준으로 다시 필터링해야 할 때 사용

- 예시

-- 사용자별 주문 수 계산 후, 3건 이상인 사람만 조회
SELECT user_id, order_count
FROM (
	SELECT user_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY user_id
    ) AS sub
WHERE order_count >= 3

 

3. 상관 서브쿼리 (Correlated Subquery)

• 서브쿼리 내부에서 외부 쿼리의 값을 참조
• 서브쿼리는 외부 쿼리의 각 행마다 한 번씩 실행됨 -> 느릴 수 있음
• 각 행마다 조건이 다른 경우에 사용함. 예를 들어 같은 반의 평균 점수보다 높은 학생을 찾기가 있음

- 예시

-- 같은 반의 평균 점수보다 높은 학생 찾기
SELECT student_id, class, score
FROM students t1
WHERE score > (
	SELECT AVG(score)
    FROM students t2
    WHERE t1.class = t2.class
)

 

 

 

---

 

- 실전 문제

 

이제 GPT를 활용해서 실전 문제를 만들고, 채점과 피드백을 요청했다.

 

🔸 문제 1.

CREATE TABLE orders (
  order_id INT,
  user_id INT,
  amount INT
);

INSERT INTO orders VALUES
(1, 101, 200),
(2, 102, 500),
(3, 103, 150),
(4, 104, 300),
(5, 105, 400);

 

 

평균보다 주문 금액이 높은 주문만 출력하라

• 사용 테이블: orders
• 출력 컬럼: order_id, user_id, amount
• 조건: 전체 주문의 평균 금액보다 큰 주문만

SELECT
order_id, user_id, amount
FROM orders
WHERE amount > (
	SELECT AVG(amount) avg_amount FROM orders
  )

 

🔸 문제 2.

CREATE TABLE employees (
  emp_id INT,
  name VARCHAR(50),
  department VARCHAR(50),
  salary INT
);

INSERT INTO employees VALUES
(1, 'Alice', 'HR', 5000),
(2, 'Bob', 'HR', 6000),
(3, 'Carol', 'Engineering', 8000),
(4, 'Dave', 'Engineering', 7500),
(5, 'Eve', 'Engineering', 8000);

 

 

자신이 속한 부서에서 연봉이 가장 높은 사원을 출력하라

• 사용 테이블: employees
• 출력 컬럼: emp_id, name, department, salary
• 조건: 각 부서별 최고 연봉자만 출력 (동점 있으면 모두 출력)

SELECT 
	emp_id, name, department, salary
FROM employees e1
WHERE salary = (
  SELECT 
  	MAX(salary) AS maxsalary
  FROM employees e2
  WHERE e1.department = e2.department
    )

 

🔸 문제 3.

CREATE TABLE orders (
  order_id INT,
  user_id INT,
  amount INT
);

INSERT INTO orders VALUES
(1, 101, 200),
(2, 101, 300),
(3, 101, 250),
(4, 102, 400),
(5, 103, 100),
(6, 103, 150),
(7, 104, 500);

 

주문을 3번 이상 한 사용자 목록을 출력하라

• 사용 테이블: orders
• 출력 컬럼: user_id, order_count
• 조건: 주문 횟수가 3 이상인 사용자만

SELECT
	user_id,
    order_count
FROM (
  SELECT
   user_id,
   COUNT(*) as order_count
  FROM orders
  GROUP BY user_id
  ) AS ord
WHERE order_count >= 3

 

🔸 문제 4.

CREATE TABLE orders (
  order_id INT,
  user_id INT,
  order_date DATE,
  amount INT
);

INSERT INTO orders VALUES
(1, 101, '2023-01-10', 200),
(2, 101, '2023-03-15', 300),
(3, 102, '2023-01-20', 400),
(4, 102, '2023-04-01', 450),
(5, 103, '2023-02-05', 500);

 

 

각 유저가 가장 최근에 주문한 내역만 출력하라

• 사용 테이블: orders
• 출력 컬럼: user_id, order_id, order_date, amount
• 조건: 유저별로 가장 최근 주문 1건만 출력

SELECT
	user_id,
    order_id,
    order_date,
    amount
FROM orders o1
WHERE order_date = (
    SELECT 
      MAX(order_date)
    FROM orders o2
    WHERE o1.user_id = o2.user_id
  	LIMIT 1
  )

 

🔸 문제 5.

CREATE TABLE orders (
  order_id INT,
  product_id INT,
  amount INT
);

INSERT INTO orders VALUES
(1, 1, 100),
(2, 1, 200),
(3, 1, 300),
(4, 2, 150),
(5, 2, 100),
(6, 2, 200);

 

 

각 상품별 평균보다 더 비싼 주문을 출력하라

• 사용 테이블: orders
• 출력 컬럼: order_id, product_id, amount
• 조건: 동일한 상품(product_id)에서 평균 주문 금액보다 비싼 주문만

SELECT
	order_id,
    product_id,
    amount
FROM orders o1
WHERE amount > (
  SELECT
  	AVG(amount) AS avg_mount
  FROM orders o2
  WHERE o1.product_id = o2.product_id
  )

 

 

 

4번의 경우 최근에 한 주문이 2건인 경우를 생각해서 혹시나 하는 마음에 LIMIT1 을 추가했으나, MAX( )함수는 원래 스칼라 값을 반환하는 함수이기 때문에 1건의 행만 반환해 의미가 없는 코드였다. GPT한테 최근 주문이 2건인 경우면 어떻게 하냐고 물어봤는데 윈도우 함수를 사용해야 한다고 한다. 그래서 다음에는 윈도우 함수를 배워보려고 한다.