SQLite 테이블 조인

소개

이 랩에서는 테이블 조인 기술을 사용하여 SQLite 에서 여러 테이블의 데이터를 결합하는 방법을 배우게 됩니다. INNER JOIN, LEFT JOIN, 여러 테이블 조인, 그리고 조인된 결과 필터링을 다룰 것입니다. 이 랩을 마치면 SQLite 를 사용하여 데이터를 효율적으로 검색하고 결합할 수 있게 됩니다.

테이블 생성 및 데이터 채우기

이 단계에서는 employees와 departments 두 개의 테이블을 생성하고 샘플 데이터를 채웁니다. 이 테이블들은 이후 단계에서 테이블 조인 기술을 시연하는 데 사용됩니다.

먼저, 터미널에서 다음 명령을 실행하여 SQLite 셸을 엽니다.

sqlite3 /home/labex/project/company.db

이 명령은 SQLite 셸을 열고 company.db라는 데이터베이스 파일에 연결합니다. 파일이 존재하지 않으면 SQLite 가 파일을 생성합니다.

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

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT,
    department_id INTEGER
);

이 명령은 id, name, 그리고 department_id 세 개의 열을 가진 employees라는 테이블을 생성합니다. id 열은 기본 키이며 각 직원을 고유하게 식별합니다.

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

CREATE TABLE departments (
    id INTEGER PRIMARY KEY,
    name TEXT
);

이 명령은 id와 name 두 개의 열을 가진 departments라는 테이블을 생성합니다. id 열은 기본 키이며 각 부서를 고유하게 식별합니다.

이제 employees 테이블에 샘플 데이터를 삽입합니다.

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

이 명령은 employees 테이블에 다섯 개의 행을 삽입합니다. 각 행은 직원과 해당 부서 ID 를 나타냅니다.

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

INSERT INTO departments (name) VALUES
('Sales'),
('Marketing'),
('Engineering');

이 명령은 departments 테이블에 세 개의 행을 삽입합니다. 각 행은 부서와 해당 이름을 나타냅니다.

테이블이 올바르게 생성되고 채워졌는지 확인하려면 다음 SQL 명령을 실행할 수 있습니다.

SELECT * FROM employees;

예상 출력:

1|Alice|1
2|Bob|2
3|Charlie|1
4|David|3
5|Eve|

그리고:

SELECT * FROM departments;

예상 출력:

1|Sales
2|Marketing
3|Engineering

INNER JOIN 쿼리 실행

이 단계에서는 SQLite 에서 INNER JOIN 절을 사용하는 방법을 배우게 됩니다. INNER JOIN은 지정된 조건을 기반으로 두 테이블 모두에서 일치하는 항목이 있을 때 행을 반환합니다.

INNER JOIN의 기본 구문은 다음과 같습니다.

SELECT column1, column2 FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;

이 경우, 직원 이름과 해당 부서 이름을 함께 검색하려고 합니다. 이를 위해 다음 SQL 명령을 실행합니다.

SELECT employees.name, departments.name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id;

이 명령은 employees 테이블의 department_id 열이 departments 테이블의 id 열과 일치하는 것을 기반으로 employees와 departments 테이블을 조인합니다. 그런 다음 employees 테이블에서 직원의 이름을, departments 테이블에서 부서의 이름을 선택합니다.

예상 출력:

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

이 출력은 직원의 이름과 해당 부서 이름을 보여줍니다. Eve 의 department_id가 NULL이고 일치하는 부서가 없기 때문에 결과에 포함되지 않았다는 점에 유의하십시오. INNER JOIN은 일치하는 행만 반환합니다.

선택적 데이터에 LEFT JOIN 사용

이 단계에서는 SQLite 에서 LEFT JOIN을 사용하는 방법을 배우게 됩니다. LEFT JOIN (또는 LEFT OUTER JOIN) 은 왼쪽 테이블의 모든 행과 오른쪽 테이블에서 일치하는 행을 반환합니다. 오른쪽 테이블에 일치하는 항목이 없으면 오른쪽 테이블의 열에 대해 NULL 값이 반환됩니다. 이는 한 테이블의 모든 레코드를 검색하고 다른 테이블에서 관련 데이터가 있는 경우 포함하려는 경우 유용합니다.

LEFT JOIN의 기본 구문은 다음과 같습니다.

SELECT column1, column2 FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;

모든 직원과 해당 부서 이름을 검색하되, 직원에게 부서가 할당되지 않은 경우에도 검색하려면 다음 SQL 명령을 실행합니다.

SELECT employees.name, departments.name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.id;

