Post

SQL의 외래키, JOIN, 서브쿼리, INDEX!

Posted
By Sangtae Seo 16 min read

외래 키(Foreign Key)란?

외래 키(Foreign Key)는 데이터베이스 테이블 간의 관계를 정의하는 데 사용되는 키에요. 외래 키는 한 테이블의 필드가 다른 테이블의 기본 키를 참조하도록 설정되고 이를 통해 두 테이블 간의 참조 무결성을 유지할 수 있어요.

외래 키의 기능

  1. 참조 무결성 유지: 외래 키는 데이터베이스의 참조 무결성을 보장해요. 즉, 외래 키로 설정된 값은 반드시 참조되는 테이블의 기본 키 값 중 하나여야 해요.
  2. 테이블 간 관계 설정: 외래 키를 사용하면 두 테이블 간의 관계(일대일, 일대다, 다대다)를 설정할 수 있어요.

Customers 테이블과 Orders 테이블 간의 외래 키 관계를 보여주는 예시 ^&^

1
2
3
4
5
6
7
8
9
10
11
12
13

-- Customers 테이블 (기본 키: customer_id)
CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100)
);

-- Orders 테이블 (외래 키: customer_id가 Customers 테이블의 customer_id를 참조)
CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    order_date DATE,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);

Orders 테이블의 customer_id는 Customers 테이블의 customer_id를 참조하는 외래 키에요. 이로 인해 Orders 테이블에 삽입되는 모든 customer_id 값은 반드시 Customers 테이블에 존재해야 해요.

외래 키 제약 조건

  1. 참조 무결성 위반 오류 상황: 참조하려는 값이 외래 키가 참조하는 테이블에 존재하지 않을 때 발생해요.
    예시: Orders 테이블에 customer_id가 5인 데이터를 삽입하려고 할 때, Customers 테이블에 customer_id가 5인 레코드가 없는 경우.
    해결 방법: 참조되는 테이블에 먼저 해당 값이 존재하는지 확인한 후, 데이터를 삽입해요.
  2. 삭제 또는 업데이트 시 참조 무결성 위반 오류
    상황: 참조되는 테이블의 데이터가 삭제되거나 업데이트될 때, 이를 참조하는 외래 키가 있는 테이블에 영향을 줄 수 있어요.
    예시: Customers 테이블에서 customer_id가 1인 레코드를 삭제하려고 할 때, Orders 테이블에 customer_id가 1인 레코드가 존재하는 경우.
    해결 방법:
    CASCADE 옵션 사용: 참조되는 레코드가 삭제되거나 업데이트될 때, 외래 키를 가진 레코드도 자동으로 삭제되거나 업데이트되도록 설정해요.
    SET NULL 옵션 사용: 참조되는 레코드가 삭제되거나 업데이트될 때, 외래 키를 가진 필드를 NULL로 설정해요.
1
2
3
4
5
6
7

-- CASCADE 옵션 예시
CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    order_date DATE,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id) ON DELETE CASCADE
);

JOIN

JOIN은 SQL에서 여러 테이블의 데이터를 결합하여 조회할 때 사용하는 명령어에요. JOIN을 사용하면 두 개 이상의 테이블을 특정 조건에 따라 연결하여 하나의 결과 집합으로 결합할 수 있어요.
JOIN에는 여러 종류가 있으며, 각각의 JOIN은 데이터베이스에서 다른 유형의 관계를 처리하는 데 사용돼요.

1. INNER JOIN

INNER JOIN은 두 테이블 간의 일치하는 데이터를 반환해요. 즉, 두 테이블에서 조인 조건을 만족하는 행만 결과로 반환돼요.

1
2
3
4
5
6
7
8

SELECT 
    A.employee_id, 
    A.name, 
    B.department_name
FROM 
    employees A
INNER JOIN 
    departments B ON A.department_id = B.department_id;

employees 테이블과 departments 테이블을 department_id를 기준으로 결합해요.
INNER JOIN은 두 테이블에서 department_id가 일치하는 데이터만 반환해요.

2. LEFT JOIN (또는 LEFT OUTER JOIN)

LEFT JOIN은 왼쪽 테이블의 모든 데이터를 반환하며, 오른쪽 테이블에서 일치하는 데이터가 없을 경우 NULL을 반환해요.

1
2
3
4
5
6
7
8

SELECT 
    A.employee_id, 
    A.name, 
    B.department_name
FROM 
    employees A
LEFT JOIN 
    departments B ON A.department_id = B.department_id;

