목차
- 서브 쿼리 사용 예시
- 유형에 따른 분류
- 자주 발생하는 실수
서브 쿼리 사용 예시
서브 쿼리는 내부 쿼리의 결과를 기반으로 데이터를 필터링, 검색 또는 조작하는 데 자주 사용되는 다른 쿼리 내에 포함된 쿼리입니다. 서브 쿼리는 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++처럼 각자의 스타일로 녹여냈다는 걸 알게되고 있습니다. 참 어렵네요.