이 명령은 employees 테이블의 department_id 열이 departments 테이블의 id 열과 일치하는 것을 기반으로 employees와 departments 테이블을 조인합니다. employees 테이블에서 직원의 이름을, departments 테이블에서 부서의 이름을 선택합니다. LEFT JOIN이므로 모든 직원이 나열됩니다.

예상 출력:

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

Eve 가 나열되어 있지만, 그녀의 department_id가 NULL이고 일치하는 부서가 없기 때문에 부서 이름이 비어 있음 (NULL) 을 확인하십시오. 이는 LEFT JOIN이 오른쪽 테이블 (departments) 에 일치하는 항목이 없더라도 왼쪽 테이블 (employees) 의 모든 행을 포함하는 방법을 보여줍니다.

여러 테이블 조인

이 단계에서는 SQLite 에서 여러 테이블을 조인하는 방법을 배우게 됩니다. 두 개 이상의 테이블을 조인하는 것은 여러 JOIN 절을 사용하여 여러 관련 테이블의 데이터를 결합하는 것을 포함합니다.

먼저, 부서 위치를 저장하기 위해 locations라는 새 테이블을 추가해 보겠습니다. 다음 SQL 명령을 실행하여 locations 테이블을 생성합니다.

CREATE TABLE locations (
    id INTEGER PRIMARY KEY,
    department_id INTEGER,
    city TEXT
);

이 명령은 id, department_id, city의 세 개의 열이 있는 locations라는 테이블을 생성합니다.

다음으로, locations 테이블에 몇 가지 샘플 데이터를 삽입합니다.

INSERT INTO locations (department_id, city) VALUES
(1, 'New York'),
(2, 'Los Angeles'),
(3, 'Chicago');

이제 세 테이블을 모두 조인하여 직원 이름, 부서 이름 및 부서 위치를 검색해 보겠습니다. 다음 SQL 명령을 실행합니다.

SELECT employees.name, departments.name, locations.city
FROM employees
INNER JOIN departments ON employees.department_id = departments.id
INNER JOIN locations ON departments.id = locations.department_id;

이 쿼리는 먼저 employees.department_id = departments.id를 기반으로 employees와 departments를 조인합니다. 그런 다음, departments.id = locations.department_id를 기반으로 결과를 locations 테이블과 조인합니다. 이렇게 하면 직원이 해당 부서에 연결된 다음 해당 부서의 위치에 연결됩니다.

예상 출력:

Alice|Sales|New York
Bob|Marketing|Los Angeles
Charlie|Sales|New York
David|Engineering|Chicago

이것은 각 직원의 이름, 해당 부서의 이름, 그리고 해당 부서가 위치한 도시를 보여줍니다. Eve 는 부서가 할당되지 않았기 때문에 포함되지 않습니다.

조인된 결과 필터링

이 단계에서는 SQLite 에서 WHERE 절을 사용하여 조인된 테이블의 결과를 필터링하는 방법을 배우게 됩니다. 필터링을 사용하면 조인 작업 후 특정 기준을 충족하는 행만 검색할 수 있습니다.

영업 부서에서 근무하는 직원의 이름을 검색하려면 다음 SQL 명령을 실행합니다.

SELECT employees.name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id
WHERE departments.name = 'Sales';

이 쿼리는 employees와 departments 테이블을 조인한 다음 부서 이름이 'Sales'인 직원만 포함하도록 결과를 필터링합니다.

예상 출력:

Alice
Charlie

이것은 Alice 와 Charlie 가 영업 부서에서 근무한다는 것을 보여줍니다.

이제 뉴욕에 위치한 부서에서 근무하는 직원의 이름을 검색해 보겠습니다. 다음 SQL 명령을 실행합니다.

SELECT employees.name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id
INNER JOIN locations ON departments.id = locations.department_id
WHERE locations.city = 'New York';

이 쿼리는 세 테이블을 모두 조인한 다음 부서의 위치가 뉴욕인 직원만 포함하도록 결과를 필터링합니다.

예상 출력:

Alice
Charlie

다시, Alice 와 Charlie 는 뉴욕에 위치한 부서에 있는 유일한 직원입니다.

요약

이 랩에서는 테이블 조인 기술을 사용하여 SQLite 에서 여러 테이블의 데이터를 결합하는 방법을 배웠습니다. 두 테이블 모두에서 일치하는 항목이 있을 때 행을 반환하는 INNER JOIN과 왼쪽 테이블의 모든 행과 오른쪽 테이블의 일치하는 행을 반환하는 LEFT JOIN을 다루었습니다. 또한 여러 테이블을 조인하고 WHERE 절을 사용하여 조인된 결과를 필터링하는 방법도 배웠습니다. 이러한 기술을 통해 SQLite 를 사용하여 데이터를 효율적으로 검색하고 결합할 수 있습니다.