employees 테이블의 모든 데이터를 반환하며, departments 테이블에서 일치하는 department_id가 없는 경우 department_name에 NULL이 반환돼요.

3. RIGHT JOIN (또는 RIGHT OUTER JOIN)

RIGHT JOIN은 오른쪽 테이블의 모든 데이터를 반환하며, 왼쪽 테이블에서 일치하는 데이터가 없을 경우 NULL을 반환해요.

1
2
3
4
5
6
7
8

SELECT 
    A.employee_id, 
    A.name, 
    B.department_name
FROM 
    employees A
RIGHT JOIN 
    departments B ON A.department_id = B.department_id;

departments 테이블의 모든 데이터를 반환하며, employees 테이블에서 일치하는 department_id가 없는 경우 employee_id와 name에 NULL이 반환돼요.

4. FULL JOIN (또는 FULL OUTER JOIN)

FULL JOIN은 두 테이블의 모든 데이터를 반환해요. 일치하는 데이터는 결합되고, 일치하지 않는 데이터는 각각 NULL로 반환돼요.

1
2
3
4
5
6
7
8

SELECT 
    A.employee_id, 
    A.name, 
    B.department_name
FROM 
    employees A
FULL OUTER JOIN 
    departments B ON A.department_id = B.department_id;

두 테이블 간에 일치하는 데이터를 반환하며, 일치하지 않는 데이터는 각각 NULL로 반환돼요.
만약 employees 테이블에 department_id가 없는 경우, 해당 레코드에 대해 department_name은 NULL이 돼요. 반대로 departments 테이블에 department_id가 없는 경우, 해당 레코드에 대해 employee_id와 name이 NULL로 반환돼요.

5. CROSS JOIN

CROSS JOIN은 두 테이블 간의 모든 조합을 반환해요. 즉, 첫 번째 테이블의 각 행과 두 번째 테이블의 각 행이 모두 결합돼요.

1
2
3
4
5
6
7
8

SELECT 
    A.employee_id, 
    A.name, 
    B.department_name
FROM 
    employees A
CROSS JOIN 
    departments B;

모든 employees의 각 행이 departments의 모든 행과 조합되어 반환돼요.
결과는 두 테이블의 행 수의 곱이 돼요.

6. SELF JOIN

SELF JOIN은 테이블을 자기 자신과 조인하는 거에요. 이 경우에는 동일한 테이블을 두 번 사용하지만, 각각 다른 별칭을 사용하여 조인해요.

1
2
3
4
5
6
7
8

SELECT 
    A.employee_id AS employee_id,
    A.name AS employee_name,
    B.name AS manager_name
FROM 
    employees A
INNER JOIN 
    employees B ON A.manager_id = B.employee_id;

employees 테이블을 자기 자신과 조인하여, 각 직원의 매니저 이름을 조회해요.
예를 들어, A.manager_id가 B.employee_id와 일치하는 경우, B.name은 해당 직원의 매니저 이름이 돼요.

요약

INNER JOIN: 두 테이블에서 일치하는 데이터만 반환해요.
LEFT JOIN: 왼쪽 테이블의 모든 데이터를 반환하고, 오른쪽 테이블에 일치하는 데이터가 없으면 NULL을 반환해요. RIGHT JOIN: 오른쪽 테이블의 모든 데이터를 반환하고, 왼쪽 테이블에 일치하는 데이터가 없으면 NULL을 반환해요. FULL JOIN: 두 테이블의 모든 데이터를 반환하며, 일치하지 않는 데이터는 각각 NULL로 반환돼요. CROSS JOIN: 두 테이블 간의 모든 조합을 반환해요. SELF JOIN: 같은 테이블을 두 번 사용하여 자기 자신과 조인해요.

서브쿼리

서브쿼리(또는 서브쿼리, 서브셀렉트)는 SQL 쿼리 내에 포함된 또 다른 쿼리를 의미해요. 서브쿼리는 복잡한 쿼리를 작성하는 데 유용하며, 주 쿼리의 결과를 기반으로 필터링, 집계, 또는 계산을 수행할 수 있어요.

1. 서브쿼리를 SELECT 문 내에서 사용

1
2
3
4
5
6

SELECT 
    employee_id,
    name,
    (SELECT AVG(salary) FROM employees) AS average_salary
FROM 
    employees;

서브쿼리: (SELECT AVG(salary) FROM employees)는 employees 테이블의 전체 평균 급여를 계산해요.
주 쿼리: employee_id와 name을 반환하면서, 각 직원의 급여와 전체 평균 급여를 함께 표시해요.

