SQL 실전 데이터 분석: 집계·순위·재귀 CTE·EXISTS 활용법

SQL 급여 분석 실습: 부서 평균·동점 순위·재귀 CTE 조직도 조회#

1장. 평균 급여가 틀렸다는 문의는 SQL 함수보다 대상 정의에서 시작된다#

인사팀에서 문의가 들어왔습니다.

개발부 평균 급여가 보고서마다 다릅니다.

SQL을 확인해 보니 두 보고서 모두 AVG(salary)를 사용하고 있습니다.

문제는 함수가 아니었습니다.

한 보고서는 퇴직자를 제외했고 다른 보고서는 퇴직자를 포함했습니다.

같은 AVG()를 사용해도 입력 데이터가 다르면 결과는 달라집니다.

SQL 실무에서 가장 먼저 확인해야 하는 것은 다음입니다.

어떤 행을 계산에 포함하는가?

한 행은 무엇을 의미하는가?

무엇을 기준으로 그룹을 만드는가?

최종 결과는 직원 단위인가 부서 단위인가?

SQL 문법보다 먼저 이 질문을 정해야 합니다.

2장. 모든 실습에 사용할 직원 데이터를 만들자#

세 개의 부서와 일곱 명의 직원을 사용하겠습니다.

CREATE TABLE department (
    dept_id integer PRIMARY KEY,
    dept_name text NOT NULL
);

CREATE TABLE employee (
    emp_id integer PRIMARY KEY,
    emp_name text NOT NULL,
    dept_id integer REFERENCES department(dept_id),
    salary integer NOT NULL,
    mgr_id integer REFERENCES employee(emp_id),
    retire_date date
);

부서를 입력합니다.

INSERT INTO department
VALUES
    (10, '개발'),
    (20, '영업'),
    (30, '인사');

직원을 입력합니다.

INSERT INTO employee
VALUES
    (1, '가람', 10, 600, NULL, NULL),
    (2, '나래', 10, 500, 1, NULL),
    (3, '다온', 10, 500, 1, NULL),
    (4, '라온', 20, 300, 1, NULL),
    (5, '마루', 20, 100, 4, NULL),
    (6, '바다', NULL, 400, 1, NULL),
    (7, '사라', 20, 900, 4, DATE '2026-01-01');

3장. 일부러 예외 데이터를 넣은 이유#

이 데이터에는 정상적인 직원만 넣지 않았습니다.

다음과 같은 경계 사례가 있습니다.

나래와 다온
→ 급여가 500으로 동점

바다
→ 부서 미배정

사라
→ 퇴직자

인사부
→ 현재 재직 직원 0명

이런 데이터가 있어야 SQL의 의미를 제대로 확인할 수 있습니다.

정상 데이터만으로 테스트하면:

NULL

동점

빈 그룹

퇴직자

계층 이상

같은 문제를 놓치기 쉽습니다.

4장. 먼저 사람이 직접 기대값을 계산하자#

현재 재직자는:

가람 600

나래 500

다온 500

라온 300

마루 100

바다 400

총 6명입니다.

합계:

600 + 500 + 500 + 300 + 100 + 400
=
2,400

전체 평균:

2,400 / 6
=
400

입니다.

이 값을 SQL 결과의 검산 기준으로 사용합니다.

5장. 부서별 평균도 직접 계산해 보자#

개발부#

600 + 500 + 500
=
1,600

직원 수:

3명

평균:

1,600 / 3
≈
533.33

영업부#

퇴직 사라는 제외합니다.

300 + 100
=
400

직원 수:

2명

평균:

400 / 2
=
200

인사부#

현재 직원:

0명

입니다.

6장. 미배정 직원 바다는 전체 평균에는 들어가지만 부서 평균에는 들어가지 않는다#

바다의 데이터:

dept_id = NULL
salary = 400

입니다.

이번 실습에서는 바다를:

전체 재직자 평균
→ 포함

부서 평균
→ 제외

하기로 했습니다.

이 규칙 때문에:

전체 평균
400

과:

개발 평균
533.33

영업 평균
200

을 서로 비교할 수 있습니다.

중요한 것은 이 포함 기준을 SQL 전체에서 일관되게 유지하는 것입니다.

7장. 문제 1: 전체 회사 평균보다 평균 급여가 높은 부서 찾기#

질문을 정확히 써보겠습니다.

재직 직원의 회사 전체 평균 급여보다 부서 평균 급여가 높은 부서를 찾아라.

반환 단위는:

직원

이 아니라:

부서

입니다.

따라서 GROUP BY가 자연스럽습니다.

8장. 먼저 재직 직원 집합을 만든다#

WITH active AS (
    SELECT *
    FROM employee
    WHERE retire_date IS NULL
)
SELECT *
FROM active;

사라가 제외됩니다.

이렇게 먼저 대상 집합에 이름을 붙이면 뒤의 계산 기준이 명확해집니다.

9장. 부서 평균을 계산한다#

WITH active AS (
    SELECT *
    FROM employee
    WHERE retire_date IS NULL
)
SELECT
    d.dept_id,
    d.dept_name,
    AVG(a.salary) AS avg_salary,
    COUNT(*) AS emp_count
FROM department AS d
JOIN active AS a
  ON a.dept_id = d.dept_id
GROUP BY
    d.dept_id,
    d.dept_name
ORDER BY d.dept_id;

예상 결과:

부서	평균	직원 수
개발	533.33	3
영업	200.00	2

인사부는 직원이 없으므로 내부 조인 결과에서 사라집니다.

10장. 회사 전체 평균과 비교한다#

WITH active AS (
    SELECT *
    FROM employee
    WHERE retire_date IS NULL
),
dept_avg AS (
    SELECT
        d.dept_id,
        d.dept_name,
        AVG(a.salary) AS avg_salary,
        COUNT(*) AS emp_count
    FROM department AS d
    JOIN active AS a
      ON a.dept_id = d.dept_id
    GROUP BY
        d.dept_id,
        d.dept_name
)
SELECT
    dept_id,
    dept_name,
    ROUND(avg_salary, 2) AS avg_salary,
    emp_count
