다중 테이블 조인의 핵심 원리와 실전 적용

데이터베이스 수업의 기말 시험에서 자주 출제되는 주제 중 하나는 emp (직원 정보)와 dept (부서 정보) 테이블 간의 조인 연산이다. 이 두 테이블은 외래키(deptno)를 통해 연결되며, 다양한 조건에 따라 결과가 달라진다.

기본 테이블 구조

• emp(empno, ename, job, mgr, hiredate, sal, comm, deptno)
• dept(deptno, dname, loc)

조인 유형별 특성 비교

조인 문법 구조

SELECT [필드]
FROM 테이블1
[INNER | LEFT | RIGHT] JOIN 테이블2
ON 테이블1.필드 = 테이블2.필드;

실제 사례 분석

1. 내부 조인 (INNER JOIN)

부서 정보가 존재하는 직원들의 이름과 부서명 조회:

SELECT e.ename, d.dname
FROM emp e
INNER JOIN dept d ON e.deptno = d.deptno;

2. 좌측 외부 조인 (LEFT JOIN)

모든 직원을 포함하여 소속 부서 정보 표시 (미배치 직원도 포함):

SELECT e.ename, d.dname
FROM emp e
LEFT JOIN dept d ON e.deptno = d.deptno;

→ 질문에서 "모든 직원"을 요구할 경우 반드시 LEFT JOIN 사용.

3. 우측 외부 조인 (RIGHT JOIN)

모든 부서를 포함하고, 해당 부서에 소속된 직원 정보 출력:

SELECT e.ename, d.dname
FROM emp e
RIGHT JOIN dept d ON e.deptno = d.deptno;

→ 실제 개발에서는 LEFT JOIN로 표현하는 것이 일반적.

4. UNION 결합 연산

판매사원과 관리자 이름을 중복 없이 정렬하여 출력:

SELECT ename
FROM emp
WHERE job = 'SALESMAN'
UNION
SELECT ename
FROM emp
WHERE job = 'MANAGER'
ORDER BY ename;

→ UNION은 중복 제거, UNION ALL은 유지 → 성능 고려 시 선택.

시험에서 자주 등장하는 오류 패턴

• JOIN 대신 WHERE 사용: WHERE는 내부 조인처럼 작동하지만 외부 조인은 불가능.
• LEFT/RIGHT 조인의 주체 혼동: "모든 A"라는 표현이 나오면, A가 주테이블.
• UNION의 컬럼 수 또는 타입 불일치: 모든 쿼리의 열 수와 데이터 타입이 동일해야 함.
• ON 조건 누락 → 카티지언 곱 발생: 필수 조건이며, 생략 시 예상치 못한 결과 발생.

실전 문제 해결

문제 1: 모든 부서와 그 부서에 속한 직원 이름 출력 (없으면 이름은 NULL)

SELECT d.dname, e.ename
FROM dept d
LEFT JOIN emp e ON d.deptno = e.deptno;

문제 2: 직원이 없는 부서 이름과 위치 찾기

SELECT d.dname, d.loc
FROM dept d
LEFT JOIN emp e ON d.deptno = e.deptno
WHERE e.empno IS NULL;

문제 3: 평균 급여 계산 (직원 없을 경우 0으로 표시)

SELECT 
    d.dname,
    COALESCE(AVG(e.sal), 0) AS avg_salary
FROM dept d
LEFT JOIN emp e ON d.deptno = e.deptno
GROUP BY d.deptno, d.dname;

문제 4: 하위 직원이 없는 관리자 찾기

SELECT e1.ename
FROM emp e1
LEFT JOIN emp e2 ON e1.empno = e2.mgr
WHERE e2.empno IS NULL;

문제 5: 조건을 ON vs WHERE에 두는 차이

-- 잘못된 예: 조건을 WHERE에 두면 외부 조인이 내부 조인처럼 작동
SELECT * FROM emp e
LEFT JOIN dept d ON e.deptno = d.deptno
WHERE d.loc = 'NEW YORK';

-- 올바른 예: 조건을 ON에 포함하면 부서 정보가 없어도 직원은 유지됨
SELECT * FROM emp e
LEFT JOIN dept d ON e.deptno = d.deptno AND d.loc = 'NEW YORK';

고득점 전략 요약

• "모든 ~"이라는 표현이 있다면, 해당 테이블을 좌측에 배치하고 LEFT JOIN 사용.
• "존재하지 않는 항목"을 찾을 때는 LEFT JOIN + WHERE 우측 키 = NULL.
• 중복 제거 필요 시 UNION, 성능 우선 시 UNION ALL.
• 연결 조건은 ON에, 필터링 조건은 상황에 따라 ON 또는 WHERE에 배치.