과정) 서브쿼리 사용하기

조인하는 테이블이 많기 때문에 어떤 테이블들을 조인하는지 가독성을 높이고자 서브쿼리를 사용하였다.
그러나 exp, certi 테이블은 max() 함수를 사용하지 않으니 한 명 직원의 경력과 자격증이 각각의 튜플로 나뉘어져 employee 테이블의 값이 중복되어 출력되었다.

SELECT
    emp.E_NO,
    emp.E_NAME,
    emp.E_ADDRESS,
    emp.E_AUTH,
    emp.E_STATUS,
    emp.E_EMAIL,
    emp.E_GENDER,
    emp.E_HIREDATE,
    emp.E_ENDDATE,
    emp.E_PASSWORD,
    emp.E_PHONE,
    emp.E_RANK,
    emp.E_BIRTH,
    emp.DEPT_NAME,
    emp.E_OCCUP,
    MAX(CASE WHEN edu.EDU_DIVISION = '고등학교' THEN edu.EDU_PERIOD END) AS high_school_PERIOD,
    MAX(CASE WHEN edu.EDU_DIVISION = '고등학교' THEN edu.EDU_NAME END) AS high_school_NAME,
    MAX(CASE WHEN edu.EDU_DIVISION = '고등학교' THEN edu.EDU_MAJOR END) AS high_school_MAJOR,
    MAX(CASE WHEN edu.EDU_DIVISION = '고등학교' THEN edu.EDU_STATUS END) AS high_school_STATUS,
    MAX(CASE WHEN edu.EDU_DIVISION = '대학교' THEN edu.EDU_PERIOD END) AS university_PERIOD,
    MAX(CASE WHEN edu.EDU_DIVISION = '대학교' THEN edu.EDU_NAME END) AS university_NAME,
    MAX(CASE WHEN edu.EDU_DIVISION = '대학교' THEN edu.EDU_MAJOR END) AS university_MAJOR,
    MAX(CASE WHEN edu.EDU_DIVISION = '대학교' THEN edu.EDU_STATUS END) AS university_STATUS,
    MAX(CASE WHEN edu.EDU_DIVISION = '대학원' THEN edu.EDU_PERIOD END) AS graduate_school_PERIOD,
    MAX(CASE WHEN edu.EDU_DIVISION = '대학원' THEN edu.EDU_NAME END) AS graduate_school_NAME,
    MAX(CASE WHEN edu.EDU_DIVISION = '대학원' THEN edu.EDU_MAJOR END) AS graduate_school_MAJOR,
    MAX(CASE WHEN edu.EDU_DIVISION = '대학원' THEN edu.EDU_STATUS END) AS graduate_school_STATUS,
    exp.EXP_NO AS experience_NO,
    exp.EXP_NAME AS experience_NAME,
    exp.EXP_DEPT AS experience_DEPT,
    exp.EXP_RANK AS experience_RANK,
    exp.EXP_DUTY AS experience_DUTY,
    exp.EXP_PERIOD AS experience_PERIOD,
    cer.CERTI_NO AS certification_NO,
    cer.CERTI_CATE AS certification_CATE,
    cer.CERTI_CODE AS certification_CODE,
    cer.CERTI_ISSUER AS certification_ISSUER,
    cer.CERTI_ACQUIRE AS certification_ACQUIRE
FROM
    EMPLOYEE emp
        LEFT JOIN
    EDUCATION edu ON emp.E_NO = edu.E_NO
        LEFT JOIN (
        SELECT
            E_NO,
            EXP_NO,
            EXP_NAME,
            EXP_DEPT,
            EXP_RANK,
            EXP_DUTY,
            EXP_PERIOD
        FROM
            EXPERIENCE
    ) exp ON emp.E_NO = exp.E_NO
        LEFT JOIN (
        SELECT
            E_NO,
            CERTI_NO,
            CERTI_CATE,
            CERTI_CODE,
            CERTI_ISSUER,
            CERTI_ACQUIRE
        FROM
            CERTIFICATION
    ) cer ON emp.E_NO = cer.E_NO
GROUP BY
    emp.E_NO, emp.E_NAME, emp.E_ADDRESS, emp.E_AUTH, emp.E_STATUS, emp.E_EMAIL, emp.E_GENDER, emp.E_HIREDATE, emp.E_ENDDATE, emp.E_PASSWORD, emp.E_PHONE, emp.E_RANK, emp.E_BIRTH, emp.DEPT_NAME, emp.E_OCCUP,
    exp.EXP_NO, exp.EXP_NAME, exp.EXP_DEPT, exp.EXP_RANK, exp.EXP_DUTY, exp.EXP_PERIOD,
    cer.CERTI_NO, cer.CERTI_CATE, cer.CERTI_CODE, cer.CERTI_ISSUER, cer.CERTI_ACQUIRE
ORDER BY
    emp.E_NO;

결과

과정)의 문제를 해결하기 위해서 모든 값에 max() 함수를 사용하여 하나의 튜플에 모든 정보가 출력되도록 변경하였다.