FROM dept_avg
WHERE avg_salary > (
    SELECT AVG(salary)
    FROM active
)
ORDER BY
    avg_salary DESC,
    dept_id;

결과:

부서ID	부서	평균	직원 수
10	개발	533.33	3

전체 평균 400보다 높은 부서는 개발부뿐입니다.

11장. 같은 문제를 HAVING으로도 표현할 수 있다#

WITH active AS (
    SELECT *
    FROM employee
    WHERE retire_date IS NULL
)
SELECT
    d.dept_id,
    d.dept_name,
    ROUND(AVG(a.salary), 2) AS avg_salary
FROM department AS d
JOIN active AS a
  ON a.dept_id = d.dept_id
GROUP BY
    d.dept_id,
    d.dept_name
HAVING AVG(a.salary) > (
    SELECT AVG(salary)
    FROM active
)
ORDER BY avg_salary DESC;

HAVING은 그룹이 만들어진 뒤 집계 결과를 조건으로 사용합니다.

12장. WHERE와 HAVING의 역할을 구분하자#

이번 문제에서:

WHERE retire_date IS NULL

은 집계 전에 직원 행을 제거합니다.

반면:

HAVING AVG(salary) > ...

은 부서별 집계가 끝난 뒤 그룹을 제거합니다.

즉:

WHERE
→ 집계 대상 행 선택

HAVING
→ 집계된 그룹 선택

입니다.

13장. 전체 직원 평균과 부서 평균들의 평균은 다를 수 있다#

현재 부서 평균은:

개발
533.33

영업
200

입니다.

부서 평균의 단순 평균:

(533.33 + 200) / 2
≈
366.67

입니다.

하지만 전체 재직 직원 평균은:

입니다.

왜 다를까요?

각 부서의 직원 수가 다르기 때문입니다.

14장. 평균의 평균은 그룹 크기가 같을 때만 단순 계산할 수 있다#

개발부는 3명입니다.

영업부는 2명입니다.

미배정 직원도 1명 있습니다.

따라서:

각 부서를 동일한 비중으로 평균

하는 것과:

직원 한 명씩 동일한 비중으로 평균

하는 것은 다른 지표입니다.

SQL을 만들기 전에 어떤 평균을 원하는지 정의해야 합니다.

15장. NULL 급여를 0으로 바꾸는 것도 업무 결정이다#

현재 salary는 NOT NULL입니다.

하지만 실제 시스템에서 급여가 NULL일 수 있다고 하겠습니다.

다음처럼 처리할 수 있습니다.

AVG(COALESCE(salary, 0))

그러나 이는:

급여 미입력

을:

실제 급여 0

으로 간주하는 것입니다.

편의를 위한 치환이 지표의 의미를 바꿀 수 있습니다.

16장. 문제 2: 자기 부서 평균보다 급여가 높은 직원을 찾자#

이번에는 질문이 달라집니다.

각 재직 직원 가운데 자신의 부서 평균보다 급여가 높은 직원을 찾아라.

반환 단위는:

부서

가 아니라:

직원

입니다.

따라서 직원 행을 유지하면서 부서 평균을 붙이는 윈도우 함수가 자연스럽습니다.

17장. 윈도우 함수로 부서 평균을 각 직원에게 붙인다#

WITH compared AS (
    SELECT
        emp_id,
        emp_name,
        dept_id,
        salary,
        AVG(salary) OVER (
            PARTITION BY dept_id
        ) AS dept_avg
    FROM employee
    WHERE retire_date IS NULL
      AND dept_id IS NOT NULL
)
SELECT *
FROM compared
ORDER BY
    dept_id,
    emp_id;

직원 행은 사라지지 않습니다.

각 직원 옆에 같은 부서 평균이 추가됩니다.

18장. 결과를 직접 예상해 보면#

개발:

가람
600 / 평균 533.33

나래
500 / 평균 533.33

다온
500 / 평균 533.33

영업:

라온
300 / 평균 200

마루
100 / 평균 200

따라서 평균보다 높은 직원은:

가람

라온

입니다.

19장. 실제 SQL 결과#

WITH compared AS (
    SELECT
        emp_id,
        emp_name,
        dept_id,
        salary,
        AVG(salary) OVER (
            PARTITION BY dept_id
        ) AS dept_avg
    FROM employee
    WHERE retire_date IS NULL
      AND dept_id IS NOT NULL
)
SELECT
    emp_id,
    emp_name,
    dept_id,
    salary,
    ROUND(dept_avg, 2) AS dept_avg
FROM compared
WHERE salary > dept_avg
ORDER BY
    dept_id,
    emp_id;

결과:

사번	이름	부서	급여	부서 평균
1	가람	10	600	533.33
4	라온	20	300	200.00

20장. GROUP BY와 윈도우 함수의 가장 중요한 차이#

GROUP BY:

여러 직원 행
↓
부서 한 행

윈도우 함수:

직원 행 유지
+
부서 기준 계산값 추가

입니다.

따라서:

부서별 평균 보여줘

라면 GROUP BY가 자연스럽고:

각 직원과 그 직원의 부서 평균을 같이 보여줘

라면 윈도우 함수가 자연스럽습니다.

21장. 표시용 ROUND를 비교 조건에 섞지 말자#

실제 개발부 평균:

533.333333...

입니다.

화면에는:

533.33

으로 보여줄 수 있습니다.

하지만 비교는 원래 값으로 수행하는 편이 좋습니다.

WHERE salary > dept_avg

를 사용하고:

ROUND(dept_avg, 2)

는 출력할 때만 적용합니다.

22장. 문제 3: 급여 순위를 구하자#

재직자의 급여:

입니다.

두 명이 500으로 동점입니다.

이 상황에서:

RANK

DENSE_RANK

ROW_NUMBER

