[LG CNS 6기] 본 과정 22일차 TIL / [DB] - 서브쿼리, DDL

1. 오늘의 한 줄 요약

내일이면 금요일!

2. 오늘 배운 것

2.1 서브쿼리란

서브쿼리는 SELECT문 안에 또 다른 SELECT문이 들어가 있는 구조다.
바깥 쿼리(메인 쿼리)가 조건값으로 쓸 값을, 직접 하드코딩하지 않고 안쪽 쿼리(서브쿼리)가 그때그때 구해서 넘겨준다.

SELECT STUDENT_NAME
FROM tb_student S
WHERE ABSENCE_YN = 'Y'
AND DEPARTMENT_NO = (
    SELECT DEPARTMENT_NO
    FROM TB_DEPARTMENT
    WHERE DEPARTMENT_NAME = '국어국문학과'
);

국어국문학과의 학과번호가 몇 번인지 몰라도, 저 자리에 학과 이름 조건을 넣은 서브쿼리를 두면 알아서 번호로 치환돼서 비교된다.

2.2 서브쿼리가 들어가는 세 자리

서브쿼리는 위치에 따라 부르는 이름이 다르다.

위치	이름	특징
SELECT 절	스칼라 서브쿼리	결과가 값 하나(1행 1열)여야 함
FROM 절	인라인 뷰(Inline View)	서브쿼리 결과를 임시 테이블(가상 테이블, virtual table)처럼 취급, 별칭 필수
WHERE 절	(일반) 서브쿼리	조건값으로 사용

FROM절에 쓴 서브쿼리, 즉 인라인 뷰는 실행되는 그 순간에만 존재하는 가상 테이블이다.
어제 JOIN이 "가상의 테이블을 만들어서 결과를 뽑는 것"이라고 배웠는데, 오늘은 그 가상 테이블을 JOIN이 아니라 서브쿼리로도 직접 만들 수 있다는 걸 배웠다.

2.3 서브쿼리 유형: 행 개수 × 열 개수

서브쿼리가 몇 행, 몇 열을 반환하는지에 따라 쓸 수 있는 연산자가 달라진다.

구분	단일열	다중열
단일행	일반 연산자(=, >, < 등)	(col1,col2) = (...)
다중행	IN, ANY, ALL	(col1,col2) IN (...)

서브쿼리가 여러 행을 반환할 수 있는 자리에 =를 그대로 쓰면 "ERROR 1242 (21000): Subquery returns more than 1 row"라는 에러가 난다. 그리고 열을 하나만 비교하면 안 되는 경우도 있다. 서별 최소급여 사원을 찾을 때 급여 하나만 비교하면, 우연히 같은 급여를 받는 다른 부서 사원까지 걸릴 수 있어서 부서번호와 급여를 묶어서 비교해야 한다.

SELECT EMP_NAME, DEPT_ID, SALARY
FROM employee E
WHERE (DEPT_ID, SALARY) IN (
    SELECT DEPT_ID, MIN(SALARY)
    FROM employee
    GROUP BY DEPT_ID
);

2.4 집계함수는 중첩할 수 없다 -> 인라인 뷰로 해결

-- 급여합 1등 부서 찾기
SELECT V.DEPT_NAME, MAX(V.SUM)
FROM (
    SELECT D.DEPT_NAME, SUM(E.SALARY) AS `SUM`
    FROM department D
    JOIN employee E ON (D.DEPT_ID = E.DEPT_ID)
    GROUP BY DEPT_NAME
) V;

부서별 합계(SUM)가 전부 나와야 그중 최댓값(MAX)을 고를 수 있는데, MAX(SUM(...))처럼 한 자리에 겹쳐 쓰면 아직 다 만들어지지도 않은 값을 놓고 최댓값을 구하라는 순서가 돼서 에러가 난다.
그래서 부서별 합계를 인라인 뷰로 먼저 완성한 다음, 그 결과 위에서 다시 MAX를 구했다.

2.5 ANY / ALL