SELECT
    emp.E_NO,
    emp.E_NAME,
    emp.E_ADDRESS,
    emp.E_AUTH,
    emp.E_STATUS,
    emp.E_EMAIL,
    emp.E_GENDER,
    emp.E_HIREDATE,
    emp.E_ENDDATE,
    emp.E_PASSWORD,
    emp.E_PHONE,
    emp.E_RANK,
    emp.E_BIRTH,
    emp.DEPT_NAME,
    emp.E_OCCUP,
    MAX(edu.HIGH_SCHOOL_PERIOD) AS HIGH_SCHOOL_PERIOD,
    MAX(edu.HIGH_SCHOOL_NAME) AS HIGH_SCHOOL_NAME,
    MAX(edu.HIGH_SCHOOL_MAJOR) AS HIGH_SCHOOL_MAJOR,
    MAX(edu.HIGH_SCHOOL_STATUS) AS HIGH_SCHOOL_STATUS,
    MAX(edu.UNIVERSITY_PERIOD) AS UNIVERSITY_PERIOD,
    MAX(edu.UNIVERSITY_NAME) AS UNIVERSITY_NAME,
    MAX(edu.UNIVERSITY_MAJOR) AS UNIVERSITY_MAJOR,
    MAX(edu.UNIVERSITY_STATUS) AS UNIVERSITY_STATUS,
    MAX(edu.GRADUATE_SCHOOL_PERIOD) AS GRADUATE_SCHOOL_PERIOD,
    MAX(edu.GRADUATE_SCHOOL_NAME) AS GRADUATE_SCHOOL_NAME,
    MAX(edu.GRADUATE_SCHOOL_MAJOR) AS GRADUATE_SCHOOL_MAJOR,
    MAX(edu.GRADUATE_SCHOOL_STATUS) AS GRADUATE_SCHOOL_STATUS,
    MAX(exp.EXP_NO) AS EXP_NO,
    MAX(exp.EXP_NAME) AS EXP_NAME,
    MAX(exp.EXP_DEPT) AS EXP_DEPT,
    MAX(exp.EXP_RANK) AS EXP_RANK,
    MAX(exp.EXP_DUTY) AS EXP_DUTY,
    MAX(exp.EXP_PERIOD) AS EXP_PERIOD,
    MAX(cer.CERTI_NO) AS CERTI_NO,
    MAX(cer.CERTI_CATE) AS CERTI_CATE,
    MAX(cer.CERTI_CODE) AS CERTI_CODE,
    MAX(cer.CERTI_ISSUER) AS CERTI_ISSUER,
    MAX(cer.CERTI_ACQUIRE) AS CERTI_ACQUIRE
FROM
    EMPLOYEE emp
        LEFT JOIN (
        SELECT
            E_NO,
            MAX(CASE WHEN EDU_DIVISION = '고등학교' THEN EDU_PERIOD END) AS HIGH_SCHOOL_PERIOD,
            MAX(CASE WHEN EDU_DIVISION = '고등학교' THEN EDU_NAME END) AS HIGH_SCHOOL_NAME,
            MAX(CASE WHEN EDU_DIVISION = '고등학교' THEN EDU_MAJOR END) AS HIGH_SCHOOL_MAJOR,
            MAX(CASE WHEN EDU_DIVISION = '고등학교' THEN EDU_STATUS END) AS HIGH_SCHOOL_STATUS,
            MAX(CASE WHEN EDU_DIVISION = '대학교' THEN EDU_PERIOD END) AS UNIVERSITY_PERIOD,
            MAX(CASE WHEN EDU_DIVISION = '대학교' THEN EDU_NAME END) AS UNIVERSITY_NAME,
            MAX(CASE WHEN EDU_DIVISION = '대학교' THEN EDU_MAJOR END) AS UNIVERSITY_MAJOR,
            MAX(CASE WHEN EDU_DIVISION = '대학교' THEN EDU_STATUS END) AS UNIVERSITY_STATUS,
            MAX(CASE WHEN EDU_DIVISION = '대학원' THEN EDU_PERIOD END) AS GRADUATE_SCHOOL_PERIOD,
            MAX(CASE WHEN EDU_DIVISION = '대학원' THEN EDU_NAME END) AS GRADUATE_SCHOOL_NAME,
            MAX(CASE WHEN EDU_DIVISION = '대학원' THEN EDU_MAJOR END) AS GRADUATE_SCHOOL_MAJOR,
            MAX(CASE WHEN EDU_DIVISION = '대학원' THEN EDU_STATUS END) AS GRADUATE_SCHOOL_STATUS
        FROM
            EDUCATION
        GROUP BY
            E_NO
    ) edu ON emp.E_NO = edu.E_NO
        LEFT JOIN (
        SELECT
            E_NO,
            MAX(EXP_NO) AS EXP_NO,
            MAX(EXP_NAME) AS EXP_NAME,
            MAX(EXP_DEPT) AS EXP_DEPT,
            MAX(EXP_RANK) AS EXP_RANK,
            MAX(EXP_DUTY) AS EXP_DUTY,
            MAX(EXP_PERIOD) AS EXP_PERIOD
        FROM
            EXPERIENCE
        GROUP BY
            E_NO
    ) exp ON emp.E_NO = exp.E_NO
        LEFT JOIN (
        SELECT
            E_NO,
            MAX(CERTI_NO) AS CERTI_NO,
            MAX(CERTI_CATE) AS CERTI_CATE,
            MAX(CERTI_CODE) AS CERTI_CODE,
            MAX(CERTI_ISSUER) AS CERTI_ISSUER,
            MAX(CERTI_ACQUIRE) AS CERTI_ACQUIRE
        FROM
            CERTIFICATION
        GROUP BY
            E_NO
    ) cer ON emp.E_NO = cer.E_NO
GROUP BY
    emp.E_NO, emp.E_NAME, emp.E_ADDRESS, emp.E_AUTH, emp.E_STATUS, emp.E_EMAIL, emp.E_GENDER, emp.E_HIREDATE, emp.E_ENDDATE, emp.E_PASSWORD, emp.E_PHONE, emp.E_RANK, emp.E_BIRTH, emp.DEPT_NAME, emp.E_OCCUP
ORDER BY
    emp.E_NO;