의 차이가 명확하게 드러납니다.

23장. RANK를 사용하면 동점 이후 순위를 건너뛴다#

SELECT
    emp_id,
    emp_name,
    salary,
    RANK() OVER (
        ORDER BY salary DESC
    ) AS salary_rank
FROM employee
WHERE retire_date IS NULL
ORDER BY
    salary DESC,
    emp_id;

순위:

입니다.

24장. DENSE_RANK는 동점 뒤 번호를 건너뛰지 않는다#

DENSE_RANK() OVER (
    ORDER BY salary DESC
)

결과:

입니다.

25장. ROW_NUMBER는 동점이어도 모든 행에 다른 번호를 준다#

ROW_NUMBER() OVER (
    ORDER BY salary DESC, emp_id
)

라면:

처럼 모든 직원에게 고유한 번호가 부여됩니다.

26장. 세 함수를 한 번에 비교해 보자#

SELECT
    emp_id,
    emp_name,
    salary,
    RANK() OVER (
        ORDER BY salary DESC
    ) AS salary_rank,
    DENSE_RANK() OVER (
        ORDER BY salary DESC
    ) AS dense_rank,
    ROW_NUMBER() OVER (
        ORDER BY salary DESC, emp_id
    ) AS row_no
FROM employee
WHERE retire_date IS NULL
ORDER BY
    salary DESC,
    emp_id;

결과:

이름	급여	RANK	DENSE_RANK	ROW_NUMBER
가람	600	1	1	1
나래	500	2	2	2
다온	500	2	2	3
바다	400	4	3	4
라온	300	5	4	5
마루	100	6	5	6

27장. “상위 2명”과 “2위까지”는 다른 요구다#

정확히 두 명#

상위 2명

이라면 ROW_NUMBER()를 사용할 수 있습니다.

WHERE row_no <= 2

결과는 정확히 두 행입니다.

공동 2위 포함#

2위까지

라면 RANK()를 사용할 수 있습니다.

WHERE salary_rank <= 2

동점자가 있으면 두 명을 넘을 수 있습니다.

28장. 순위 함수 선택은 문법이 아니라 업무 규칙이다#

다음 질문을 먼저 해야 합니다.

동점자는 모두 같은 순위인가?

동점 이후 순위를 건너뛰는가?

정확히 N명만 필요한가?

그 답에 따라:

RANK

DENSE_RANK

ROW_NUMBER

를 선택합니다.

29장. 급여 550인 직원을 추가하면 기존 직원의 순위도 바뀐다#

새 직원:

급여 550

을 추가했다고 하겠습니다.

새 정렬:

이제 500 두 명은:

RANK
3위

입니다.

400은:

RANK
5위

가 됩니다.

직원의 급여가 그대로여도 비교 집합이 변하면 상대 순위가 바뀝니다.

30장. LAG는 이전 순위를 가져오는 함수가 아니다#

다음 SQL을 실행해 보겠습니다.

SELECT
    emp_id,
    emp_name,
    salary,
    LAG(salary) OVER (
        ORDER BY salary DESC, emp_id
    ) AS prev_salary,
    LEAD(salary) OVER (
        ORDER BY salary DESC, emp_id
    ) AS next_salary
FROM employee
WHERE retire_date IS NULL
ORDER BY
    salary DESC,
    emp_id;

LAG는 정렬된 결과에서 바로 이전 행의 값을 가져옵니다.

31장. 동점에서 LAG의 의미를 확인하자#

결과 일부:

가람 600
prev = NULL
next = 500

나래 500
prev = 600
next = 500

다온 500
prev = 500
next = 400

나래의 다음 값은 400이 아닙니다.

같은 급여 500을 가진 다온이 다음 행이기 때문입니다.

따라서:

LAG
=
이전 순위

가 아닙니다.

32장. 전월 급여 비교에는 현재 직원 테이블만으로 부족하다#

현재 employee 테이블에는:

현재 급여

만 있습니다.

따라서:

LAG(salary)

를 사용했다고 해서:

전월 급여

가 되는 것이 아닙니다.

전월 비교를 하려면 최소한:

employee_id

salary_month

salary

같은 급여 이력 데이터가 필요합니다.

33장. 문제 4: 부서별 상위 2명을 구하자#

이번에는 부서 안에서 순위를 계산합니다.

WITH ranked AS (
    SELECT
        emp_id,
        emp_name,
        dept_id,
        salary,
        ROW_NUMBER() OVER (
            PARTITION BY dept_id
            ORDER BY salary DESC, emp_id
        ) AS rn
    FROM employee
    WHERE retire_date IS NULL
      AND dept_id IS NOT NULL
)
SELECT
    emp_id,
    emp_name,
    dept_id,
    salary,
    rn
FROM ranked
WHERE rn <= 2
ORDER BY
    dept_id,
    rn;

34장. PARTITION BY가 순위의 경계를 나눈다#

PARTITION BY dept_id를 사용하면:

개발부 안에서 다시 1위부터

영업부 안에서 다시 1위부터

순위를 계산합니다.

즉 회사 전체 순위와 부서 내부 순위는 다른 계산입니다.

35장. 공동 2위를 모두 포함하려면 RANK를 사용할 수 있다#

개발부:

입니다.

ROW_NUMBER <= 2라면:

600

500 한 명

만 나옵니다.

RANK <= 2라면:

세 명 모두 나옵니다.

따라서:

상위 2명

과:

2위까지

를 구분해야 합니다.

36장. 문제 5: 조직도를 재귀 CTE로 만들자#

직원 관리자 관계:

가람
├─ 나래
├─ 다온
├─ 라온
│  └─ 마루
└─ 바다

입니다.

가람은 최상위 관리자입니다.

라온은 마루의 관리자입니다.

37장. 조직도는 부모·자식 관계를 반복해서 따라가야 한다#

일반 조인 한 번이면:

직원
→ 직접 관리자

까지만 찾을 수 있습니다.

하지만 조직도에서는:

CEO
→ 팀장
→ 팀원
→ 하위 직원

처럼 깊이를 알 수 없는 관계를 반복 탐색해야 합니다.

이때 재귀 CTE가 유용합니다.

38장. 재귀 CTE의 시작점부터 정하자#

최상위 직원 조건:

mgr_id IS NULL

입니다.

또 퇴직자는 제외합니다.

SELECT
    emp_id,
    emp_name,
    mgr_id
FROM employee
WHERE mgr_id IS NULL
  AND retire_date IS NULL;

결과는 가람입니다.

39장. 기본 재귀 CTE를 만들어 보자#

WITH RECURSIVE org AS (
    SELECT
        emp_id,
        emp_name,
        mgr_id,
        1 AS depth
    FROM employee
    WHERE mgr_id IS NULL
      AND retire_date IS NULL

    UNION ALL

    SELECT
        e.emp_id,
        e.emp_name,
        e.mgr_id,
        o.depth + 1
    FROM employee AS e
    JOIN org AS o
      ON e.mgr_id = o.emp_id
    WHERE e.retire_date IS NULL
)
SELECT *
FROM org;

각 단계에서 직속 부하 직원을 계속 찾아 내려갑니다.

40장. 경로까지 표시해 보자#

WITH RECURSIVE org AS (
    SELECT
        emp_id,
        emp_name,
        mgr_id,
        1 AS depth,
        emp_name::text AS path
    FROM employee
    WHERE mgr_id IS NULL
      AND retire_date IS NULL

    UNION ALL

    SELECT
        e.emp_id,
        e.emp_name,
        e.mgr_id,
        o.depth + 1,
        o.path || ' → ' || e.emp_name
    FROM employee AS e
    JOIN org AS o
      ON e.mgr_id = o.emp_id
    WHERE e.retire_date IS NULL
)
SELECT
    emp_id,
    depth,
    path
FROM org
ORDER BY path;

예상 결과:

가람

가람 → 나래

가람 → 다온

가람 → 라온

가람 → 라온 → 마루

가람 → 바다

입니다.

41장. 하지만 잘못된 조직 데이터에 순환이 있으면 문제가 생긴다#

예를 들어:

가람의 관리자
→ 라온

라온의 관리자
→ 가람

처럼 잘못 입력됐다고 하겠습니다.

그러면:

가람
→ 라온
→ 가람
→ 라온
...

으로 반복될 수 있습니다.

따라서 순환 방지를 설계하는 편이 안전합니다.

42장. 방문한 직원 ID를 배열로 보존할 수 있다#

WITH RECURSIVE org AS (
    SELECT
        emp_id,
        emp_name,
        mgr_id,
        1 AS depth,
        ARRAY[emp_id] AS visited,
        emp_name::text AS path
    FROM employee
    WHERE mgr_id IS NULL
      AND retire_date IS NULL

    UNION ALL

    SELECT
        e.emp_id,
        e.emp_name,
        e.mgr_id,
        o.depth + 1,
        o.visited || e.emp_id,
        o.path || ' → ' || e.emp_name
    FROM employee AS e
    JOIN org AS o
      ON e.mgr_id = o.emp_id
    WHERE e.retire_date IS NULL
      AND NOT e.emp_id = ANY(o.visited)
)
SELECT
    emp_id,
    depth,
    path
FROM org
ORDER BY path;

이미 방문한 직원은 다시 따라가지 않습니다.

43장. 단순히 depth 10으로 제한하는 것은 완전한 순환 해결책이 아니다#

다음과 같이 할 수도 있습니다.

WHERE depth < 10

무한 반복은 막을 수 있습니다.

하지만 정상 조직이 11단계라면 마지막 직원을 잃습니다.

즉:

깊이 제한
→ 안전장치

방문 노드 검사
→ 실제 순환 방지

라는 차이가 있습니다.

44장. 관리자와 말단 직원도 구분해 보자#

현재 조직도에서:

가람
→ 부하 있음

라온
→ 부하 있음

나래
→ 부하 없음

입니다.

EXISTS를 사용할 수 있습니다.

CASE
    WHEN EXISTS (
        SELECT 1
        FROM employee AS e
        WHERE e.mgr_id = o.emp_id
          AND e.retire_date IS NULL
    )
    THEN '관리자'
    ELSE '말단'
END

45장. 완성된 조직도 SQL#

WITH RECURSIVE org AS (
    SELECT
        emp_id,
        emp_name,
        mgr_id,
        1 AS depth,
        ARRAY[emp_id] AS visited,
        emp_name::text AS path
    FROM employee
    WHERE mgr_id IS NULL
      AND retire_date IS NULL

    UNION ALL

    SELECT
        e.emp_id,
        e.emp_name,
        e.mgr_id,
        o.depth + 1,
        o.visited || e.emp_id,
        o.path || ' → ' || e.emp_name
    FROM employee AS e
    JOIN org AS o
      ON e.mgr_id = o.emp_id
    WHERE e.retire_date IS NULL
      AND NOT e.emp_id = ANY(o.visited)
)
SELECT
    o.emp_id,
    o.depth,
    o.path,
    CASE
        WHEN EXISTS (
            SELECT 1
            FROM employee AS e
            WHERE e.mgr_id = o.emp_id
              AND e.retire_date IS NULL
        )
        THEN '관리자'
        ELSE '말단'
    END AS node_type
FROM org AS o
ORDER BY o.path;

46장. 결과를 확인하면#

사번	깊이	경로	유형
1	1	가람	관리자
2	2	가람 → 나래	말단
3	2	가람 → 다온	말단
4	2	가람 → 라온	관리자
5	3	가람 → 라온 → 마루	말단
6	2	가람 → 바다	말단

퇴직한 사라는 제외됩니다.

문자열 정렬 순서는 데이터베이스의 정렬 규칙에 따라 달라질 수 있으므로 표의 핵심은 경로와 깊이입니다.