다중행 서브쿼리를 비교연산자와 같이 쓸 때 붙이는 지시자다.

연산자	의미
> ANY	목록 중 어느 하나보다 크면 참 → 최솟값보다 크다
< ANY	목록 중 어느 하나보다 작으면 참 → 최댓값보다 작다
> ALL	목록 전부보다 커야 참 → 최댓값보다 크다
< ALL	목록 전부보다 작아야 참 → 최솟값보다 작다

-- 과장 전원보다 급여가 적은 대리
SELECT E.EMP_NAME, E.SALARY
FROM employee E
JOIN job J ON (E.JOB_ID = J.JOB_ID)
WHERE J.JOB_TITLE = '대리'
AND SALARY < ALL (
    SELECT E.SALARY FROM employee E
    JOIN job J ON (E.JOB_ID = J.JOB_ID)
    WHERE J.JOB_TITLE = '과장'
);

ANY는 조건을 만족하는 값이 목록에 하나만 있어도 참이 되고,
ALL은 목록에 있는 값 전부가 조건을 만족해야 참이 된다고 정리하면 헷갈리지 않는다.
= ANY는 IN과 같은 뜻이다.

2.6 TOP-N 쿼리: LIMIT / OFFSET

SELECT D.DEPT_NAME, SUM(E.SALARY) AS S
FROM employee E
JOIN department D ON (E.DEPT_ID=D.DEPT_ID)
GROUP BY E.DEPT_ID
ORDER BY S DESC
LIMIT 1;

-- OFFSET: M+1번지부터 N번지까지 범위 검색
SELECT E.DEPT_ID, SUM(SALARY)
FROM employee E
GROUP BY DEPT_ID
ORDER BY 2 DESC
LIMIT 3 OFFSET 0;

2.4에서 서브쿼리로 복잡하게 풀었던 "급여합 1등 부서 찾기"를, 정렬 후 LIMIT으로 자르면 훨씬 짧게 끝난다.
다만 급여합이 똑같은 부서가 두 곳이어도 LIMIT 1은 딱 한 행만 돌려준다는 차이는 있다.

2.7 상관관계 서브쿼리

SELECT E.EMP_NAME, J.JOB_TITLE, E.SALARY
FROM job J
JOIN employee E ON (J.JOB_ID = E.JOB_ID)
WHERE E.SALARY = (
    SELECT TRUNCATE(AVG(SALARY), -5)
    FROM employee EE
    WHERE EE.JOB_ID = E.JOB_ID
);

서브쿼리 조건절에 등장하는 E.JOB_ID는 이 서브쿼리 자신의 것이 아니라, 바깥 FROM절에서 가져온 값이다. 이렇게 안쪽 쿼리가 자기 힘만으로 완성되지 못하고 바깥 쿼리의 값을 매번 받아와야 하는 구조를 상관관계 서브쿼리라고 부른다. 그래서 실행 방식도 다르다. 바깥 쿼리가 한 행씩 넘어갈 때마다 안쪽 서브쿼리가 그 행 기준으로 다시 계산된다. 같은 문제를 직급별 평균을 인라인 뷰로 먼저 구해두고 그 결과와 조인하는 방식으로도 풀 수 있는데, 이 경우엔 서브쿼리가 한 번만 실행된다는 점이 다르다.

2.8 JOIN의 실행 방법 3가지

• NESTED LOOP JOIN: 한쪽 테이블(구동 테이블)을 한 행씩 짚어가며, 그때마다 다른 쪽 테이블에서 짝을 찾는다. 테이블 크기가 크고 인덱스가 없으면 느려진다.
• SORT MERGE JOIN: 두 테이블이 조인 키 기준으로 이미 정렬돼 있으면, 양쪽을 나란히 한 번씩만 훑어서 짝을 맞춘다.
• HASH JOIN: 한쪽 테이블을 해시 값 기준으로 미리 나눠두고, 다른 쪽 테이블을 훑을 때 그 해시로 짝을 빠르게 찾는다.

