02_joins.sql - Examples

  1-- =============================================================================
  2-- PostgreSQL JOIN 예제
  3-- Various Types of JOINs in PostgreSQL
  4-- =============================================================================
  5
  6-- 먼저 01_basic_crud.sql을 실행하여 테이블과 데이터를 생성하세요.
  7
  8-- =============================================================================
  9-- 테스트 데이터 추가
 10-- =============================================================================
 11
 12-- 부서가 없는 직원 추가 (NULL dept_id)
 13INSERT INTO employees (first_name, last_name, email, salary, dept_id)
 14VALUES ('무소속', '직원', 'nodept@company.com', 40000, NULL);
 15
 16-- 직원이 없는 부서 추가
 17INSERT INTO departments (dept_name, location)
 18VALUES ('Finance', 'Seoul');
 19
 20-- =============================================================================
 21-- 1. INNER JOIN
 22-- =============================================================================
 23-- 양쪽 테이블에서 매칭되는 행만 반환
 24
 25SELECT
 26    e.emp_id,
 27    e.first_name,
 28    e.last_name,
 29    e.salary,
 30    d.dept_name,
 31    d.location
 32FROM employees e
 33INNER JOIN departments d ON e.dept_id = d.dept_id;
 34
 35-- 테이블 별칭 없이
 36SELECT
 37    employees.first_name,
 38    employees.last_name,
 39    departments.dept_name
 40FROM employees
 41INNER JOIN departments ON employees.dept_id = departments.dept_id;
 42
 43-- =============================================================================
 44-- 2. LEFT JOIN (LEFT OUTER JOIN)
 45-- =============================================================================
 46-- 왼쪽 테이블의 모든 행 + 매칭되는 오른쪽 테이블 행
 47
 48SELECT
 49    e.emp_id,
 50    e.first_name,
 51    e.last_name,
 52    e.salary,
 53    d.dept_name,
 54    d.location
 55FROM employees e
 56LEFT JOIN departments d ON e.dept_id = d.dept_id;
 57
 58-- 부서가 없는 직원만
 59SELECT
 60    e.first_name,
 61    e.last_name
 62FROM employees e
 63LEFT JOIN departments d ON e.dept_id = d.dept_id
 64WHERE d.dept_id IS NULL;
 65
 66-- =============================================================================
 67-- 3. RIGHT JOIN (RIGHT OUTER JOIN)
 68-- =============================================================================
 69-- 오른쪽 테이블의 모든 행 + 매칭되는 왼쪽 테이블 행
 70
 71SELECT
 72    e.emp_id,
 73    e.first_name,
 74    e.last_name,
 75    d.dept_name,
 76    d.location
 77FROM employees e
 78RIGHT JOIN departments d ON e.dept_id = d.dept_id;
 79
 80-- 직원이 없는 부서만
 81SELECT
 82    d.dept_name,
 83    d.location
 84FROM employees e
 85RIGHT JOIN departments d ON e.dept_id = d.dept_id
 86WHERE e.emp_id IS NULL;
 87
 88-- =============================================================================
 89-- 4. FULL OUTER JOIN
 90-- =============================================================================
 91-- 양쪽 테이블의 모든 행 (매칭되지 않으면 NULL)
 92
 93SELECT
 94    e.emp_id,
 95    e.first_name,
 96    e.last_name,
 97    d.dept_name,
 98    d.location
 99FROM employees e
