-
Notifications
You must be signed in to change notification settings - Fork 0
1조 SQL JOIN
git clone https://github.com/datacharmer/test_db.git
sudo mysql -u root -p < test_db/employees.sqlHow To Install MySQL on Ubuntu 22.04 | DigitalOcean
-
mysql test db 다운로드
git clone <https://github.com/datacharmer/test_db.git> sudo mysql -u root -p < test_db/employees.sql
| Team 테이블 | team_id | name |
|---|---|---|
| 1 | go_for_it | |
| 2 | fighting | |
| 3 | team_blue | |
| 4 | team_red | |
| 5 | team_green |
- INNER JOIN: 두 테이블에서 일치하는 행만 반환
- LEFT JOIN: 왼쪽 테이블의 모든 행과 오른쪽 테이블의 일치하는 행 반환
- RIGHT JOIN: 오른쪽 테이블의 모든 행과 왼쪽 테이블의 일치하는 행 반환
- FULL OUTER JOIN: 양쪽 테이블의 모든 행 반환
- CROSS JOIN: 두 테이블의 모든 가능한 조합 생성
집계된 결과 사용:
- 서브쿼리를 사용하여 집계 함수의 결과를 메인 쿼리에서 사용할 수 있습니다.
- 예시: 각 직원의 월급이 평균 월급보다 높은 직원 목록을 가져올 때.
SELECT *
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);
- 서브쿼리를 사용하여 실행 순서에 맞지 않는 쿼리를 실행해야 하는 경우
- 집계 함수와 함께 사용하여 특정 조건을 만족하는 데이터를 필터링할 때
- GROUP BY와 HAVING 절을 사용하여 집계된 결과를 필터링할 때
- TOP N 분석을 수행할 때
- SELF JOIN을 통해 상위 레코드를 필터링할 때
상관 서브쿼리(Correlated Subquery):
- 메인 쿼리의 각 행에 대해 서브쿼리를 수행해야 하는 경우.
- 예시: 각 직원의 부서에서 최고 월급을 받는 사람의 정보를 가져올 때.
SELECT EmployeeID, FirstName, LastName
FROM Employees e1
WHERE Salary = (SELECT MAX(Salary) FROM Employees e2 WHERE e2.DepartmentID = e1.DepartmentID);
존재 여부 확인:
- 특정 조건에 맞는 행이 존재하는지 확인하기 위해 서브쿼리를 사용합니다.
- 예시: 어떤 부서에 직원이 있는지 확인할 때.
SELECT DepartmentName
FROM Departments
WHERE EXISTS (SELECT 1 FROM Employees WHERE Employees.DepartmentID = Departments.DepartmentID);
- 조건에 맞는 행 하나를 찾으면 종료되므로 join보다 빠를 수 있음
- JOIN 사용
WITH avg_salaries AS (
SELECT de.dept_no, AVG(s.salary) AS avg_salary
FROM salaries s
JOIN dept_emp de ON s.emp_no = de.emp_no
WHERE s.to_date = '9999-01-01'
AND de.to_date = '9999-01-01'
GROUP BY de.dept_no
)
SELECT e.emp_no, e.first_name, e.last_name, s.salary, d.dept_name
FROM employees e
JOIN salaries s ON e.emp_no = s.emp_no
JOIN dept_emp de ON e.emp_no = de.emp_no
JOIN departments d ON de.dept_no = d.dept_no
JOIN avg_salaries av ON de.dept_no = av.dept_no
WHERE s.to_date = '9999-01-01'
AND de.to_date = '9999-01-01'
AND s.salary > av.avg_salary;
물론입니다! 앞서 설명한 JOIN 이외에도 SQL에는 다양한 JOIN 유형이 있습니다. 여기서는 NATURAL JOIN, SELF JOIN, 그리고 USING 키워드를 이용한 JOIN에 대해 설명하겠습니다.
NATURAL JOIN은 두 테이블에서 동일한 이름을 가진 컬럼을 자동으로 사용하여 조인합니다. 조인 조건을 명시적으로 지정하지 않아도 됩니다.
목표: 직원의 이름과 부서명을 조회합니다. (단, 두 테이블에 동일한 이름을 가진 컬럼이 있어야 합니다.)
-- 두 테이블에 DepartmentID가 동일한 컬럼 이름을 가집니다.
SELECT e.EmployeeID, e.FirstName, e.LastName, d.DepartmentName
FROM Employees e
NATURAL JOIN Departments d;
결과:
EmployeeID | FirstName | LastName | DepartmentName
-----------|-----------|----------|----------------
1 | John | Doe | Engineering
2 | Jane | Smith | HR
3 | Bob | Johnson | Engineering
4 | Alice | Williams | Marketing
SELF JOIN은 동일한 테이블을 자기 자신과 조인하는 방법입니다. 주로 계층 구조나 동일 테이블 내의 비교를 위해 사용됩니다.
목표: 같은 부서에 있는 다른 직원의 정보를 조회합니다.
SELECT e1.EmployeeID AS Employee1ID, e1.FirstName AS Employee1Name, e2.EmployeeID AS Employee2ID, e2.FirstName AS Employee2Name
FROM Employees e1
INNER JOIN Employees e2 ON e1.DepartmentID = e2.DepartmentID AND e1.EmployeeID <> e2.EmployeeID;
결과:
Employee1ID | Employee1Name | Employee2ID | Employee2Name
------------|----------------|-------------|---------------
1 | John | 3 | Bob
3 | Bob | 1 | John
USING 키워드는 NATURAL JOIN과 유사하지만, 동일한 이름을 가진 컬럼을 명시적으로 지정하여 조인합니다.
목표: 직원의 이름과 부서명을 조회합니다.
SELECT e.EmployeeID, e.FirstName, e.LastName, d.DepartmentName
FROM Employees e
INNER JOIN Departments d USING (DepartmentID);
결과:
EmployeeID | FirstName | LastName | DepartmentName
-----------|-----------|----------|----------------
1 | John | Doe | Engineering
2 | Jane | Smith | HR
3 | Bob | Johnson | Engineering
4 | Alice | Williams | Marketing
SQL에서는 직접적으로 SEMI JOIN을 지원하지 않지만, EXISTS 키워드를 사용하여 SEMI JOIN과 동일한 효과를 낼 수 있습니다. SEMI JOIN은 왼쪽 테이블의 행이 오른쪽 테이블의 조건에 맞는 행과 일치할 때만 반환합니다.
목표: 프로젝트에 참여한 직원의 정보를 조회합니다.
SELECT e.EmployeeID, e.FirstName, e.LastName
FROM Employees e
WHERE EXISTS (
SELECT 1
FROM EmployeeProjects ep
WHERE e.EmployeeID = ep.EmployeeID
);
결과:
EmployeeID | FirstName | LastName
-----------|-----------|----------
1 | John | Doe
2 | Jane | Smith
3 | Bob | Johnson
4 | Alice | Williams
SQL에서 ANTI JOIN도 직접적으로 지원하지 않지만, NOT EXISTS 키워드를 사용하여 ANTI JOIN과 동일한 효과를 낼 수 있습니다. ANTI JOIN은 왼쪽 테이블의 행이 오른쪽 테이블의 조건과 일치하지 않는 경우에만 반환합니다.
목표: 프로젝트에 참여하지 않은 직원의 정보를 조회합니다.
SELECT e.EmployeeID, e.FirstName, e.LastName
FROM Employees e
WHERE NOT EXISTS (
SELECT 1
FROM EmployeeProjects ep
WHERE e.EmployeeID = ep.EmployeeID
);
결과:
EmployeeID | FirstName | LastName
-----------|-----------|----------
(No rows returned since all employees are part of at least one project)
- LEFT JOIN과 WHERE NULL 사용:
LEFT JOIN을 사용하여 두 테이블을 조인한 후, 오른쪽 테이블의 조인 컬럼이 NULL인 경우를 필터링하여 ANTI JOIN을 구현할 수 있습니다.
SELECT e.*
FROM Employees e
LEFT JOIN Departments d ON e.DepartmentID = d.DepartmentID
WHERE d.DepartmentID IS NULL;
드라이빙 테이블은 JOIN 시 먼저 액세스 되는 테이블을 의미합니다. (나중에 액세스 되는 테이블은 드리븐 테이블 or 이너테이블 이라고 합니다.)
조인을 할 때 성능상 이점을 얻기 위해 드라이빙 테이블을 선정하는게 중요합니다. 아래와 같은 테이블이 있다고 가정할 때
A테이블 : 5000만건의 ROW
B테이블 : 1000만건의 ROW
-
A 테이블이 드라이빙 테이블일때
A 테이블의 1번째 행의
no를 B테이블에서 찾아 매칭되는지 확인A 테이블의 2번째 행의
no를 B테이블에서 찾아 매칭되는지 확인A 테이블의 3번째 행의
no를 B테이블에서 찾아 매칭되는지 확인…
A 테이블의 5000만번째 행의
no를 B테이블에서 찾아 매칭되는지 확인 -
B 테이블이 드라이빙 테이블일때
1번과 마찬가지로 1000만번 A테이블을 조회합니다.
즉, 한 테이블을 5000만번 조회하는것과 1000만번 조회하는 것은 속도에서 큰 차이를 보이므로 ROW의 갯수가 더 적은 테이블을 드라이빙 테이블로 선정하는것이 유리합니다.
옵티마이저가 이런 부분들을 결정하는데, where A.no = B.no와 같은 쿼리를 발견하면 테이블의 인덱스 유무를 살핍니다.
인덱스가 있는 테이블이라면 굳이 풀 테이블 스캔을 하지 않아도 되기에 인덱스가 없는 테이블을 드라이빙 테이블로 선정하고, 인덱스가 있는 테이블을 드리븐 테이블로 선정하게 됩니다.
만약 양쪽 모두 인덱스가 있거나, 없으면 옵티마이저가 비용을 계산해보고 드라이빙 테이블을 결정하게 됩니다.
- 적절한 인덱스 설정: 조인 컬럼에 인덱스를 설정하면 쿼리 성능이 크게 향상됩니다. 인덱스는 조인 시 검색 속도를 높여줍니다.
인덱스가 너무 많을 때 발생할 수 있는 문제점
-
쓰기 성능 저하:
- INSERT, UPDATE, DELETE 작업 시 성능 저하: 각 인덱스는 데이터를 삽입하거나 업데이트할 때마다 함께 수정되어야 합니다. 따라서 인덱스가 많을수록 쓰기 작업의 성능이 저하됩니다.
- 예시: 한 테이블에 인덱스가 10개 있을 경우, 데이터를 삽입할 때마다 10개의 인덱스가 업데이트되어야 하므로 성능이 크게 저하될 수 있습니다.
-
디스크 공간 증가:
- 디스크 사용량 증가: 인덱스는 추가적인 디스크 공간을 차지합니다. 인덱스가 많아질수록 더 많은 디스크 공간이 필요하며, 이는 스토리지 비용 증가로 이어질 수 있습니다.
- 예시: 테이블이 수백만 행으로 커질수록 인덱스가 차지하는 공간도 상당해질 수 있습니다.
-
쿼리 최적화의 복잡성 증가:
- 쿼리 최적화 복잡성 증가: 너무 많은 인덱스가 있을 경우, 데이터베이스 옵티마이저가 최적의 인덱스를 선택하는 데 어려움을 겪을 수 있습니다. 이는 쿼리 성능의 예측 가능성을 낮춥니다.
- 예시: 비슷한 목적을 가진 여러 인덱스가 있을 때, 옵티마이저가 최적의 인덱스를 선택하는 데 시간이 더 걸릴 수 있습니다.
-
인덱스 유지 관리 비용 증가:
- 인덱스 유지 관리 비용 증가: 인덱스를 추가하고 수정하는 데 시간이 소요되며, 이는 데이터베이스의 유지 관리 비용을 증가시킵니다.
- 예시: 인덱스가 너무 많을 경우, 정기적인 인덱스 리빌드나 분석 작업이 더 오래 걸릴 수 있습니다.
인덱스가 없는 경우 발생할 수 있는 문제
-
검색 성능 저하:
-
전체 테이블 스캔: 인덱스가 없으면 데이터베이스는 조건에 맞는 데이터를 찾기 위해 전체 테이블을 스캔해야 합니다. 이는 큰 테이블의 경우 매우 느려질 수 있습니다.
-
예시: 인덱스 없이 특정 이름을 검색할 때
SELECT * FROM Employees WHERE first_name = 'John';이 경우, 데이터베이스는 테이블의 모든 행을 검사해야 합니다.
-
-
조인 성능 저하:
-
조인 성능 저하: 조인 컬럼에 인덱스가 없으면 데이터베이스는 조인 작업을 수행할 때도 전체 테이블을 스캔해야 합니다. 이는 조인 성능을 크게 저하시킬 수 있습니다.
-
예시: 인덱스 없이 두 테이블을 조인할 때
SELECT e.*, d.* FROM Employees e JOIN Departments d ON e.department_id = d.department_id;
-
-
집계 함수 성능 저하:
-
집계 함수 성능 저하: 인덱스가 없으면 집계 함수(예: COUNT, SUM, AVG)를 사용할 때도 전체 테이블을 스캔해야 하므로 성능이 저하됩니다.
-
예시: 인덱스 없이 부서별 직원 수를 계산할 때
SELECT department_id, COUNT(*) FROM Employees GROUP BY department_id;
-
-
정렬 성능 저하:
-
정렬 성능 저하: ORDER BY 절을 사용할 때 인덱스가 없으면 데이터베이스는 결과를 정렬하기 위해 모든 데이터를 메모리에 로드하고 정렬해야 합니다.
-
예시: 인덱스 없이 특정 컬럼으로 정렬할 때
SELECT * FROM Employees ORDER BY last_name;
-
- 테이블 스캔(Table Scan): 인덱스가 없을 때, 데이터베이스는 조인할 때 테이블의 모든 행을 순차적으로 읽습니다. 이를 "테이블 스캔"이라고 합니다. 테이블 스캔은 각 행을 읽고 조건에 맞는지를 검사하기 때문에 대규모 데이터셋에서는 매우 비효율적입니다.
- 네스티드 루프 조인(Nested Loop Join): 인덱스가 없는 경우, 데이터베이스는 일반적으로 네스티드 루프 조인을 사용합니다. 네스티드 루프 조인은 두 테이블을 중첩된 반복문으로 순회하면서 조건에 맞는 행을 찾습니다. 이는 작은 데이터셋에서는 괜찮지만, 대규모 데이터셋에서는 성능 저하를 초래합니다.
- 해시 조인(Hash Join): 인덱스가 없는 경우, 해시 조인을 사용할 수도 있습니다. 해시 조인은 하나의 테이블에 대해 해시 테이블을 생성하고, 다른 테이블의 각 행을 해시 테이블과 비교합니다. 해시 조인은 큰 데이터셋에서 비교적 효율적이지만, 여전히 인덱스를 사용한 조인보다는 느립니다.
[How To Install MySQL on Ubuntu 22.04 | DigitalOcean](https://www.digitalocean.com/community/tutorials/how-to-install-mysql-on-ubuntu-22-04)
-
mysql test db 다운로드
git clone https://github.com/datacharmer/test_db.git sudo mysql -u root -p < test_db/employees.sql
| Team 테이블 | team_id | name |
|---|---|---|
| 1 | go_for_it | |
| 2 | fighting | |
| 3 | team_blue | |
| 4 | team_red | |
| 5 | team_green |
| Member 테이블 | member_id | name | team_id |
|---|---|---|---|
| 1 | Liam | 1 | |
| 2 | Emma | 1 | |
| 3 | Noah | 3 | |
| 4 | Ava | 5 | |
| 5 | Mia | 5 | |
| 6 | Jack | 1 | |
| 7 | Ella | 1 |
-- Team 테이블 생성
CREATE TABLE Team (
team_id INT PRIMARY KEY,
name VARCHAR(100)
);
-- Team 테이블에 데이터 삽입
INSERT INTO Team (team_id, name) VALUES
(1, 'go_for_it'),
(2, 'fighting'),
(3, 'team_blue'),
(4, 'team_red'),
(5, 'team_green');
-- Member 테이블 생성
CREATE TABLE Member (
member_id INT PRIMARY KEY,
name VARCHAR(100),
team_id INT,
FOREIGN KEY (team_id) REFERENCES Team(team_id)
);
-- Member 테이블에 데이터 삽입
INSERT INTO Member (member_id, name, team_id) VALUES
(1, 'Liam', 1),
(2, 'Emma', 1),
(3, 'Noah', 3),
(4, 'Ava', 5),
(5, 'Mia', 5),
(6, 'Jack', 1),
(7, 'Ella', 1);SELECT Member.member_id, Member.name AS member_name, Team.name AS team_name
FROM Member
INNER JOIN Team ON Member.team_id = Team.team_id;
결과:
| member_id | member_name | team_name |
|---|---|---|
| 1 | Liam | go_for_it |
| 2 | Emma | go_for_it |
| 3 | Noah | team_blue |
| 4 | Ava | team_green |
| 5 | Mia | team_green |
| 6 | Jack | go_for_it |
| 7 | Ella | go_for_it |
SELECT Team.team_id, Team.name AS team_name, Member.name AS member_name
FROM Team
LEFT JOIN Member ON Team.team_id = Member.team_id;
결과:
| team_id | team_name | member_name |
|---|---|---|
| 1 | go_for_it | Liam |
| 1 | go_for_it | Emma |
| 1 | go_for_it | Jack |
| 1 | go_for_it | Ella |
| 2 | fighting | NULL |
| 3 | team_blue | Noah |
| 4 | team_red | NULL |
| 5 | team_green | Ava |
| 5 | team_green | Mia |
SELECT Member.member_id, Member.name AS member_name, Team.name AS team_name
FROM Member
RIGHT JOIN Team ON Member.team_id = Team.team_id;
결과:
| member_id | member_name | team_name |
|---|---|---|
| 1 | Liam | go_for_it |
| 2 | Emma | go_for_it |
| 6 | Jack | go_for_it |
| 7 | Ella | go_for_it |
| NULL | NULL | fighting |
| 3 | Noah | team_blue |
| NULL | NULL | team_red |
| 4 | Ava | team_green |
| 5 | Mia | team_green |
MySQL에서는 직접적인 FULL OUTER JOIN을 지원하지 않으므로, LEFT JOIN과 RIGHT JOIN의 UNION으로 구현합니다.
SELECT Team.team_id, Team.name AS team_name, Member.name AS member_name
FROM Team
LEFT JOIN Member ON Team.team_id = Member.team_id
UNION
SELECT Team.team_id, Team.name AS team_name, Member.name AS member_name
FROM Team
RIGHT JOIN Member ON Team.team_id = Member.team_id;
결과:
| team_id | team_name | member_name |
|---|---|---|
| 1 | go_for_it | Liam |
| 1 | go_for_it | Emma |
| 1 | go_for_it | Jack |
| 1 | go_for_it | Ella |
| 2 | fighting | NULL |
| 3 | team_blue | Noah |
| 4 | team_red | NULL |
| 5 | team_green | Ava |
| 5 | team_green | Mia |
SELECT Member.member_id, Member.name AS member_name, Team.name AS team_name
FROM Member
CROSS JOIN Team
LIMIT 10; -- 결과를 10개로 제한
결과:
| member_id | member_name | team_name |
|---|---|---|
| 1 | Liam | go_for_it |
| 1 | Liam | fighting |
| 1 | Liam | team_blue |
| 1 | Liam | team_red |
| 1 | Liam | team_green |
| 2 | Emma | go_for_it |
| 2 | Emma | fighting |
| 2 | Emma | team_blue |
| 2 | Emma | team_red |
| 2 | Emma | team_green |
- INNER JOIN: 두 테이블에서 일치하는 행만 반환
- LEFT JOIN: 왼쪽 테이블의 모든 행과 오른쪽 테이블의 일치하는 행 반환
- RIGHT JOIN: 오른쪽 테이블의 모든 행과 왼쪽 테이블의 일치하는 행 반환
- FULL OUTER JOIN: 양쪽 테이블의 모든 행 반환
- CROSS JOIN: 두 테이블의 모든 가능한 조합 생성
집계된 결과 사용:
- 서브쿼리를 사용하여 집계 함수의 결과를 메인 쿼리에서 사용할 수 있습니다.
- 예시: 각 직원의 월급이 평균 월급보다 높은 직원 목록을 가져올 때.
SELECT *
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);- 서브쿼리를 사용하여 실행 순서에 맞지 않는 쿼리를 실행해야 하는 경우
- 집계 함수와 함께 사용하여 특정 조건을 만족하는 데이터를 필터링할 때
- GROUP BY와 HAVING 절을 사용하여 집계된 결과를 필터링할 때
- TOP N 분석을 수행할 때
- SELF JOIN을 통해 상위 레코드를 필터링할 때
상관 서브쿼리(Correlated Subquery):
- 메인 쿼리의 각 행에 대해 서브쿼리를 수행해야 하는 경우.
- 예시: 각 직원의 부서에서 최고 월급을 받는 사람의 정보를 가져올 때.
SELECT EmployeeID, FirstName, LastName
FROM Employees e1
WHERE Salary = (SELECT MAX(Salary) FROM Employees e2 WHERE e2.DepartmentID = e1.DepartmentID);존재 여부 확인:
- 특정 조건에 맞는 행이 존재하는지 확인하기 위해 서브쿼리를 사용합니다.
- 예시: 어떤 부서에 직원이 있는지 확인할 때.
SELECT DepartmentName
FROM Departments
WHERE EXISTS (SELECT 1 FROM Employees WHERE Employees.DepartmentID = Departments.DepartmentID);- 조건에 맞는 행 하나를 찾으면 종료되므로 join보다 빠를 수 있음
- JOIN 사용
WITH avg_salaries AS (
SELECT de.dept_no, AVG(s.salary) AS avg_salary
FROM salaries s
JOIN dept_emp de ON s.emp_no = de.emp_no
WHERE s.to_date = '9999-01-01'
AND de.to_date = '9999-01-01'
GROUP BY de.dept_no
)
SELECT e.emp_no, e.first_name, e.last_name, s.salary, d.dept_name
FROM employees e
JOIN salaries s ON e.emp_no = s.emp_no
JOIN dept_emp de ON e.emp_no = de.emp_no
JOIN departments d ON de.dept_no = d.dept_no
JOIN avg_salaries av ON de.dept_no = av.dept_no
WHERE s.to_date = '9999-01-01'
AND de.to_date = '9999-01-01'
AND s.salary > av.avg_salary;물론입니다! 앞서 설명한 JOIN 이외에도 SQL에는 다양한 JOIN 유형이 있습니다. 여기서는 NATURAL JOIN, SELF JOIN, 그리고 USING 키워드를 이용한 JOIN에 대해 설명하겠습니다.
NATURAL JOIN은 두 테이블에서 동일한 이름을 가진 컬럼을 자동으로 사용하여 조인합니다. 조인 조건을 명시적으로 지정하지 않아도 됩니다.
목표: 직원의 이름과 부서명을 조회합니다. (단, 두 테이블에 동일한 이름을 가진 컬럼이 있어야 합니다.)
-- 두 테이블에 DepartmentID가 동일한 컬럼 이름을 가집니다.
SELECT e.EmployeeID, e.FirstName, e.LastName, d.DepartmentName
FROM Employees e
NATURAL JOIN Departments d;
결과:
EmployeeID | FirstName | LastName | DepartmentName
-----------|-----------|----------|----------------
1 | John | Doe | Engineering
2 | Jane | Smith | HR
3 | Bob | Johnson | Engineering
4 | Alice | Williams | Marketing
SELF JOIN은 동일한 테이블을 자기 자신과 조인하는 방법입니다. 주로 계층 구조나 동일 테이블 내의 비교를 위해 사용됩니다.
목표: 같은 부서에 있는 다른 직원의 정보를 조회합니다.
SELECT e1.EmployeeID AS Employee1ID, e1.FirstName AS Employee1Name, e2.EmployeeID AS Employee2ID, e2.FirstName AS Employee2Name
FROM Employees e1
INNER JOIN Employees e2 ON e1.DepartmentID = e2.DepartmentID AND e1.EmployeeID <> e2.EmployeeID;
결과:
Employee1ID | Employee1Name | Employee2ID | Employee2Name
------------|----------------|-------------|---------------
1 | John | 3 | Bob
3 | Bob | 1 | John
USING 키워드는 NATURAL JOIN과 유사하지만, 동일한 이름을 가진 컬럼을 명시적으로 지정하여 조인합니다.
목표: 직원의 이름과 부서명을 조회합니다.
SELECT e.EmployeeID, e.FirstName, e.LastName, d.DepartmentName
FROM Employees e
INNER JOIN Departments d USING (DepartmentID);
결과:
EmployeeID | FirstName | LastName | DepartmentName
-----------|-----------|----------|----------------
1 | John | Doe | Engineering
2 | Jane | Smith | HR
3 | Bob | Johnson | Engineering
4 | Alice | Williams | Marketing
SQL에서는 직접적으로 SEMI JOIN을 지원하지 않지만, EXISTS 키워드를 사용하여 SEMI JOIN과 동일한 효과를 낼 수 있습니다. SEMI JOIN은 왼쪽 테이블의 행이 오른쪽 테이블의 조건에 맞는 행과 일치할 때만 반환합니다.
목표: 프로젝트에 참여한 직원의 정보를 조회합니다.
SELECT e.EmployeeID, e.FirstName, e.LastName
FROM Employees e
WHERE EXISTS (
SELECT 1
FROM EmployeeProjects ep
WHERE e.EmployeeID = ep.EmployeeID
);결과:
EmployeeID | FirstName | LastName
-----------|-----------|----------
1 | John | Doe
2 | Jane | Smith
3 | Bob | Johnson
4 | Alice | Williams
SQL에서 ANTI JOIN도 직접적으로 지원하지 않지만, NOT EXISTS 키워드를 사용하여 ANTI JOIN과 동일한 효과를 낼 수 있습니다. ANTI JOIN은 왼쪽 테이블의 행이 오른쪽 테이블의 조건과 일치하지 않는 경우에만 반환합니다.
목표: 프로젝트에 참여하지 않은 직원의 정보를 조회합니다.
SELECT e.EmployeeID, e.FirstName, e.LastName
FROM Employees e
WHERE NOT EXISTS (
SELECT 1
FROM EmployeeProjects ep
WHERE e.EmployeeID = ep.EmployeeID
);
결과:
EmployeeID | FirstName | LastName
-----------|-----------|----------
(No rows returned since all employees are part of at least one project)
- LEFT JOIN과 WHERE NULL 사용:
LEFT JOIN을 사용하여 두 테이블을 조인한 후, 오른쪽 테이블의 조인 컬럼이 NULL인 경우를 필터링하여 ANTI JOIN을 구현할 수 있습니다.
SELECT e.*
FROM Employees e
LEFT JOIN Departments d ON e.DepartmentID = d.DepartmentID
WHERE d.DepartmentID IS NULL;드라이빙 테이블은 JOIN 시 먼저 액세스 되는 테이블을 의미합니다. (나중에 액세스 되는 테이블은 드리븐 테이블 or 이너테이블 이라고 합니다.)
조인을 할 때 성능상 이점을 얻기 위해 드라이빙 테이블을 선정하는게 중요합니다. 아래와 같은 테이블이 있다고 가정할 때
A테이블 : 5000만건의 ROW
B테이블 : 1000만건의 ROW-
A 테이블이 드라이빙 테이블일때
A 테이블의 1번째 행의
no를 B테이블에서 찾아 매칭되는지 확인A 테이블의 2번째 행의
no를 B테이블에서 찾아 매칭되는지 확인A 테이블의 3번째 행의
no를 B테이블에서 찾아 매칭되는지 확인…
A 테이블의 5000만번째 행의
no를 B테이블에서 찾아 매칭되는지 확인 -
B 테이블이 드라이빙 테이블일때
1번과 마찬가지로 1000만번 A테이블을 조회합니다.
즉, 한 테이블을 5000만번 조회하는것과 1000만번 조회하는 것은 속도에서 큰 차이를 보이므로 ROW의 갯수가 더 적은 테이블을 드라이빙 테이블로 선정하는것이 유리합니다.
옵티마이저가 이런 부분들을 결정하는데, where [A.no](http://A.no) = B.no와 같은 쿼리를 발견하면 테이블의 인덱스 유무를 살핍니다.
인덱스가 있는 테이블이라면 굳이 풀 테이블 스캔을 하지 않아도 되기에 인덱스가 없는 테이블을 드라이빙 테이블로 선정하고, 인덱스가 있는 테이블을 드리븐 테이블로 선정하게 됩니다.
만약 양쪽 모두 인덱스가 있거나, 없으면 옵티마이저가 비용을 계산해보고 드라이빙 테이블을 결정하게 됩니다.
- 적절한 인덱스 설정: 조인 컬럼에 인덱스를 설정하면 쿼리 성능이 크게 향상됩니다. 인덱스는 조인 시 검색 속도를 높여줍니다.
인덱스가 너무 많을 때 발생할 수 있는 문제점
-
쓰기 성능 저하:
- INSERT, UPDATE, DELETE 작업 시 성능 저하: 각 인덱스는 데이터를 삽입하거나 업데이트할 때마다 함께 수정되어야 합니다. 따라서 인덱스가 많을수록 쓰기 작업의 성능이 저하됩니다.
- 예시: 한 테이블에 인덱스가 10개 있을 경우, 데이터를 삽입할 때마다 10개의 인덱스가 업데이트되어야 하므로 성능이 크게 저하될 수 있습니다.
-
디스크 공간 증가:
- 디스크 사용량 증가: 인덱스는 추가적인 디스크 공간을 차지합니다. 인덱스가 많아질수록 더 많은 디스크 공간이 필요하며, 이는 스토리지 비용 증가로 이어질 수 있습니다.
- 예시: 테이블이 수백만 행으로 커질수록 인덱스가 차지하는 공간도 상당해질 수 있습니다.
-
쿼리 최적화의 복잡성 증가:
- 쿼리 최적화 복잡성 증가: 너무 많은 인덱스가 있을 경우, 데이터베이스 옵티마이저가 최적의 인덱스를 선택하는 데 어려움을 겪을 수 있습니다. 이는 쿼리 성능의 예측 가능성을 낮춥니다.
- 예시: 비슷한 목적을 가진 여러 인덱스가 있을 때, 옵티마이저가 최적의 인덱스를 선택하는 데 시간이 더 걸릴 수 있습니다.
-
인덱스 유지 관리 비용 증가:
- 인덱스 유지 관리 비용 증가: 인덱스를 추가하고 수정하는 데 시간이 소요되며, 이는 데이터베이스의 유지 관리 비용을 증가시킵니다.
- 예시: 인덱스가 너무 많을 경우, 정기적인 인덱스 리빌드나 분석 작업이 더 오래 걸릴 수 있습니다.
인덱스가 없는 경우 발생할 수 있는 문제
-
검색 성능 저하:
-
전체 테이블 스캔: 인덱스가 없으면 데이터베이스는 조건에 맞는 데이터를 찾기 위해 전체 테이블을 스캔해야 합니다. 이는 큰 테이블의 경우 매우 느려질 수 있습니다.
-
예시: 인덱스 없이 특정 이름을 검색할 때
SELECT * FROM Employees WHERE first_name = 'John';
이 경우, 데이터베이스는 테이블의 모든 행을 검사해야 합니다.
-
-
조인 성능 저하:
-
조인 성능 저하: 조인 컬럼에 인덱스가 없으면 데이터베이스는 조인 작업을 수행할 때도 전체 테이블을 스캔해야 합니다. 이는 조인 성능을 크게 저하시킬 수 있습니다.
-
예시: 인덱스 없이 두 테이블을 조인할 때
SELECT e.*, d.* FROM Employees e JOIN Departments d ON e.department_id = d.department_id;
-
-
집계 함수 성능 저하:
-
집계 함수 성능 저하: 인덱스가 없으면 집계 함수(예: COUNT, SUM, AVG)를 사용할 때도 전체 테이블을 스캔해야 하므로 성능이 저하됩니다.
-
예시: 인덱스 없이 부서별 직원 수를 계산할 때
SELECT department_id, COUNT(*) FROM Employees GROUP BY department_id;
-
-
정렬 성능 저하:
-
정렬 성능 저하: ORDER BY 절을 사용할 때 인덱스가 없으면 데이터베이스는 결과를 정렬하기 위해 모든 데이터를 메모리에 로드하고 정렬해야 합니다.
-
예시: 인덱스 없이 특정 컬럼으로 정렬할 때
SELECT * FROM Employees ORDER BY last_name;
-
- 테이블 스캔(Table Scan): 인덱스가 없을 때, 데이터베이스는 조인할 때 테이블의 모든 행을 순차적으로 읽습니다. 이를 "테이블 스캔"이라고 합니다. 테이블 스캔은 각 행을 읽고 조건에 맞는지를 검사하기 때문에 대규모 데이터셋에서는 매우 비효율적입니다.
- 네스티드 루프 조인(Nested Loop Join): 인덱스가 없는 경우, 데이터베이스는 일반적으로 네스티드 루프 조인을 사용합니다. 네스티드 루프 조인은 두 테이블을 중첩된 반복문으로 순회하면서 조건에 맞는 행을 찾습니다. 이는 작은 데이터셋에서는 괜찮지만, 대규모 데이터셋에서는 성능 저하를 초래합니다.
- 해시 조인(Hash Join): 인덱스가 없는 경우, 해시 조인을 사용할 수도 있습니다. 해시 조인은 하나의 테이블에 대해 해시 테이블을 생성하고, 다른 테이블의 각 행을 해시 테이블과 비교합니다. 해시 조인은 큰 데이터셋에서 비교적 효율적이지만, 여전히 인덱스를 사용한 조인보다는 느립니다.