복수의 서브쿼리를 결합한 SQL 쿼리 작성법

SQL의 서브쿼리는 쿼리 내에서 다른 쿼리를 실행하는 강력한 방법입니다. 특히 복수의 서브쿼리를 효과적으로 결합하면 복잡한 데이터 추출 및 분석이 가능합니다. 이 기사에서는 서브쿼리의 기본 개념부터 시작하여, 상관 서브쿼리와 복수 서브쿼리의 결합 방법, 그리고 실용적인 예제를 통해 서브쿼리를 활용한 SQL 쿼리 작성법을 자세히 설명합니다. 마지막으로 성능을 최적화하기 위한 팁도 제공합니다. SQL 스킬을 향상시키고 싶은 분들은 꼭 읽어보세요.

서브쿼리의 기본

서브쿼리는 SQL 문 내에 포함된 다른 SQL 문입니다. 서브쿼리는 부모 쿼리에 데이터를 제공하는 데 사용되며, 그 결과는 부모 쿼리 내에서 활용됩니다. 다음은 기본적인 서브쿼리의 예입니다.

기본 구조

기본적인 서브쿼리는 SELECT 문에서 사용되며, 괄호로 둘러싸입니다. 예를 들어, 가장 높은 급여를 가진 직원을 찾기 위한 서브쿼리는 다음과 같습니다.

SELECT employee_name
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

서브쿼리의 종류

서브쿼리에는 다음과 같은 종류가 있습니다:

• 단일 행 서브쿼리: 1행의 결과를 반환하는 서브쿼리.
• 복수 행 서브쿼리: 복수 행의 결과를 반환하는 서브쿼리.
• 상관 서브쿼리: 부모 쿼리의 각 행에 의존하는 서브쿼리.

서브쿼리의 기본을 이해하는 것은 더 복잡한 쿼리를 작성하기 위한 첫걸음입니다. 다음으로 상관 서브쿼리에 대해 자세히 설명하겠습니다.

상관 서브쿼리

상관 서브쿼리는 부모 쿼리의 각 행에 대해 실행되는 서브쿼리로, 부모 쿼리의 열을 참조합니다. 이를 통해 더 동적이고 유연한 데이터 추출이 가능합니다.

상관 서브쿼리의 개념

상관 서브쿼리는 부모 쿼리의 각 행에 의존하며, 부모 쿼리와 서브쿼리가 상호 관련되어 있습니다. 이를 통해 복잡한 데이터 비교나 집계가 가능합니다.

상관 서브쿼리의 기본 구조

상관 서브쿼리의 기본 구조는 다음과 같습니다. 다음 예제에서는 각 직원의 급여가 부서 내 평균 급여보다 높은 직원을 추출하고 있습니다.

SELECT employee_name, salary
FROM employees e1
WHERE salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e1.department_id = e2.department_id
);

이 쿼리에서는 바깥쪽 쿼리(부모 쿼리)와 안쪽 쿼리(서브쿼리)가 department_id로 연결되어 있으며, 각 직원의 급여가 해당 부서의 평균 급여를 초과하는지 확인합니다.

상관 서브쿼리의 이점

상관 서브쿼리는 다음과 같은 이점이 있습니다:

• 유연성: 복잡한 조건을 설정할 수 있으며, 부모 쿼리의 각 행에 대해 서로 다른 계산이나 비교가 가능합니다.
• 동적인 데이터 추출: 부모 쿼리의 데이터에 따라 동적으로 결과를 생성합니다.

다음으로, 복수의 서브쿼리를 결합하여 복잡한 데이터 추출을 수행하는 방법을 소개합니다.

복수의 서브쿼리를 결합하는 방법

복수의 서브쿼리를 결합하면 매우 복잡하고 세부적인 데이터 추출 및 분석이 가능합니다. 여기서는 복수의 서브쿼리를 효과적으로 사용하는 방법을 설명합니다.

중첩된 서브쿼리