2.9 DDL과 제약조건

CREATE TABLE TB_BLOG(
    BLOG_ID     VARCHAR(50),
    TITLE       VARCHAR(100),
    CONTENT     VARCHAR(100),
    EMAIL       VARCHAR(100),
    CATEGORY    VARCHAR(100) CHECK(CATEGORY IN ('전체','취미','일상','코딩')),
    CREATE_AT   DATE DEFAULT SYSDATE(),
    PRIMARY KEY (BLOG_ID)
);

DDL은 테이블, 뷰 같은 객체의 구조 자체를 정의하는 언어로 CREATE(생성), ALTER(변경), DROP(삭제)이 있다.
오늘 다룬 제약조건은 PRIMARY KEY(NOT NULL+UNIQUE 성질을 동시에 가짐), FOREIGN KEY, NOT NULL, UNIQUE, CHECK 총 다섯 가지다. 컬럼을 선언하면서 바로 옆에 붙이면 컬럼 레벨 제약, 모든 컬럼을 다 쓴 뒤 따로 한 줄로 적으면 테이블 레벨 제약이라고 부른다. DEFAULT는 INSERT할 때 값을 안 넣으면 대신 채워지는 값을 미리 정해두는 것이다.

3. 실습 / 적용

직접 해본 것:
워크북 18번(국어국문학과 총평점 최고 학생)을 서브쿼리로 풀었다.

SELECT S.STUDENT_NO, S.STUDENT_NAME
FROM tb_student S
JOIN tb_grade G ON (S.STUDENT_NO = G.STUDENT_NO)
WHERE S.DEPARTMENT_NO = (
    SELECT DEPARTMENT_NO FROM tb_department WHERE DEPARTMENT_NAME = '국어국문학과'
)
GROUP BY S.STUDENT_NO, S.STUDENT_NAME
HAVING AVG(G.POINT) = (
    SELECT MAX(V.AVG)
    FROM (
        SELECT AVG(G.POINT) AS `AVG`
        FROM tb_student S
        JOIN tb_grade G ON (S.STUDENT_NO = G.STUDENT_NO)
        WHERE S.DEPARTMENT_NO = (
            SELECT DEPARTMENT_NO FROM tb_department WHERE DEPARTMENT_NAME = '국어국문학과'
        )
        GROUP BY S.STUDENT_NO
    ) V
);

TABLEDB에 TB_BLOG 테이블을 만들어서 PRIMARY KEY, DEFAULT, CHECK 제약조건을 하나씩 걸어보고, 정상적으로 들어가는 INSERT와 거부되는 INSERT를 직접 비교해봤다.

4. 트러블 슈팅

① 집계함수를 중첩해서 쓴 오류
부서별 급여 총합 중 최댓값을 구하려고 MAX(SUM(E.SALARY))를 바로 썼다.

-- 원인이 된 코드
SELECT D.DEPT_NAME, MAX(SUM(E.SALARY))
FROM department D
JOIN employee E ON (D.DEPT_ID = E.DEPT_ID)
GROUP BY DEPT_NAME;

집계함수 위에 집계함수를 바로 씌울 수 없어서 에러가 났다. 부서별 합계를 인라인 뷰로 먼저 만들고, 그 결과에서 다시 MAX를 구하는 방식으로 해결했다.

② 집계함수와 전체 컬럼(*)을 같이 쓴 오류

-- 원인이 된 코드
SELECT E.*, MIN(SALARY)
FROM employee E;

E.*(모든 일반 컬럼)와 MIN(SALARY)(집계함수)를 GROUP BY 없이 같이 써서 에러가 났다.
집계함수와 일반 컬럼을 같이 쓰려면 GROUP BY로 묶어야 한다는 걸 다시 확인했다.

5. 오늘의 회고

• 느낀 점: 열심히 하자!
• 다음에 할 것: 실습 내용 복습.

---

#LGCNS #LGCNS6기 #개발자 #LGCNSINSPIRECAMP