SQL | MySQL | 서브쿼리 (Subquery)

 

 

00. 서브쿼리(Subquery)란?

 

 

✅ 서브쿼리란?

 

       👉 서브쿼리(Subquery)란 다른 쿼리 내부에 포함되어 있는 SELECT 문을 의미

       👉 서브쿼리를 포함하고 있는 쿼리를 외부쿼리(outer query)라고 함

       👉 서브쿼리는 내부쿼리(inner query) 라고도 함

       👉 서브쿼리는 반드시 괄호 안에 있어야 함

       👉 복잡한 JOIN이나 UNION을 대체할 수 있음

       👉 메인쿼리 안에서 SELECT, FROM, WHERE, HAVING, 또는 IN 절과 함께 사용

 

 

✅ 서브쿼리의 예

SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);

 

✅ 예제 테이블들

-- customers 테이블 생성
DROP TABLE customers;
CREATE TABLE customers(
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50),
  email VARCHAR(50),
  phone VARCHAR(20)
);

-- customers 테이블에 데이터 추가
INSERT INTO customers (name, email, phone) 
VALUES 
('Alice', 'alice@example.com', '123-4567'),
('Bob', 'bob@example.com', '234-5678'),
('Charlie', 'charlie@example.com', '345-6789'),
('David', 'david@example.com', '456-7890');

-- orders 테이블 생성
DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT,
  order_date DATE
);

-- orders 테이블에 데이터 추가
INSERT INTO orders (customer_id, order_date) VALUES 
(1, '2022-03-01'),
(1, '2022-03-03'),
(2, '2022-03-02'),
(3, '2022-03-01'),
(3, '2022-03-04'),
(3, '2022-03-05'),
(4, '2022-03-01'),
(4, '2022-03-03'),
(4, '2022-03-04');

 

customers

 

orders

 

 

 

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

 

 

✅ 스칼라 서브쿼리

 

      👉 SELECT 문 내부에 사용되며, 서브쿼리 결과는 스칼라임

      👉 서브쿼리의 결과가 단일 값(스칼라)인 경우에 사용 

      👉 특정 테이블에서 가장 높은값, 낮은 값, 또는 평균값을 가져오는 등의 작업을 할 때 사용 

SELECT column1, (SELECT MAX(column2) FROM table2) AS max_value FROM table1;

 

 

➡️ customers 테이블에서 가장 많은 주문을 한 고객의 이름과 주문수

SELECT name, 
       (SELECT COUNT(*) 
        FROM orders 
        WHERE orders.customer_id = customers.id) AS order_count 
FROM customers 
ORDER BY order_count DESC 
LIMIT 1;

 

 

 

 

02. 인라인 뷰 서브쿼리(Inline View Subquery)

 

 

✅ 인라인뷰 서브쿼리

 

      👉 테이블 형태의 결과를 반환

      👉 From 절 내부에 사용되며, 서브쿼리의 결과는 하나 이상의 컬럼을 반환

SELECT column1, column2 
FROM (SELECT column1, column2 FROM table1) AS subquery 
WHERE column1 > 10;

 

 

➡️ orders 테이블에서 가장 많은 주문을 한 고객의 이름과 주문 수를 찾기

SELECT customers.name, order_counts.order_count 
FROM customers 
JOIN (
  SELECT customer_id, COUNT(*) AS order_count 
  FROM orders 
  GROUP BY customer_id 
  ORDER BY order_count DESC 
  LIMIT 1
) AS order_counts 
ON customers.id = order_counts.customer_id;

 

 

 

 

 

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

 

 

✅ 상관 서브쿼리 

 

      👉 WHERE 절 내부에 사용

      👉 서브쿼리의 결과는 외부 쿼리의 컬럼을 기반으로 계산

SELECT column1 
FROM table1 
WHERE column2 > (SELECT AVG(column2) FROM table1 WHERE table1.column1 = column1);

 

 

➡️ orders 테이블에서 주문한 고객의 이름, 해당 고객의 총 주문 건수를 계산해 출력하는 상관 서브쿼리 

SELECT c.name, (
  SELECT COUNT(*)
  FROM orders o
  WHERE o.customer_id = c.id
) AS total_orders
FROM customers c;

 

 

 

04. EXISTS 서브쿼리

 

 

✅ EXISTS 서브쿼리 

 

      👉 WHERE OR HAVING 절 내부에 사용

      👉 서브쿼리의 결과가 존재하는지 여부에 따라 TRUE/FALSE 값을 반환

SELECT column1 
FROM table1 
WHERE EXISTS (SELECT column2 FROM table2 WHERE table1.column1 = table2.column2);

 

 

➡️ 3월 3일에 주문을 한 적이 있는 고객의 이름과 이메일 주소 

SELECT name, email
FROM customers c
WHERE EXISTS (
  SELECT *
  FROM orders o
  WHERE o.customer_id = c.id
  AND o.order_date ='2022-03-03'
);

 

 

➡️ 3월 3일에 주문을 한 적이 없는 고객의 이름과 이메일 주소 

SELECT name, email
FROM customers c
WHERE NOT EXISTS (
  SELECT *
  FROM orders o
  WHERE o.customer_id = c.id
  AND o.order_date ='2022-03-03'
);

 

 

 

 

05. 비교 연산자를 사용한 서브쿼리

 

 

✅ 비교 연산자를 사용한 서브쿼리 

 

      👉 WHERE 절 내부에 사용 

      👉 서브쿼리의 결과가 비교 연산자(> , < , = 등)와 함께 사용 

SELECT column1 
FROM table1 
WHERE column2 > ANY (SELECT column2 FROM table2 WHERE column3 = 'value');

 

 

➡️ ANY 연산자를 사용한 서브쿼리 

      : 서브쿼리의 결과 중 하나라도 조건을 만족하는 경우 조건식이 참이 됨.

      : 3월 3일과 3월 5일 사이에 주문한 고객의 정보

SELECT *
FROM customers
WHERE id = ANY (
  SELECT customer_id
  FROM orders
  WHERE order_date BETWEEN '2022-03-03' AND '2022-03-05'
);

 

 

 

➡️ ALL 연산자를 사용한 서브쿼리 

      : 서브쿼리의 모든 결과가 조건을 만족해야 전체 조건식이 참이 됨

      :  주문을 2건 이상하지 않은 고객 출력

SELECT *
FROM customers
WHERE id <> ALL (
  SELECT customer_id
  FROM orders
  GROUP BY  customer_id
  HAVING count(*) >=2
);

 

 

 

 

 

---

출처 및 참고 문헌(reference)

https://dev.mysql.com/doc/refman/8.0/en/scalar-subqueries.html

MySQL :: MySQL 8.0 Reference Manual :: 13.2.15.1 The Subquery as Scalar Operand