100FULL OUTER JOIN departments d ON e.dept_id = d.dept_id;
101
102-- 매칭되지 않는 행만
103SELECT
104    e.emp_id,
105    e.first_name,
106    d.dept_name
107FROM employees e
108FULL OUTER JOIN departments d ON e.dept_id = d.dept_id
109WHERE e.emp_id IS NULL OR d.dept_id IS NULL;
110
111-- =============================================================================
112-- 5. CROSS JOIN (카테시안 곱)
113-- =============================================================================
114-- 모든 가능한 조합
115
116SELECT
117    e.first_name,
118    d.dept_name
119FROM employees e
120CROSS JOIN departments d
121LIMIT 20;
122
123-- CROSS JOIN은 ON 절 없이 콤마로도 표현 가능
124SELECT
125    e.first_name,
126    d.dept_name
127FROM employees e, departments d
128WHERE e.dept_id IS NOT NULL
129LIMIT 20;
130
131-- =============================================================================
132-- 6. SELF JOIN
133-- =============================================================================
134-- 같은 테이블을 자기 자신과 조인
135
136-- 예제를 위한 관리자 컬럼 추가
137ALTER TABLE employees ADD COLUMN IF NOT EXISTS manager_id INTEGER REFERENCES employees(emp_id);
138
139-- 일부 직원에게 관리자 지정
140UPDATE employees SET manager_id = 4 WHERE emp_id IN (1, 2);
141UPDATE employees SET manager_id = 1 WHERE emp_id IN (5, 6);
142
143-- 직원과 관리자 조회
144SELECT
145    e.emp_id,
146    e.first_name || ' ' || e.last_name AS employee_name,
147    m.first_name || ' ' || m.last_name AS manager_name
148FROM employees e
149LEFT JOIN employees m ON e.manager_id = m.emp_id;
150
151-- =============================================================================
152-- 7. 다중 테이블 JOIN
153-- =============================================================================
154
155-- 프로젝트 테이블 생성
156DROP TABLE IF EXISTS projects CASCADE;
157DROP TABLE IF EXISTS employee_projects CASCADE;
158
159CREATE TABLE projects (
160    project_id SERIAL PRIMARY KEY,
161    project_name VARCHAR(100) NOT NULL,
162    start_date DATE,
163    end_date DATE,
164    budget NUMERIC(12, 2)
165);
166
167-- 직원-프로젝트 연결 테이블 (다대다 관계)
168CREATE TABLE employee_projects (
169    emp_id INTEGER REFERENCES employees(emp_id),
170    project_id INTEGER REFERENCES projects(project_id),
171    role VARCHAR(50),
172    PRIMARY KEY (emp_id, project_id)
173);
174
175-- 데이터 삽입
176INSERT INTO projects (project_name, start_date, end_date, budget)
177VALUES
178    ('웹사이트 리뉴얼', '2024-01-01', '2024-06-30', 100000),
179    ('모바일 앱 개발', '2024-03-01', '2024-12-31', 200000),
180    ('데이터 분석 플랫폼', '2024-02-01', '2024-08-31', 150000);
181
182INSERT INTO employee_projects (emp_id, project_id, role)
183VALUES
184    (1, 1, 'Lead'),
185    (2, 1, 'Developer'),
186    (1, 2, 'Developer'),
187    (3, 2, 'Lead'),
188    (4, 3, 'Lead'),
189    (2, 3, 'Analyst');
190
191-- 3개 테이블 조인: 직원 + 부서 + 프로젝트
192SELECT
193    e.first_name || ' ' || e.last_name AS employee_name,
194    d.dept_name,
195    p.project_name,
196    ep.role
197FROM employees e
198JOIN departments d ON e.dept_id = d.dept_id
199JOIN employee_projects ep ON e.emp_id = ep.emp_id
200JOIN projects p ON ep.project_id = p.project_id
201ORDER BY e.first_name, p.project_name;
202
203-- =============================================================================
204-- 8. NATURAL JOIN
205-- =============================================================================
206-- 같은 이름의 컬럼으로 자동 조인 (사용 비권장 - 명시적 ON 절 권장)
207
208-- dept_id가 같은 이름이므로 자동 매칭
209-- SELECT * FROM employees NATURAL JOIN departments;
210
211-- =============================================================================
212-- 9. USING 절
213-- =============================================================================
214-- 같은 이름의 컬럼이 있을 때 ON 대신 사용
215
216SELECT
217    e.first_name,
218    e.last_name,
219    d.dept_name
220FROM employees e
221JOIN departments d USING (dept_id);
222
223-- =============================================================================
224-- 10. 조인 + 집계
225-- =============================================================================
226
227-- 부서별 직원 수와 평균 급여
228SELECT
229    d.dept_name,
230    COUNT(e.emp_id) AS employee_count,
231    ROUND(AVG(e.salary), 2) AS avg_salary,
232    MIN(e.salary) AS min_salary,
233    MAX(e.salary) AS max_salary
234FROM departments d
235LEFT JOIN employees e ON d.dept_id = e.dept_id
236GROUP BY d.dept_id, d.dept_name
237ORDER BY employee_count DESC;
238
239-- 프로젝트별 참여 직원 수
240SELECT
241    p.project_name,
242    COUNT(ep.emp_id) AS member_count,
243    p.budget
244FROM projects p
245LEFT JOIN employee_projects ep ON p.project_id = ep.project_id
246GROUP BY p.project_id, p.project_name, p.budget
247ORDER BY member_count DESC;
248
249-- =============================================================================
250-- 11. 조인 성능 팁
251-- =============================================================================
252
253-- 조인에 사용되는 컬럼에 인덱스가 있는지 확인
254-- CREATE INDEX idx_employees_dept ON employees(dept_id);
255-- CREATE INDEX idx_employee_projects_emp ON employee_projects(emp_id);
256-- CREATE INDEX idx_employee_projects_proj ON employee_projects(project_id);
257
258-- 실행 계획 확인
259EXPLAIN ANALYZE
260SELECT
261    e.first_name,
262    d.dept_name
263FROM employees e
264JOIN departments d ON e.dept_id = d.dept_id;
265
266-- =============================================================================
267-- JOIN 유형 요약
268-- =============================================================================
269/*
270| JOIN 유형        | 설명                                      |
271|-----------------|-------------------------------------------|
272| INNER JOIN      | 양쪽 모두 매칭되는 행만                      |
273| LEFT JOIN       | 왼쪽 테이블 전체 + 매칭되는 오른쪽           |
274| RIGHT JOIN      | 오른쪽 테이블 전체 + 매칭되는 왼쪽           |
275| FULL OUTER JOIN | 양쪽 테이블 전체                            |
276| CROSS JOIN      | 모든 가능한 조합 (카테시안 곱)               |
277| SELF JOIN       | 같은 테이블끼리 조인                         |
278
279팁:
280- 항상 ON 절을 명시적으로 작성
281- 조인 컬럼에 인덱스 생성
282- EXPLAIN으로 실행 계획 확인
283- 불필요한 CROSS JOIN 피하기
284*/