서브쿼리를 또 다른 서브쿼리 내에 중첩시켜 계층적인 데이터 추출을 할 수 있습니다. 다음 예제에서는 가장 높은 급여를 가진 직원의 부서명을 가져옵니다.

SELECT department_name
FROM departments
WHERE department_id = (
    SELECT department_id
    FROM employees
    WHERE salary = (
        SELECT MAX(salary)
        FROM employees
    )
);

이 쿼리는 가장 높은 급여를 가진 직원을 찾고, 그 직원이 속한 부서의 이름을 가져옵니다.

상관 서브쿼리와의 결합

상관 서브쿼리와 다른 서브쿼리를 결합하는 것도 가능합니다. 다음 예제에서는 각 직원의 급여가 부서의 평균 급여보다 높은지 확인하고, 그 부서의 이름을 가져옵니다.

SELECT employee_name, department_name
FROM employees e1
JOIN departments d ON e1.department_id = d.department_id
WHERE salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e1.department_id = e2.department_id
);

이 쿼리에서는 상관 서브쿼리를 사용하여 각 직원의 급여가 그 부서의 평균 급여를 초과하는지 확인하고, 그 결과를 바탕으로 부서 이름을 가져옵니다.

복수의 WITH절을 사용한 서브쿼리

복수의 서브쿼리를 사용할 때, WITH절(공통 테이블 표현식, CTE)을 사용하면 쿼리를 읽기 쉽게 하고 유지보수를 용이하게 할 수 있습니다. 다음은 CTE를 사용한 예제입니다.

WITH MaxSalary AS (
    SELECT department_id, MAX(salary) AS max_salary
    FROM employees
    GROUP BY department_id
),
HighEarners AS (
    SELECT e.employee_name, d.department_name, e.salary
    FROM employees e
    JOIN departments d ON e.department_id = d.department_id
    JOIN MaxSalary m ON e.department_id = m.department_id AND e.salary = m.max_salary
)
SELECT * FROM HighEarners;

이 쿼리는 먼저 각 부서의 최고 급여를 가진 직원을 얻고, 그 후 그 직원의 이름과 부서 이름을 선택하고 있습니다.

다음으로 실용적인 예제로서, 중첩된 서브쿼리의 실제 사례를 보여줍니다.

실용예1: 중첩된 서브쿼리

중첩된 서브쿼리는 계층적인 데이터 추출을 위해 사용됩니다. 이 섹션에서는 실용적인 예를 통해 중첩된 서브쿼리의 사용 방법을 설명합니다.

예: 특정 부서의 가장 높은 급여를 가진 직원 추출

이 예제에서는 특정 부서(예: 부서ID가 5)의 가장 높은 급여를 가진 직원을 추출합니다.

SELECT employee_name, salary
FROM employees
WHERE salary = (
    SELECT MAX(salary)
    FROM employees
    WHERE department_id = 5
);

이 쿼리는 먼저 내부의 서브쿼리가 부서ID가 5인 직원 중 가장 높은 급여를 찾아내고, 그 결과를 바깥쪽 쿼리가 사용하여 해당 직원을 추출합니다.

예: 각 부서의 가장 높은 급여를 가진 직원 추출

더 복잡한 예제로 각 부서의 가장 높은 급여를 가진 직원을 추출하는 방법을 보여줍니다.

SELECT employee_name, department_id, salary
FROM employees e1
WHERE salary = (
    SELECT MAX(salary)
    FROM employees e2
    WHERE e1.department_id = e2.department_id
);

이 쿼리에서는 상관 서브쿼리를 사용하여 각 부서의 가장 높은 급여를 가진 직원을 찾고 있습니다. 내부의 서브쿼리는 바깥쪽 쿼리에서 전달된 department_id에 기반하여 최대 급여를 가져오고, 그 결과를 사용하여 바깥쪽 쿼리가 해당 직원을 선택합니다.

예: 각 부서의 가장 높은 급여를 가진 직원의 상세 정보 추출

