SQLite 서브쿼리 기법

소개

이 랩에서는 데이터 검색 및 필터링 기능을 향상시키기 위해 SQLite 서브쿼리 (subquery) 기술을 탐구합니다. WHERE 절 내에서 서브쿼리를 사용하는 방법, SELECT 문에 서브쿼리를 포함하는 방법, 그리고 상관 서브쿼리 (correlated subquery) 를 구축하는 방법을 배우게 됩니다.

테이블 생성 및 데이터 삽입

이 첫 번째 단계에서는 departments와 employees 두 개의 테이블을 생성하고, 샘플 데이터를 삽입합니다. 이렇게 하면 다음 단계에서 서브쿼리를 사용하는 연습에 필요한 데이터를 얻을 수 있습니다.

먼저, LabEx VM 에서 터미널을 엽니다. 기본 경로는 /home/labex/project입니다.

시작하려면 company.db라는 SQLite 데이터베이스에 연결합니다. 데이터베이스가 존재하지 않으면 SQLite 가 자동으로 생성합니다. 다음 명령을 실행합니다.

sqlite3 company.db

이 명령은 SQLite 명령줄 도구를 열고 company.db 데이터베이스에 연결합니다. sqlite> 프롬프트를 보게 됩니다.

이제 다음 SQL 명령을 사용하여 departments 테이블을 생성합니다.

CREATE TABLE departments (
    department_id INTEGER PRIMARY KEY,
    department_name TEXT,
    location TEXT
);

이 명령은 department_id, department_name, location 세 개의 열을 가진 departments라는 테이블을 생성합니다. department_id 열은 이 테이블의 기본 키 (primary key) 입니다.

다음으로, departments 테이블에 샘플 데이터를 삽입합니다.

INSERT INTO departments (department_name, location) VALUES
('Sales', 'New York'),
('Marketing', 'Los Angeles'),
('Engineering', 'San Francisco');

이 명령은 세 개의 서로 다른 부서와 해당 위치를 나타내는 세 개의 행을 departments 테이블에 삽입합니다.

이제 다음 SQL 명령을 사용하여 employees 테이블을 생성합니다.

CREATE TABLE employees (
    employee_id INTEGER PRIMARY KEY,
    employee_name TEXT,
    department_id INTEGER,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

이 명령은 employee_id, employee_name, department_id 세 개의 열을 가진 employees라는 테이블을 생성합니다. department_id 열은 departments 테이블의 department_id 열을 참조하는 외래 키 (foreign key) 입니다.

마지막으로, employees 테이블에 샘플 데이터를 삽입합니다.

INSERT INTO employees (employee_name, department_id) VALUES
('Alice', 1),
('Bob', 2),
('Charlie', 1),
('David', 3);

이 명령은 네 명의 서로 다른 직원과 해당 부서 ID 를 나타내는 네 개의 행을 employees 테이블에 삽입합니다.

WHERE 절에서 서브쿼리 사용

이 단계에서는 다른 쿼리의 출력을 기반으로 결과를 필터링하기 위해 WHERE 절에서 서브쿼리를 사용하는 방법을 배웁니다.

서브쿼리는 다른 SQL 문 안에 중첩된 SELECT 문입니다. 이 경우, 서브쿼리를 사용하여 departments 테이블에서 department_id 값을 선택한 다음, 해당 값을 사용하여 employees 테이블에 대한 쿼리의 결과를 필터링합니다.

'New York'에 위치한 부서에서 근무하는 모든 직원을 찾아보겠습니다. 이를 위해 먼저 'New York'에 있는 부서의 department_id를 찾은 다음, 해당 department_id를 가진 모든 직원을 찾아야 합니다.

sqlite> 프롬프트에 다음 SQL 명령을 입력합니다.

SELECT employee_name FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'New York');

이 명령은 department_id가 서브쿼리에 의해 반환된 department_id 값 목록에 있는 employees 테이블에서 employee_name을 선택합니다. 서브쿼리는 location이 'New York'인 departments 테이블에서 department_id를 선택합니다.

명령을 실행한 후 다음 출력을 보게 됩니다.

Alice
Charlie

이 출력은 뉴욕에 위치한 Sales 부서에서 근무하는 직원의 이름을 보여줍니다.

SELECT 절에 서브쿼리 포함

이 단계에서는 관련 데이터를 검색하기 위해 SQL 문의 SELECT 절 내에 서브쿼리를 포함하는 방법을 배웁니다.

SELECT 절에 서브쿼리를 포함하면 외부 쿼리의 각 행에 대해 단일 값을 검색할 수 있습니다. 이 값은 종종 계산된 값 또는 다른 테이블의 관련 값입니다.

각 직원의 이름과 해당 부서의 이름을 검색해 보겠습니다. sqlite> 프롬프트에 다음 SQL 명령을 입력합니다.

SELECT employee_name, (SELECT department_name FROM departments WHERE department_id = employees.department_id) AS department_name FROM employees;

