[SQL] MySQL 서브 쿼리(SUBQUERY)란?

목차

1. 서브 쿼리 사용 예시
2. 유형에 따른 분류
3. 자주 발생하는 실수

---

서브 쿼리 사용 예시

서브 쿼리는 내부 쿼리의 결과를 기반으로 데이터를 필터링, 검색 또는 조작하는 데 자주 사용되는 다른 쿼리 내에 포함된 쿼리입니다. 서브 쿼리는 SELECT, FROM, WHERE 및 HAVING 절과 같은 SQL 쿼리의 다양한 부분에서 사용할 수 있습니다. 다음은 각 절에서 서브 쿼리를 사용하는 방법에 대한 몇 가지 예입니다.

1. SELECT의 서브 쿼리
SELECT 절의 서브 쿼리를 사용하면 서브 쿼리 결과를 기본 쿼리 결과의 열로 포함할 수 있습니다.

SELECT first_name, last_name,
       (SELECT COUNT(*) FROM orders WHERE orders.customer_id = customers.customer_id)
       AS order_count
FROM customers;

이 쿼리는 고객 테이블에서 first_name 및 last_name 열을 검색하고 각 고객이 주문한 수를 포함하는 열을 추가합니다.

---

2. FROM의 서브 쿼리
FROM 절의 서브 쿼리를 사용하면 기본 쿼리에서 사용할 임시 테이블 또는 보기를 만들 수 있습니다.

SELECT emp.first_name, emp.last_name, emp.salary
FROM (SELECT * FROM employees WHERE salary > 50000) AS emp
ORDER BY emp.salary DESC;

이 쿼리는 급여가 50,000보다 큰 직원을 포함하는 'emp'라는 임시 테이블에서 first_name, last_name 및 급여 열을 검색합니다.

---

3. WHERE의 서브 쿼리
WHERE 절의 서브 쿼리를 사용하면 서브 쿼리의 결과를 기반으로 기본 쿼리의 결과를 필터링할 수 있습니다.

SELECT first_name, last_name, salary
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'New York');

이 쿼리는 뉴욕에 위치한 부서에서 근무하는 직원의 이름, 성, 급여 열을 검색합니다.

---

4. HAVING의 서브 쿼리
HAVING 절의 서브 쿼리를 사용하면 서브 쿼리 결과를 기반으로 GROUP BY 쿼리 결과를 필터링할 수 있습니다.

SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id
HAVING COUNT(*) > (SELECT AVG(employee_count) FROM
                  (SELECT department_id, COUNT(*) AS employee_count
                   FROM employees
                   GROUP BY department_id) AS dept_counts);

이 쿼리는 부서_id 및 각 부서의 직원 수를 검색하지만 직원 수가 부서당 평균 직원 수보다 많은 부서만 표시합니다.

---

5. GROUP BY의 서브 쿼리
일반적으로 GROUP BY 절에서 직접 서브 쿼리를 사용하지 않습니다. 그러나 SELECT, FROM 또는 HAVING 절에서 서브 쿼리를 사용하여 기본 쿼리의 그룹화에 영향을 줄 수 있습니다.

SELECT d.department_name, COUNT(e.employee_id) AS employee_count
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
WHERE e.salary > (SELECT AVG(salary) FROM employees)
GROUP BY d.department_name;

이 쿼리는 부서_이름과 평균 급여보다 높은 급여를 받는 직원 수를 부서_이름별로 그룹화하여 검색합니다.

---

6. ORDER BY의 서브 쿼리
ORDER BY 절에서 서브 쿼리를 사용하여 서브 쿼리의 결과를 기반으로 기본 쿼리의 결과를 정렬할 수 있습니다.

SELECT customer_id, first_name, last_name
FROM customers
ORDER BY (SELECT SUM(amount) FROM orders WHERE orders.customer_id = customers.customer_id) DESC;

 

이 쿼리는 고객 테이블에서 customer_id, first_name 및 last_name 열을 검색하여 결과를 각 고객이 소비한 총 금액을 기준으로 내림차순으로 정렬합니다.

---

7. INSERT의 서브 쿼리
INSERT 문에서 서브 쿼리를 사용하여 다른 테이블의 데이터 또는 쿼리 결과를 새 테이블에 삽입할 수 있습니다.

INSERT INTO high_salary_employees (employee_id, first_name, last_name, salary)
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

이 쿼리는 평균 급여보다 높은 급여를 받는 직원의 employee_id, first_name, last_name 및 급여 열을 'high_salary_employees'라는 새 테이블에 삽입합니다.

---

8. UPDATE의 서브 쿼리
UPDATE 문에서 서브 쿼리를 사용하여 쿼리 결과에 따라 테이블을 업데이트할 수 있습니다.

UPDATE employees
SET salary = salary * 1.1
WHERE employee_id IN (SELECT employee_id
                      FROM employees
                      WHERE department_id = (SELECT department_id
                                             FROM departments
                                             WHERE department_name = 'IT'));

이 쿼리는 'IT' 부서 직원의 급여를 10% 인상하여 업데이트합니다. 부질의는 'IT' 부서의 department_id를 찾는 데 사용되며 또 다른 부질의는 해당 부서 직원의 employee_id를 찾는 데 사용됩니다.

이러한 예제는 다양한 SQL 문에서 서브 쿼리를 사용하여 보다 복잡하고 유연한 쿼리를 만드는 방법을 보여줍니다.