마지막으로, 각 부서의 가장 높은 급여를 가진 직원의 상세 정보(이름, 급여, 부서명)를 추출하는 방법을 보여줍니다.

SELECT e1.employee_name, e1.salary, d.department_name
FROM employees e1
JOIN departments d ON e1.department_id = d.department_id
WHERE e1.salary = (
    SELECT MAX(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e1.department_id
);

이 쿼리는 먼저 각 부서의 최대 급여를 가진 직원을 찾고, 그 후 해당 직원의 상세 정보를 부서명과 함께 추출합니다.

다음으로, WITH절을 사용하여 복잡한 서브쿼리를 정리하는 방법에 대해 설명합니다.

실용예2: WITH절을 사용한 서브쿼리

WITH절(공통 테이블 표현식, CTE)을 사용하면 복잡한 쿼리를 이해하기 쉽게 정리할 수 있습니다. 이 섹션에서는 WITH절을 사용하여 서브쿼리를 간결하게 정리하는 방법을 실용적인 예제와 함께 소개합니다.

예: 각 부서의 가장 높은 급여를 가진 직원 추출

먼저, 각 부서의 가장 높은 급여를 가진 직원을 추출하는 방법을 WITH절을 사용하여 보여줍니다.

WITH MaxSalaries AS (
    SELECT department_id, MAX(salary) AS max_salary
    FROM employees
    GROUP BY department_id
)
SELECT e.employee_name, e.salary, d.department_name
FROM employees e
JOIN MaxSalaries m ON e.department_id = m.department_id AND e.salary = m.max_salary
JOIN departments d ON e.department_id = d.department_id;

이 쿼리에서는 MaxSalaries라는 CTE를 사용하여 각 부서의 최대 급여를 계산하고, 이를 메인 쿼리에서 사용합니다. 이를 통해 전체 쿼리가 더 읽기 쉬워집니다.

예: 매출이 가장 높은 판매원 추출

다음으로, 매출이 가장 많은 판매원의 상세 정보를 추출하는 예를 보여줍니다.

WITH SalesData AS (
    SELECT salesperson_id, SUM(sales_amount) AS total_sales
    FROM sales
    GROUP BY salesperson_id
),
TopSalesperson AS (
    SELECT salesperson_id, MAX(total_sales) AS max_sales
    FROM SalesData
)
SELECT s.salesperson_name, sd.total_sales
FROM SalesData sd
JOIN TopSalesperson ts ON sd.salesperson_id = ts.salesperson_id AND sd.total_sales = ts.max_sales
JOIN salespersons s ON sd.salesperson_id = s.salesperson_id;

이 쿼리에서는 SalesData라는 CTE에서 각 판매원의 총매출을 계산하고, TopSalesperson이라는 CTE에서 가장 매출이 많은 판매원을 특정한 후, 그 판매원의 상세 정보를 추출합니다.

예: 특정 연도의 각 월의 평균 매출 추출

마지막으로, 특정 연도의 각 월의 평균 매출을 WITH절을 사용하여 추출하는 방법을 보여줍니다.

WITH MonthlySales AS (
    SELECT DATE_TRUNC('month', sale_date) AS month, AVG(sales_amount) AS avg_sales
    FROM sales
    WHERE EXTRACT(year FROM sale_date) = 2023
    GROUP BY DATE_TRUNC('month', sale_date)
)
SELECT month, avg_sales
FROM MonthlySales
ORDER BY month;

이 쿼리에서는 MonthlySales라는 CTE에서 2023년의 각 월의 평균 매출을 계산하고, 그 결과를 메인 쿼리에서 사용하여 각 월의 평균 매출을 간결하게 추출합니다.

다음으로 서브쿼리를 사용한 SQL 쿼리의 성능을 최적화하기 위한 팁과 기술을 소개합니다.

성능 최적화

서브쿼리를 사용한 SQL 쿼리는 강력하지만, 성능 문제가 발생할 수 있습니다. 여기서는 서브쿼리를 사용한 SQL 쿼리의 성능을 최적화하기 위한 팁과 기술을 소개합니다.

인덱스 활용

서브쿼리에서 사용하는 열에 인덱스를 생성하면 쿼리 실행 속도를 크게 향상시킬 수 있습니다. 특히 서브쿼리 내에서 자주 사용되는 열에는 인덱스를 설정하는 것이 중요합니다.

CREATE INDEX idx_department_id ON employees(department_id);

이 예제에서는 department_id 열에 인덱스를 생성하여 검색을 가속화하고 있습니다.

불필요한 서브쿼리 회피

일부 서브쿼리는 중복되며, JOIN을 사용하여 간소화할 수 있는 경우가 있습니다. 불필요한 서브쿼리를 제거함으로써 쿼리 성능을 향상시킬 수 있습니다.

-- 서브쿼리를 사용한 예
SELECT e.employee_name, d.department_name
FROM employees e
WHERE e.department_id = (SELECT department_id FROM departments WHERE department_name = 'Sales');

-- JOIN을 사용한 최적화 예
SELECT e.employee_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.department_name = 'Sales';

이 예제에서는 서브쿼리를 JOIN으로 대체하여 쿼리가 간단하고 빠르게 실행되도록 했습니다.

EXISTS를 사용한 최적화

서브쿼리의 결과를 존재 여부로 확인할 때, EXISTS 연산자를 사용하여 성능을 향상시킬 수 있습니다. EXISTS는 조건에 일치하는 행이 발견되면 처리를 종료하므로 효율적입니다.

-- 서브쿼리를 사용한 예
SELECT employee_name
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'New York');