이 명령은 employees 테이블에서 employee_name을 선택하고, departments 테이블에서 department_name을 검색하는 서브쿼리도 포함합니다. 서브쿼리는 employees 테이블의 department_id를 사용하여 올바른 부서를 일치시킵니다. 서브쿼리의 결과는 department_name으로 별칭 (alias) 처리됩니다.

명령을 실행한 후 다음 출력을 보게 됩니다.

Alice|Sales
Bob|Marketing
Charlie|Sales
David|Engineering

이 출력은 각 직원의 이름과 해당 부서의 이름을 보여줍니다.

상관 서브쿼리 구축

이 단계에서는 상관 서브쿼리를 구축하는 방법을 배웁니다. 상관 서브쿼리는 외부 쿼리의 열을 참조하는 서브쿼리입니다. 즉, 서브쿼리는 외부 쿼리의 각 행에 대해 한 번씩 실행됩니다.

한 번 실행되고 그 결과가 외부 쿼리에서 사용되는 단순 서브쿼리와 달리, 상관 서브쿼리는 해당 값에 대해 외부 쿼리에 의존합니다. 서브쿼리 내의 값을 외부 쿼리의 현재 행에 있는 값과 비교해야 할 때 사용됩니다.

직원의 이름과 동일한 도시에서 근무하는 부서에서 근무하는 모든 직원을 찾아보겠습니다. 이를 위해 먼저 직원 이름을 도시 이름으로 업데이트합니다.

UPDATE employees SET employee_name = 'New York' WHERE employee_name = 'Alice';
UPDATE employees SET employee_name = 'Los Angeles' WHERE employee_name = 'Bob';
UPDATE employees SET employee_name = 'New York' WHERE employee_name = 'Charlie';
UPDATE employees SET employee_name = 'San Francisco' WHERE employee_name = 'David';

이 명령은 employees 테이블의 employee_name 열을 도시 이름으로 업데이트합니다.

이제 상관 서브쿼리를 작성해 보겠습니다.

SELECT e.employee_name FROM employees e WHERE e.department_id IN (SELECT d.department_id FROM departments d WHERE d.location = e.employee_name);

이 명령은 department_id가 서브쿼리에 의해 반환된 department_id 값 목록에 있는 employees 테이블 (별칭 e) 에서 employee_name을 선택합니다. 서브쿼리는 location이 외부 쿼리의 employee_name과 일치하는 departments 테이블 (별칭 d) 에서 department_id를 선택합니다.

명령을 실행한 후 다음 출력을 보게 됩니다.

New York
Los Angeles
San Francisco

이 출력은 동일한 도시에 위치한 부서에서 근무하는 직원의 이름 (이제 도시 이름) 을 보여줍니다.

JOIN 을 사용한 서브쿼리 효율성 평가

이 단계에서는 서브쿼리의 효율성을 평가하고 최적화를 위해 JOIN 연산을 사용하는 대체 접근 방식을 탐색하는 방법을 배웁니다.

서브쿼리는 강력하지만, 특히 대규모 데이터 세트의 경우 성능 병목 현상을 초래할 수 있습니다. 많은 경우, 더 효율적일 수 있는 JOIN 연산을 사용하여 서브쿼리를 다시 작성할 수 있습니다.

이전 단계에서 사용한 상관 서브쿼리를 JOIN을 사용하여 다시 작성해 보겠습니다.

SELECT e.employee_name FROM employees e JOIN departments d ON e.department_id = d.department_id WHERE d.location = e.employee_name;

이 명령은 employees 테이블 (별칭 e) 에서 employee_name을 선택하고 department_id 열을 기준으로 departments 테이블 (별칭 d) 과 조인합니다. 그런 다음 WHERE 절은 departments 테이블의 location이 employees 테이블의 employee_name과 일치하는 행만 포함하도록 결과를 필터링합니다.

결과를 확인하려면 명령을 실행하십시오. 이전 단계와 동일한 출력을 보게 됩니다.

New York
Los Angeles
San Francisco

효율성을 평가하기 위해 일반적으로 변경 전후에 EXPLAIN QUERY PLAN을 사용합니다. 그러나 LabEx 환경의 제한으로 인해 EXPLAIN QUERY PLAN 명령을 완전히 시연할 수 없습니다. 핵심은 JOIN 연산이 특히 더 큰 데이터 세트의 경우 상관 서브쿼리보다 더 효율적인 경우가 많다는 것입니다.

마지막으로, sqlite3 셸을 종료합니다.

.exit

그러면 bash 프롬프트로 돌아갑니다.

요약

이 랩에서는 SQLite 서브쿼리를 사용하여 데이터 검색 및 필터링 기능을 향상시키는 방법을 배웠습니다. WHERE 절 내에서 서브쿼리를 사용하고, SELECT 문에 서브쿼리를 포함시키고, 상관 서브쿼리를 구축하는 연습을 했습니다. 또한 더 나은 효율성을 위해 JOIN 연산을 사용하여 서브쿼리를 다시 작성하는 방법도 배웠습니다. 이러한 기술은 SQLite 에서 데이터를 처리하기 위한 강력한 도구를 제공합니다.