47장. 최상위 직원이 여러 명이면 여러 조직 트리가 시작될 수 있다#

조건:

mgr_id IS NULL

을 만족하는 직원이 여러 명이라면 재귀 CTE는 여러 시작점에서 동시에 출발합니다.

따라서 실제 회사에서:

CEO가 반드시 한 명

이어야 한다면 데이터 무결성 규칙을 별도로 두는 것이 좋습니다.

재귀 CTE는 데이터에 있는 관계를 읽는 것이지 조직 규칙을 자동으로 보장하지 않습니다.

48장. 관리자 행이 삭제되면 조직에서 고립된 직원이 생길 수 있다#

직원 A의:

mgr_id = 100

인데 직원 100이 존재하지 않는다면 정상적인 외래키가 있을 경우 입력 자체가 막힐 수 있습니다.

그러나 과거 이관 데이터나 외래키 없는 시스템에서는 이런 고아 관계가 생길 수 있습니다.

재귀 CTE 결과에 나타나지 않는 직원이 있다면 이런 데이터 품질 문제도 확인해야 합니다.

49장. 문제 6: CASE로 급여 등급을 만들자#

학습용 기준:

600 이상
→ S

450 이상
→ A

350 이상
→ B

그 외
→ C

입니다.

SQL:

SELECT
    e.emp_id,
    e.emp_name,
    COALESCE(d.dept_name, '미배정') AS dept_name,
    CASE
        WHEN e.salary >= 600 THEN 'S'
        WHEN e.salary >= 450 THEN 'A'
        WHEN e.salary >= 350 THEN 'B'
        ELSE 'C'
    END AS salary_grade
FROM employee AS e
LEFT JOIN department AS d
  ON d.dept_id = e.dept_id
WHERE e.retire_date IS NULL
ORDER BY e.emp_id;

50장. CASE는 위에서부터 검사한다#

가람의 급여:

입니다.

첫 조건:

salary >= 600

이 참이므로 바로:

가 됩니다.

그 아래:

salary >= 450

도 참이지만 이미 평가가 끝났습니다.

51장. 낮은 임계값부터 쓰면 등급이 잘못된다#

잘못된 예:

CASE
    WHEN salary >= 350 THEN 'B'
    WHEN salary >= 450 THEN 'A'
    WHEN salary >= 600 THEN 'S'
END

600도 첫 조건인:

>= 350

을 만족합니다.

따라서 B로 끝납니다.

범위가 겹치는 CASE에서는 일반적으로 더 높은 임계값부터 검사해야 합니다.

52장. 미배정 표시와 실제 오류를 구분해야 한다#

바다의:

dept_id = NULL

은 정상적인 미배정 상태라고 하겠습니다.

따라서:

COALESCE(d.dept_name, '미배정')

가 자연스럽습니다.

하지만 실제 데이터에:

dept_id = 999

가 있고 999 부서가 존재하지 않는다면 이것은 참조 오류입니다.

둘을 모두:

미배정

으로 표시하면 오류가 숨겨질 수 있습니다.

53장. LEFT JOIN을 사용하는 이유#

바다는 부서가 없습니다.

내부 조인을 사용하면:

바다
→ 결과에서 사라짐

입니다.

LEFT JOIN은 직원 행을 유지하면서 부서가 없을 때 NULL을 반환합니다.

따라서:

직원 전체를 보여주고
부서가 없으면 미배정 표시

라는 요구에는 LEFT JOIN이 맞습니다.

54장. 문제 7: 특정 프로젝트에 참여한 직원을 찾자#

프로젝트 배정 테이블을 추가합니다.

CREATE TABLE project_assignment (
    project_id text NOT NULL,
    emp_id integer NOT NULL
        REFERENCES employee(emp_id),
    task_id integer NOT NULL,
    PRIMARY KEY (
        project_id,
        emp_id,
        task_id
    )
);

데이터:

INSERT INTO project_assignment
VALUES
    ('P2024', 2, 1),
    ('P2024', 2, 2),
    ('P2024', 4, 1),
    ('P2025', 3, 1);

55장. 나래는 P2024에 두 개의 작업을 가지고 있다#

현재 데이터:

나래
P2024 task 1

나래
P2024 task 2

라온
P2024 task 1

질문은:

P2024에 참여한 직원은 누구인가?

입니다.

원하는 결과는:

나래

라온

두 명입니다.

56장. 단순 조인을 사용하면 나래가 두 번 나온다#

SELECT
    e.emp_id,
    e.emp_name
FROM employee AS e
JOIN project_assignment AS p
  ON p.emp_id = e.emp_id
WHERE p.project_id = 'P2024'
ORDER BY e.emp_id;

결과:

나래

나래

라온

입니다.

왜 그럴까요?

나래에게 프로젝트 배정 행이 두 개 있기 때문입니다.

57장. 조인은 행을 연결하는 연산이지 존재 여부만 확인하는 연산이 아니다#

직원 한 명이:

프로젝트 배정 2건

과 연결되면 조인 결과도 두 행이 됩니다.

따라서:

조인하면 중복이 생겼다

가 아니라:

관계의 카디널리티 때문에 직원 한 행이 여러 배정 행으로 확장됐다.

라고 이해하는 것이 정확합니다.

58장. 존재 여부만 필요하면 EXISTS가 자연스럽다#

SELECT
    e.emp_id,
    e.emp_name
FROM employee AS e
WHERE EXISTS (
    SELECT 1
    FROM project_assignment AS p
    WHERE p.project_id = 'P2024'
      AND p.emp_id = e.emp_id
)
ORDER BY e.emp_id;

결과:

나래

라온

입니다.

직원당 한 행을 유지합니다.

59장. IN으로도 같은 집합을 표현할 수 있다#

SELECT
    e.emp_id,
    e.emp_name
FROM employee AS e
WHERE e.emp_id IN (
    SELECT p.emp_id
    FROM project_assignment AS p
    WHERE p.project_id = 'P2024'
)
ORDER BY e.emp_id;