2. 서브쿼리를 WHERE 절 내에서 사용

1
2
3
4
5
6
7
8

SELECT 
    employee_id,
    name,
    salary
FROM 
    employees
WHERE 
    salary > (SELECT AVG(salary) FROM employees);

서브쿼리: (SELECT AVG(salary) FROM employees)는 employees 테이블의 평균 급여를 계산해요.
주 쿼리: 평균 급여보다 높은 급여를 가진 직원들을 반환해요.

3. 서브쿼리를 FROM 절 내에서 사용

1
2
3
4
5
6
7

SELECT 
    department_id,
    AVG(salary) AS avg_salary
FROM 
    (SELECT department_id, salary FROM employees WHERE salary > 50000) AS high_paid_employees
GROUP BY 
    department_id;

서브쿼리: (SELECT department_id, salary FROM employees WHERE salary > 50000)는 급여가 50,000 이상인 직원만을 선택해요.
주 쿼리: 이 서브쿼리의 결과를 high_paid_employees라는 별칭을 사용하여 부서별 평균 급여를 계산해요.

4. 서브쿼리를 HAVING 절 내에서 사용

1
2
3
4
5
6
7
8
9

SELECT 
    department_id,
    COUNT(*) AS num_employees
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 department_counts);

서브쿼리: (SELECT AVG(employee_count) FROM (SELECT department_id, COUNT(*) AS employee_count FROM employees GROUP BY department_id) AS department_counts)는 각 부서의 직원 수의 평균을 계산해요.
주 쿼리: 부서별로 직원 수가 이 평균보다 많은 부서를 반환해요.

INDEX

인덱스(Index)는 데이터베이스에서 검색 성능을 향상시키기 위해 사용되는 데이터 구조애요. 인덱스는 테이블의 특정 컬럼에 대해 빠른 검색을 가능하게 하여, 쿼리 성능을 크게 향상시킬 수 있어요. 인덱스는 특히 대량의 데이터가 있는 테이블에서 쿼리 성능을 개선하는 데 유용해요.

인덱스 설명

  • 기능: 인덱스는 데이터베이스 테이블의 특정 열에 대한 데이터 구조를 유지하여, 해당 열을 기반으로 하는 검색을 빠르게 수행할 수 있게 해요.
  • 구조: 인덱스는 보통 B-트리, 해시, 비트맵 등과 같은 데이터 구조를 사용하여 구현돼요.
  • 장점: 검색, 정렬, 조인 등의 성능을 향상시켜요.
  • 단점: 인덱스는 추가적인 저장 공간을 소모하고, 데이터의 삽입, 업데이트, 삭제 시 성능을 저하시킬 수 있어요.

인덱스 예시 및 설명

  1. 단일 열 인덱스 목적: 특정 열에 대한 검색 성능을 향상시키기 위해 사용돼요.
1

CREATE INDEX idx_employee_name ON employees(name);

인덱스 생성: employees 테이블의 name 열에 대해 idx_employee_name이라는 인덱스를 생성해요.
사용 경우: name 열에 대해 WHERE 절에서 검색을 수행할 때 인덱스를 사용하여 검색 성능을 향상시켜요.

1

SELECT * FROM employees WHERE name = 'John Doe';

이 쿼리는 name 열에 대해 생성된 인덱스를 사용하여 검색 속도를 빠르게 할 수 있어요.

  1. 복합 인덱스 (다중 열 인덱스) 목적: 여러 열에 대한 검색 성능을 향상시키기 위해 사용돼요. 복합 인덱스는 인덱스가 생성된 순서대로 열에 대해 쿼리 성능을 개선해요.
1

CREATE INDEX idx_employee_dept_name ON employees(department_id, name);

인덱스 생성: employees 테이블의 department_id와 name 열에 대해 idx_employee_dept_name이라는 복합 인덱스를 생성해요. 사용 경우: department_id와 name 두 열을 동시에 사용하는 검색 쿼리에서 인덱스를 사용하여 성능을 향상시켜요.

1

SELECT * FROM employees WHERE department_id = 2 AND name = 'John Doe';

이 쿼리는 department_id와 name 두 열 모두를 사용하므로, 복합 인덱스 idx_employee_dept_name을 통해 검색 성능이 향상돼요.

패스트캠퍼스, 데이터분석
SQL 외래키 JOIN 서브쿼리 INDEX
This post is licensed under CC BY 4.0 by the author.