---

유형에 따른 분류

서브 쿼리는 반환하는 값의 유형에 따라 분류할 수 있습니다. 반환 값에 따라 세 가지 기본 유형의 서브 쿼리가 있습니다.

1. 스칼라(Scalar) 서브 쿼리:
스칼라 서브 쿼리는 단일 값(하나의 행과 하나의 열)을 반환합니다. SELECT, WHERE 및 HAVING 절과 같이 단일 값을 반환하는 표현식이 허용되는 모든 곳에서 사용할 수 있습니다.

SELECT first_name, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

이 예에서 서브 쿼리는 모든 직원의 평균 급여(단일 값)를 반환하고 기본 쿼리는 평균보다 급여가 높은 직원을 검색합니다.

---

2. 행(Row) 서브 쿼리
행 서브 쿼리는 여러 열이 있는 단일 행을 반환합니다. =, <>, >, <, >= 및 <=와 같이 전체 행을 비교하는 비교 연산자에 사용할 수 있습니다.

SELECT first_name, last_name, salary
FROM employees
WHERE (employee_id, salary) = (SELECT employee_id, MAX(salary) FROM employees);

이 쿼리는 급여가 가장 높은 직원을 검색합니다. 서브 쿼리는 employee_id 및 최대 급여가 포함된 단일 행을 반환한 다음 기본 쿼리의 각 행과 비교합니다.

---

3. 테이블(Table) 서브 쿼리
테이블 서브 쿼리는 테이블(여러 행 및 여러 열)을 반환합니다. FROM, WHERE 및 HAVING 절은 물론 IN, EXISTS 및 NOT EXISTS 연산자와 함께 사용할 수 있습니다.

SELECT first_name, last_name, salary
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'New York');

이 쿼리는 뉴욕에 위치한 부서에서 근무하는 직원을 검색합니다. 서브 쿼리는 뉴욕의 부서에 대한 부서_id가 포함된 테이블을 반환하며 이는 기본 쿼리의 WHERE 절에서 사용됩니다.

---

자주 발생하는 실수

1. 서브쿼리 중첩이 올바르지 않은 경우

-- 올바른 구문
SELECT * FROM orders WHERE order_id IN (SELECT order_id FROM order_items WHERE product_id = 100);

-- 잘못된 구문 (괄호가 누락됨)
SELECT * FROM orders WHERE order_id IN SELECT order_id FROM order_items WHERE product_id = 100);

 

2. 상관 서브쿼리 사용이 잘못된 경우

-- 비효율적인 상관 서브쿼리
SELECT o.* FROM orders o WHERE EXISTS (SELECT 1 FROM order_items oi WHERE o.order_id = oi.order_id AND oi.product_id = 100);

-- 더 효율적인 JOIN
SELECT o.* FROM orders o JOIN order_items oi ON o.order_id = oi.order_id WHERE oi.product_id = 100;

3. EXISTS 또는 NOT EXISTS를 적절하게 사용하지 않는 경우

-- 비효율적인 방법
SELECT * FROM orders WHERE (SELECT COUNT(*) FROM order_items WHERE order_id = orders.order_id) > 0;

-- 더 효율적인 방법
SELECT * FROM orders WHERE EXISTS (SELECT 1 FROM order_items WHERE order_id = orders.order_id);

4. 집계 및 비집계 식을 혼합하여 사용하는 경우

-- 잘못된 방법
SELECT product_id, COUNT(order_id), order_id FROM order_items GROUP BY product_id;

-- 올바른 방법
SELECT product_id, COUNT(order_id) FROM order_items GROUP BY product_id;

5. 서브쿼리가 단일 행을 예상하는데 여러 행이 반환되는 경우

-- 잘못된 방법
SELECT * FROM orders WHERE order_id = (SELECT order_id FROM order_items WHERE product_id = 100);

-- 올바른 방법
SELECT * FROM orders WHERE order_id IN (SELECT order_id FROM order_items WHERE product_id = 100);

6. 조인이 더 적절한 경우에 서브쿼리를 사용하는 경우

-- 비효율적인 서브쿼리
SELECT * FROM orders WHERE order_id IN (SELECT order_id FROM order_items WHERE product_id = 100);

-- 더 효율적인 JOIN
SELECT o.* FROM orders o JOIN order_items oi ON o.order_id = oi.order_id WHERE oi.product_id = 100;

7. 모호한 열 참조

-- 잘못된 방법
SELECT * FROM orders o, order_items oi WHERE o.order_id = oi.order_id AND product_id = 100;

-- 올바른 방법 (가독성을 위해 테이블 별칭 사용)
SELECT * FROM orders o, order_items oi WHERE o.order_id = oi.order_id AND oi.product_id = 100;

8. 서로 다른 DBMS 간의 호환성 문제

-- PostgreSQL-specific 구문 (LIMIT)
SELECT * FROM orders LIMIT 10;

-- SQL Server-specific 구문 (TOP)
SELECT TOP 10 * FROM orders;

---

조사하면서 느끼는 것은 SQL 들어간 것들은 다 MySQL인줄 알았는데 SQL sever가 따로 있고, PostgreSQL도 다른거고.. 결국 SQL이 Structured Query Language의 앞자를 딴 것처럼 결국 하나의 언어고 C와 C+, C++처럼 각자의 스타일로 녹여냈다는 걸 알게되고 있습니다. 참 어렵네요.