현재 데이터에서는 EXISTS와 같은 결과가 나옵니다.

60장. EXISTS와 IN의 성능을 단순 규칙으로 외우지 말자#

다음 설명은 지나치게 단순합니다.

작은 데이터
→ IN

큰 데이터
→ EXISTS

현대 옵티마이저는 두 표현을 유사한 실행 계획으로 변환할 수도 있습니다.

실제 성능은:

통계

인덱스

데이터 분포

서브쿼리 구조

선택도

에 따라 달라집니다.

실행 계획을 확인해야 합니다.

61장. NOT IN에서는 NULL을 특히 조심해야 한다#

예를 들어:

WHERE emp_id NOT IN (
    SELECT emp_id
    FROM project_assignment
)

이라고 했는데 서브쿼리 결과에 NULL이 포함될 수 있다고 하겠습니다.

SQL의 3값 논리 때문에 비교 결과가 UNKNOWN이 되어 예상하지 못한 결과가 나올 수 있습니다.

미존재 조건에서는 NOT EXISTS가 의미를 더 명확하게 표현하는 경우가 많습니다.

62장. 프로젝트에 참여하지 않은 직원을 찾는 NOT EXISTS#

SELECT
    e.emp_id,
    e.emp_name
FROM employee AS e
WHERE e.retire_date IS NULL
  AND NOT EXISTS (
      SELECT 1
      FROM project_assignment AS p
      WHERE p.emp_id = e.emp_id
  )
ORDER BY e.emp_id;

이 쿼리는:

해당 직원과 연결되는 프로젝트 배정 행이 하나도 없는가?

를 직접 표현합니다.

63장. GROUP BY 전에 조인하면 급여 합계도 중복될 수 있다#

다음 쿼리를 생각해 보겠습니다.

SELECT
    SUM(e.salary)
FROM employee AS e
JOIN project_assignment AS p
  ON p.emp_id = e.emp_id
WHERE p.project_id = 'P2024';

P2024에서:

나래 급여 500
→ 두 번

라온 급여 300
→ 한 번

따라서 합계:

500 + 500 + 300
=
1,300

이 됩니다.

64장. 실제 참여 직원 급여 합계는 800이다#

참여 직원은:

나래
500

라온
300

입니다.

따라서 직원 기준 합계는:

입니다.

집계 함수가 틀린 것이 아닙니다.

조인 후 행의 단위가:

직원

에서:

프로젝트 작업 배정

으로 바뀐 것이 원인입니다.

65장. DISTINCT로 무조건 덮는 것도 주의해야 한다#

다음처럼 처리할 수 있습니다.

SUM(DISTINCT e.salary)

하지만 나래와 라온의 급여가 둘 다 500이라면:

을 한 번만 합산합니다.

즉 DISTINCT salary는 직원 중복 제거가 아니라 급여 값의 중복 제거입니다.

완전히 다른 의미입니다.

66장. 먼저 직원을 고유하게 만든 뒤 집계해야 한다#

예:

SELECT
    SUM(e.salary)
FROM employee AS e
WHERE EXISTS (
    SELECT 1
    FROM project_assignment AS p
    WHERE p.project_id = 'P2024'
      AND p.emp_id = e.emp_id
);

직원 한 행을 유지하므로:

500 + 300
=
800

입니다.

67장. 직원 수를 셀 때도 같은 원칙이 적용된다#

잘못된 경우:

COUNT(*)

조인 결과:

3행

따라서:

참여 직원 3명

이라고 잘못 해석할 수 있습니다.

실제 직원은 두 명입니다.

68장. COUNT DISTINCT로 직원 식별자를 세는 방법#

SELECT
    COUNT(DISTINCT e.emp_id)
FROM employee AS e
JOIN project_assignment AS p
  ON p.emp_id = e.emp_id
WHERE p.project_id = 'P2024';

결과:

입니다.

핵심은 DISTINCT를 어디에 적용하는지입니다.

69장. 급여 분석에서 한 행의 의미를 계속 확인해야 한다#

employee:

한 행
=
직원 한 명

project_assignment:

한 행
=
직원의 프로젝트 작업 한 건

두 테이블을 조인하면:

한 행
=
직원과 작업의 연결 한 건

이 됩니다.

집계 전에 이 변화를 인식해야 합니다.

70장. 전체 평균과 부서 평균 문제를 잘못 섞으면 다른 답이 나온다#

질문 A:

평균 급여가 회사 평균보다 높은 부서는?

반환:

부서

질문 B:

자기 부서 평균보다 급여가 높은 직원은?

반환:

직원

질문이 비슷해 보여도 SQL 구조는 다릅니다.

GROUP BY

윈도우 함수

가 자연스럽습니다.

71장. 부서가 없는 직원을 회사 평균에서 제외하면 결과도 바뀐다#

현재 전체 평균:

2,400 / 6
=
400

입니다.

바다 400을 제외하면:

2,000 / 5
=
400

이번 데이터에서는 우연히 같습니다.

하지만 바다의 급여가 700이었다면 결과는 달라졌을 것입니다.

따라서 결과가 우연히 같다고 기준이 중요하지 않은 것은 아닙니다.

72장. 퇴직 사라를 포함하면 영업 평균은 크게 달라진다#

재직자만:

퇴직 사라 900까지 포함:

300 + 100 + 900
=
1,300

평균
433.33

입니다.

같은 영업부인데 평균이 두 배 이상 달라집니다.

그래서 집계 결과에는:

재직자 기준

전체 이력 기준

같은 대상 정의가 필요합니다.

73장. 0명 부서를 보여줘야 하는지도 요구사항이다#

현재 인사부는 직원이 없습니다.

내부 조인에서는 결과에서 사라집니다.

하지만 보고서가:

모든 부서와 현재 인원수를 보여라.

라면 인사도 보여야 합니다.

이때 LEFT JOIN을 사용할 수 있습니다.

74장. 0명 부서를 포함한 직원 수 조회#