-- EXISTS를 사용한 최적화 예
SELECT employee_name
FROM employees e
WHERE EXISTS (
    SELECT 1
    FROM departments d
    WHERE e.department_id = d.department_id AND d.location = 'New York'
);

이 예제에서는 IN 연산자를 EXISTS로 대체하여 쿼리 실행 효율을 향상시켰습니다.

뷰 활용

복잡한 서브쿼리를 포함하는 쿼리를 자주 실행하는 경우, 그 서브쿼리를 뷰로 변환하여 성능을 향상시킬 수 있습니다. 뷰는 쿼리 결과를 가상 테이블로 저장하므로 재사용이 용이합니다.

-- 뷰 생성
CREATE VIEW HighSalaryEmployees AS
SELECT employee_name, salary, department_id
FROM employees
WHERE salary > 100000;

-- 뷰를 사용한 쿼리
SELECT e.employee_name, d.department_name
FROM HighSalaryEmployees e
JOIN departments d ON e.department_id = d.department_id;

이 예제에서는 HighSalaryEmployees라는 뷰를 생성하고, 그 뷰를 사용하여 쿼리의 가독성과 성능을 향상시켰습니다.

통계 정보 업데이트

데이터베이스의 통계 정보를 정기적으로 업데이트함으로써 쿼리 옵티마이저가 최적의 실행 계획을 생성하기 쉽게 할 수 있습니다. 통계 정보는 인덱스나 테이블의 카디널리티에 관한 정보를 포함합니다.

-- 통계 정보 업데이트 (예: PostgreSQL)
ANALYZE employees;

이 예제에서는 employees 테이블의 통계 정보를 업데이트하여 쿼리 성능을 최적화하고 있습니다.

정리

이 기사에서는 복수의 서브쿼리를 결합한 SQL 쿼리 작성법에 대해 설명했습니다. 서브쿼리의 기본 개념, 상관 서브쿼리, 복수의 서브쿼리 결합 방법, 중첩된 서브쿼리와 WITH절의 실용 예제, 그리고 성능 최적화 방법을 다루었습니다. 이러한 기술을 활용함으로써 보다 효율적이고 강력한 SQL 쿼리를 작성할 수 있게 됩니다. SQL 스킬을 향상시키고 복잡한 데이터 추출 및 분석을 수행할 때 유용하게 사용하시길 바랍니다.