[DBMS]효율적인 데이터 조회를 위한 MariaDB 서브쿼리 사용법

서브쿼리는 복잡한 데이터 조회와 분석을 가능하게 하는 중요한 도구입니다. 이 포스트에서는 MariaDB 서브쿼리에 대한 기본 개념, 종류, 작성 및 사용법, 실전 예제, 성능 최적화 방법에 대해 알아보겠습니다.

≣ 목차

• 서브쿼리란 무엇인가❓
• 서브쿼리의 종류📊
• 서브쿼리 작성 및 사용법📝
• 성능 최적화🚀
• 핵심 내용👀

예제 데이터📦

아래는 각 예제에서 사용할 예제 데이터 테이블입니다:

Employees 테이블

employee_id	name	department_id	salary
1	Alice	101	50000
2	Bob	102	60000
3	Charlie	101	55000
4	David	103	45000
5	Eve	102	70000

Departments 테이블

department_id	name	location_id
101	Sales	1700
102	IT	1700
103	HR	1800

Locations 테이블

location_id	city
1700	London
1800	Paris

Job_History 테이블

department_id	job_id	end_date
101	1	2023-03-15
102	2	2024-01-01
103	3	2022-12-31

서브쿼리란 무엇인가❓

정의: 서브쿼리(Subquery)는 SQL 문 안에 포함된 또 다른 SQL 문입니다. 주로 메인 쿼리의 결과를 제한하거나 특정 값을 계산하는 데 사용됩니다.

용도: 서브쿼리는 복잡한 데이터 검색, 조건부 데이터 조회, 집계 함수와 결합된 데이터 분석 등 다양한 용도로 사용됩니다.

예제

SELECT * 
FROM `employees` 
WHERE `salary` > (
    SELECT AVG(`salary`) 
    FROM `employees`
);

위 예제는 전체 직원의 평균 급여보다 높은 급여를 받는 직원을 조회합니다.

결과

employee_id	name	department_id	salary
2	Bob	102	60000
3	Charlie	101	55000
4	Eve	102	70000

서브쿼리의 종류📊

단일 행 서브쿼리

단일 행 서브쿼리는 하나의 값이나 하나의 행만을 반환하는 서브쿼리입니다. 이는 WHERE 구문이나 SELECT 구문의 일부로 사용됩니다. 예를 들어, 가장 높은 값을 찾거나 조건에 맞는 한 가지 값을 찾는 데 유용합니다.

예제

Sales 부서에 속한 직원 이름을 조회합니다.

SELECT `name` 
FROM `employees` 
WHERE `department_id` = (
    SELECT `department_id` 
    FROM `departments` 
    WHERE `name` = 'Sales'
);

결과

name
Alice
Bob

다중 행 서브쿼리

다중 행 서브쿼리는 여러 개의 행을 반환하는 서브쿼리입니다. 이는 IN, ANY, ALL 등의 비교 연산자와 함께 사용될 수 있습니다. 예를 들어, 특정 집합에 포함되는 값들을 찾거나, 여러 행을 비교할 때 유용합니다.

예제

London에 위치한 부서에 속한 직원의 이름을 조회합니다.

SELECT `name` 
FROM `employees` 
WHERE `department_id` IN (
    SELECT `department_id` 
    FROM `departments` 
    WHERE `location_id` = (
        SELECT `location_id` 
        FROM `locations` 
        WHERE `city` = 'London'
    )
);

결과

name
Alice
Bob
Charlie
Eve

IN, ALL, ANY는 서브쿼리나 비교 연산자와 함께 사용되어 특정 값을 비교하거나 조건을 설정하는 데 사용됩니다. 각각의 역할을 간단히 설명하겠습니다:

1. IN: IN 연산자는 서브쿼리의 결과 집합 안에 특정 값이 존재하는지 여부를 확인합니다. 예를 들어, WHERE column_name IN (subquery)와 같이 사용되어 column_name이 서브쿼리의 결과 집합 안에 있는지 확인할 수 있습니다.
2. ANY: ANY 연산자는 서브쿼리의 결과 집합 중 하나 이상의 조건을 만족하는지 여부를 확인합니다. 예를 들어, WHERE column_name > ANY (subquery)와 같이 사용되어 column_name이 서브쿼리의 결과 집합 중 어떤 값보다 큰지 여부를 확인할 수 있습니다.
3. ALL: ALL 연산자는 서브쿼리의 결과 집합의 모든 값이 조건을 만족하는지 여부를 확인합니다. 예를 들어, WHERE column_name > ALL (subquery)와 같이 사용되어 column_name이 서브쿼리의 결과 집합 안의 모든 값보다 큰지 여부를 확인할 수 있습니다.

이렇게 사용하면 서브쿼리의 결과에 따라 다양한 조건을 설정할 수 있습니다. 간단한 예제를 통해 IN, ALL, ANY 연산자를 설명하겠습니다.

예제

학생들의 성적을 저장한 테이블이 있습니다. 이 테이블에서 특정 과목의 성적이 특정 범위 내에 있는지 확인하는 쿼리를 작성해보겠습니다.

students 테이블

student_id	name	score
1	Alice	85
2	Bob	70
3	Charlie	95
4	David	60
5	Eve	75

1. IN 연산자 예제:

SELECT `name` 
FROM `students` 
WHERE `score` IN (85, 95);