SELECT
    d.dept_id,
    d.dept_name,
    COUNT(e.emp_id) AS active_count
FROM department AS d
LEFT JOIN employee AS e
  ON e.dept_id = d.dept_id
 AND e.retire_date IS NULL
GROUP BY
    d.dept_id,
    d.dept_name
ORDER BY d.dept_id;

결과:

부서	직원 수
개발	3
영업	2
인사	0

75장. LEFT JOIN에서 조건 위치를 잘못 두면 0명 부서가 사라질 수 있다#

다음 SQL을 생각해 보겠습니다.

FROM department d
LEFT JOIN employee e
  ON e.dept_id = d.dept_id
WHERE e.retire_date IS NULL

WHERE 조건이 NULL 확장 행을 어떻게 다루는지에 따라 의도와 다른 결과가 나올 수 있습니다.

외부 조인에서는:

ON 조건

WHERE 조건

의 차이를 반드시 확인해야 합니다.

76장. COUNT 별표와 COUNT 컬럼도 결과가 다를 수 있다#

0명 부서를 LEFT JOIN으로 만들면 부서 행 자체는 존재합니다.

따라서:

COUNT(*)

를 사용하면 인사부도 1로 셀 수 있습니다.

반면:

COUNT(e.emp_id)

는 실제 직원 ID가 있는 행만 세므로 0입니다.

외부 조인 집계에서는 특히 중요한 차이입니다.

77장. 조직도에서도 퇴직자를 어디까지 제외할지 정책이 필요하다#

현재는:

퇴직 직원
→ 조직도에서 완전 제외

했습니다.

하지만 과거 조직도를 복원하려면:

특정 기준일 당시 재직 여부

가 필요합니다.

현재 retire_date IS NULL만으로는 과거 조직도까지 정확하게 재현할 수 없습니다.

입사일과 관리자 관계 이력이 필요할 수 있습니다.

78장. 현재 관리자 ID 하나만 저장하면 과거 조직도를 알 수 없다#

직원 나래의 현재 관리자:

가람

이라고 하겠습니다.

작년에는 라온이 관리자였을 수 있습니다.

현재 mgr_id 하나만 저장하면 과거 관계가 사라집니다.

과거 조직 분석이 필요하다면:

emp_id

mgr_id

valid_from

valid_to

같은 이력 구조를 검토해야 합니다.

79장. 급여 역시 현재값과 이력값을 구분해야 한다#

현재 급여:

만 저장되어 있다면:

작년 급여

전월 급여

승진 전 급여

를 알 수 없습니다.

LAG()가 과거 데이터를 만들어 주는 것은 아닙니다.

윈도우 함수는 이미 존재하는 행 사이를 비교할 뿐입니다.

80장. SQL 함수가 부족한 것이 아니라 원본 데이터가 부족할 수도 있다#

전월 대비 급여 증가율을 요구받았습니다.

하지만 테이블에는 현재 급여 한 행뿐입니다.

이때 복잡한 SQL을 만드는 것이 해결책이 아닙니다.

먼저:

급여 이력 데이터

가 존재해야 합니다.

SQL은 저장되지 않은 과거 사실을 복원해 주는 마법이 아닙니다.

81장. SQL 실습에서 가장 먼저 해야 할 것은 예상 결과를 손으로 만드는 것이다#

예를 들어:

자기 부서 평균보다 급여가 높은 직원

이라는 문제라면 먼저:

개발 평균
533.33

영업 평균
200

을 계산합니다.

그다음 직원별로 비교합니다.

가람 600
→ 포함

나래 500
→ 제외

다온 500
→ 제외

라온 300
→ 포함

마루 100
→ 제외

예상 결과:

가람
라온

입니다.

SQL 결과가 다르면 쿼리를 의심할 수 있습니다.

82장. 결과 건수도 함께 예상해야 한다#

P2024 프로젝트 참여자:

나래

라온

따라서 기대 행 수:

입니다.

조인 결과가:

3행

이라면 바로 중복 확장을 발견할 수 있습니다.

단순히 값만 보는 것보다:

기대 행 수

를 함께 계산하는 습관이 좋습니다.

83장. SQL 문제를 풀 때 세 가지를 먼저 쓰자#

첫째:

입력 대상

예:

퇴직자 제외

둘째:

반환 단위

예:

직원 한 행

셋째:

비교 기준

예:

자기 부서 재직자 평균

이 세 줄만 먼저 적어도 많은 SQL 오류를 줄일 수 있습니다.

84장. 함수 이름부터 고르면 요구를 왜곡하기 쉽다#

예를 들어:

윈도우 함수를 써야 한다.

부터 시작하면 모든 문제를 윈도우 함수로 풀려고 할 수 있습니다.

하지만 실제 질문이:

부서별 평균 한 행씩

이라면 단순 GROUP BY가 더 자연스럽습니다.

반대로:

직원과 부서 평균을 동시에

라면 윈도우 함수가 좋습니다.

문법은 요구를 구현하는 수단입니다.

85장. 이번 실습의 주요 SQL 패턴을 정리하면#

GROUP BY#

여러 직원
→ 부서 한 행

HAVING#

집계된 부서 결과에 조건

윈도우 함수#

직원 행 유지
+
그룹 기준 계산

재귀 CTE#

관리자 관계 반복 탐색

CASE#

조건별 분류

EXISTS#

관련 행의 존재 여부

각 문법은 서로 다른 질문에 답합니다.

86장. 실무에서 자주 발생하는 실수 1: 퇴직자 조건 누락#

SELECT AVG(salary)
FROM employee;

를 사용하면 사라의 900도 포함됩니다.

업무 보고서가 재직자 기준이라면:

WHERE retire_date IS NULL

이 필요합니다.

하지만 과거 인건비 보고라면 퇴직자를 제외하는 것이 오히려 틀릴 수 있습니다.

항상 지표 기준일을 확인해야 합니다.