이 쿼리는 성적이 85 또는 95인 학생의 이름을 반환합니다. 결과는 "Alice"와 "Charlie"가 됩니다.

2. ANY 연산자 예제:

SELECT name 
FROM students 
WHERE score > ANY (SELECT score FROM students WHERE name = 'Bob');

이 쿼리는 "Bob"의 성적보다 높은 성적을 받은 학생의 이름을 반환합니다. 결과는 "Alice", "Charlie", "Eve"가 됩니다.

3. ALL 연산자 예제:

SELECT name 
FROM students 
WHERE score > ALL (SELECT score FROM students WHERE name = 'David');

이 쿼리는 "David"의 성적보다 높은 성적을 모두 받은 학생의 이름을 반환합니다. 결과는 "Alice", "Charlie", "Eve"가 됩니다.

다중 열 서브쿼리

다중 열 서브쿼리는 여러 개의 열을 반환하는 서브쿼리입니다. 이러한 서브쿼리는 주로 외부 쿼리에서 복수의 열 값을 비교하거나 다룰 때 사용됩니다. 주로 비교 연산자와 함께 사용되며, 결과를 비교할 때 다중 열을 반환하는 서브쿼리가 필요할 수 있습니다.

예제

'2023-01-01' 이후에 종료된 부서와 직무에 속한 직원 이름을 조회합니다.

SELECT `name` 
FROM `employees` 
WHERE (`department_id`, `job_id`) IN (
    SELECT `department_id`, `job_id` 
    FROM `job_history` 
    WHERE `end_date` > '2023-01-01'
);

결과

name
Charlie
Eve

상관 서브쿼리

상관 서브쿼리는 서브쿼리 내에서 외부 쿼리의 열을 참조하여 작동하는 특별한 유형의 서브쿼리입니다. 이러한 서브쿼리는 외부 쿼리의 결과에 따라 동적으로 동작하며, 외부 쿼리의 행마다 서브쿼리가 반복적으로 실행됩니다.

주로 외부 쿼리의 결과에 따라 서브쿼리가 필터링되거나 집계되는 상황에서 사용됩니다. 외부 쿼리와 내부 서브쿼리 간의 상호작용을 통해 보다 유연하고 정교한 데이터 처리를 할 수 있습니다.

예제

각 부서의 평균 급여보다 높은 급여를 받는 직원 이름을 조회합니다.

SELECT `name` 
FROM `employees` AS `e` 
WHERE `salary` > (
    SELECT AVG(`salary`) 
    FROM `employees` 
    WHERE `department_id` = `e`.`department_id`
);

결과

name
Bob
Eve

서브쿼리 작성 및 사용법📝

1. 기본 문법: 서브쿼리는 괄호 ()로 감싸서 작성합니다.

2. SELECT 문 내 서브쿼리:

예제

각 직원의 평균 급여와 함께 직원 이름을 조회합니다.

SELECT `name`, 
    (SELECT AVG(`salary`) FROM `employees`) AS `avg_salary` 
FROM `employees`;

결과

name	avg_salary
Alice	56000
Bob	56000
Charlie	56000
David	56000
Eve	56000

3. WHERE 절 내 서브쿼리:

예제

HR 부서에 속한 직원 이름을 조회합니다.

SELECT `name` 
FROM `employees` 
WHERE `department_id` = (
    SELECT `department_id` 
    FROM `departments` 
    WHERE `name` = 'HR'
);

결과

name
David

4. FROM 절 내 서브쿼리:

예제

각 직원의 부서 이름과 함께 직원 이름을 조회합니다.

SELECT `e`.`name`, `d`.`department_name` 
FROM `employees` AS `e`
JOIN (
    SELECT `department_id`, `name` AS `department_name` 
    FROM `departments`
) AS `d` ON `e`.`department_id` = `d`.`department_id`;

결과

name	department_name
Alice	Sales
Bob	IT
Charlie	Sales
David	HR
Eve	IT

성능 최적화🚀

1. 서브쿼리 성능 문제: 서브쿼리는 때때로 성능 문제를 일으킬 수 있습니다. 특히 상관 서브쿼리는 메인 쿼리의 각 행마다 서브쿼리를 실행하므로 비효율적일 수 있습니다.

2. 최적화 팁:

• 인덱스 사용: 서브쿼리에서 사용하는 열에 인덱스를 설정하여 조회 속도를 높입니다.
• JOIN 사용: 가능한 경우 서브쿼리 대신 JOIN을 사용하여 성능을 향상합니다.

3. 대안 제시: JOIN을 사용한 대체 방법

SELECT `e`.`name`, `d`.`name` 
FROM `employees` AS `e` 
JOIN `departments` AS `d` ON `e`.`department_id` = `d`.`department_id` 
WHERE `d`.`location_id` = 1700;

위의 코드는 다음을 수행합니다:

1. employees 테이블과 departments 테이블을 조인합니다. 이를 위해 employees 테이블을 e라는 별칭으로, departments 테이블을 d라는 별칭으로 지정합니다.
2. 조인 조건으로는 employees 테이블의 department_id와 departments 테이블의 department_id를 사용합니다.
3. 조건절에서는 departments 테이블의 location_id가 1700인 행만을 선택합니다.
4. 결과로는 employees 테이블의 직원 이름과 그들이 속한 부서 이름을 반환합니다.

결과

e.name	d.name
Alice	Sales
Bob	IT
Charlie	Sales
Eve	IT

핵심 내용👀