87장. 실무에서 자주 발생하는 실수 2: 조인으로 행이 늘어난 뒤 집계#

직원:

1행

프로젝트:

2행

조인 후:

2행

이 됩니다.

그 상태에서 급여를 합치면 두 번 합산됩니다.

집계 전에 현재 행의 단위를 확인해야 합니다.

88장. 실무에서 자주 발생하는 실수 3: DISTINCT로 원인을 숨긴다#

중복 결과가 나오자:

SELECT DISTINCT ...

를 붙였습니다.

결과는 원하는 모양으로 보입니다.

하지만 원인이:

N:M 조인

중복 원천 데이터

잘못된 조인 조건

중 무엇인지 알 수 없습니다.

DISTINCT는 의미상 중복 제거가 맞을 때 사용해야 합니다.

89장. 실무에서 자주 발생하는 실수 4: 동점 규칙이 없다#

상위 3명

이라는 요구가 있습니다.

3위에 세 명이 동점입니다.

질문해야 합니다.

정확히 3명인가?

3위까지 모두 포함인가?

답에 따라 ROW_NUMBER와 RANK의 선택이 달라집니다.

90장. 실무에서 자주 발생하는 실수 5: 재귀 쿼리에 순환 방지가 없다#

조직 데이터는 원래 트리여야 한다고 믿고 순환 방지를 넣지 않았습니다.

하지만 데이터 오류 하나로:

A → B → C → A

가 생길 수 있습니다.

재귀 SQL에서는 데이터가 항상 완벽하다는 가정보다 비정상 관계를 어떻게 감지할지를 함께 설계하는 것이 안전합니다.

91장. 실무에서 자주 발생하는 실수 6: NULL을 단순한 빈값으로만 본다#

바다의:

dept_id = NULL

은 이번 예제에서는 미배정입니다.

하지만 실제 시스템에서는:

아직 배정 전

정보 누락

조직 외 인력

데이터 연계 실패

일 수도 있습니다.

같은 NULL이라도 업무 의미가 다를 수 있습니다.

92장. SQL 결과 검증 체크리스트#

집계 대상 직원은 누구인가?
퇴직자를 포함하는가?
부서 미배정자를 포함하는가?
결과 한 행은 직원인가 부서인가?
GROUP BY 전에 조인으로 행이 늘어나지 않았는가?
COUNT 별표와 COUNT 컬럼의 차이를 확인했는가?
평균의 분모는 정확한가?
동점 처리 기준은 무엇인가?
LAG가 이전 순위가 아니라 이전 행이라는 것을 구분했는가?
재귀 CTE에 순환 가능성이 있는가?
LEFT JOIN의 ON과 WHERE 조건 위치가 의도와 맞는가?
EXISTS가 더 자연스러운 존재 조건을 조인으로 풀지 않았는가?
DISTINCT가 오류를 감추고 있지 않은가?
NULL을 0이나 문자열로 바꾸면서 의미를 변경하지 않았는가?
SQL 실행 전 예상 결과 건수를 계산했는가?

93장. 핵심 정리#

이번 실습에서는 하나의 직원 데이터로 여러 SQL 패턴을 비교했습니다.

가장 먼저 확인한 것은 문법이 아니었습니다.

누구를 계산할 것인가?

결과 한 행은 무엇인가?

어떤 집단과 비교할 것인가?

였습니다.

회사 전체 평균보다 평균 급여가 높은 부서를 찾을 때는 결과 한 행이 부서입니다.

따라서:

GROUP BY

HAVING

이 자연스럽습니다.

반대로 자기 부서 평균보다 급여가 높은 직원을 찾을 때는 직원 행을 유지해야 합니다.

그래서:

AVG() OVER (
    PARTITION BY dept_id
)

를 이용해 각 직원 옆에 부서 평균을 붙일 수 있습니다.

순위에서는 동점을 어떻게 다룰지 먼저 정해야 합니다.

RANK
→ 동점 뒤 순위 건너뜀

DENSE_RANK
→ 동점 뒤 순위 연속

ROW_NUMBER
→ 모든 행에 다른 번호

입니다.

LAG와 LEAD는 이전·다음 행을 가져옵니다.

현재 직원 테이블에 급여 이력이 없다면 LAG(salary)를 전월 급여로 해석할 수 없습니다.

조직도처럼 부모·자식 관계를 반복 탐색해야 하는 경우에는 재귀 CTE가 적합합니다.

하지만 실제 데이터에 순환이 존재할 수 있으므로:

visited

와 같은 경로 기록을 이용해 이미 방문한 노드를 다시 따라가지 않도록 만들 수 있습니다.

프로젝트 참여 여부에서는 EXISTS가 중요한 차이를 보여줍니다.

직원 한 명이 프로젝트 작업 두 건을 가지고 있을 때 단순 조인은 직원을 두 행으로 늘립니다.

직원 1행
×
프로젝트 작업 2행
=
조인 결과 2행

입니다.

이 상태에서 SUM(salary)나 COUNT(*)를 사용하면 급여나 직원 수가 중복 계산될 수 있습니다.

따라서 존재 여부만 필요하다면:

WHERE EXISTS (...)

가 요구의 의미를 더 직접적으로 표현할 수 있습니다.

이번 실습 전체를 관통하는 원칙은 하나입니다.

SQL 함수를 선택하기 전에 결과의 한 행이 무엇을 의미해야 하는지 먼저 정의한다.

그리고 쿼리를 실행하기 전에:

대상

분모

예상 결과

예상 행 수

를 손으로 계산해 두는 습관이 중요합니다.

복잡한 SQL이 틀리는 이유는 문법을 몰라서보다 집계 전후의 행 단위와 대상 범위가 바뀌었다는 사실을 놓쳐서인 경우가 많습니다.

좋은 SQL은 실행되는 SQL이 아닙니다.

같은 입력을 넣었을 때 왜 정확히 그 행과 그 숫자가 나왔는지를 설명할 수 있는 SQL입니다.