[SQL] SELF, OUTER JOIN (21일차)

<오늘의 학습>

1. SELF JOIN

2. LEFT JOIN (OUTER JOIN)

3. 시간 함수

 

 

1. SELF JOIN

▶ 같은 테이블을 조인하는 방법이다.

SELECT E.ENAME AS 본인, E2.ENAME AS 팀장
FROM EMP E
INNER JOIN EMP E2 ON E.MGR = E2.EMPNO
WHERE E.ENAME = 'SMITH';

 

 

---

<EMP> 테이블

EMP

 

▷ 예제 1) 부하직원이 가장 많은 사람의 사번, 이름, 부하직원 수 출력

▶ MANAGER 기준으로 부하직원의 수를 구한 뒤, 최댓값을 찾기 위해 내림차순 정렬을 한다.

SELECT MGR, COUNT(MGR) AS CNT
FROM EMP
GROUP BY MGR
ORDER BY CNT DESC;

 

▶ 최댓값을 구한 테이블을 JOIN 한다

SELECT E.EMPNO AS 사번, E.ENAME AS 팀장이름, E2.CNT AS 부하직원수
FROM EMP E
INNER JOIN (
    SELECT MGR, COUNT(MGR) AS CNT
    FROM EMP
    GROUP BY MGR
    ORDER BY CNT DESC
)E2 ON E.EMPNO = E2.MGR
WHERE ROWNUM = 1;

 

 

---

※ WHERE ROWNUM=1 : 최상위 값을 하나 가져온다

SELECT *
FROM STUDENT
WHERE ROWNUM=1; --첫번째 값을 가져온다

 

 

---

▶ ROWNUM을 사용하지 않고 서브 쿼리를 이용한 코드

SELECT E.EMPNO AS 사번, E.ENAME AS 팀장이름, E2.CNT AS 부하직원수
FROM EMP E
INNER JOIN (
    SELECT MGR, COUNT(MGR) AS CNT
    FROM EMP
    GROUP BY MGR
    HAVING COUNT(MGR) = (
        SELECT MAX(CNT)
        FROM (
            SELECT MGR, COUNT(MGR) AS CNT
            FROM EMP
            GROUP BY MGR
        )
    )
) E2 ON E.EMPNO = E2.MGR;

• 부하직원의 수를 구한 테이블을 이용해 최댓값을 찾는다.
• 최댓값을 찾은 값으로 다시 부하직원의 수 중 최댓값과 일치하는 CNT를 가진 테이블을 구한다.

 

 

 

---

▷ 예제2) 부하직원의 평균 급여가 가장 높은 사람의 사번, 이름, 급여 평균 출력

▶ MANAGER 기준으로 부하직원의 평균 급여를 구한 뒤, 최댓값을 찾기 위해 내림차순 정렬을 한다.

SELECT MGR, AVG(SAL) AS AV
FROM EMP 
GROUP BY MGR
ORDER BY AV DESC;

 

 

▶ NULL이 아닌 값만을 조회하는 조건을 추가한다.

SELECT MGR, AVG(SAL) AS AV
FROM EMP 
WHERE MGR IS NOT NULL
GROUP BY MGR
ORDER BY AV DESC;

 

 

▶ 평균의 최댓값을 구한 테이블을 JOIN 한다

SELECT E.EMPNO AS 사번, E.ENAME AS 이름, E2.AV AS 평균
FROM EMP E
INNER JOIN(
    SELECT MGR, AVG(SAL) AS AV
    FROM EMP 
    GROUP BY MGR
    ORDER BY AV DESC
) E2 ON E.EMPNO = E2.MGR
WHERE ROWNUM = 1;

 

 

 

 

실습을 위한 <EMP> 테이블 수정

INSERT INTO EMP VALUES(1234,'HONG','SALESMAN',7698,SYSDATE, 3500,1000,NULL);
INSERT INTO EMP VALUES(9876,'KIM','CLERK',7902,SYSDATE,3000,500,NULL);

 

EMP

 

 

 

2. LEFT JOIN (LEFT OUTER JOIN)

• 값이 조회되지 않는 NULL 값이라도 JOIN된 결과를 출력해준다.
• 메인 테이블을 기준으로 LEFT와 RIGHT의 결과가 다르게 출력되며, RIGHT OUTER JOIN보다 LEFT OUTER JOIN이 많이 사용된다.
• OUTER는 생략이 가능하다.

 

▶ EMP&DEPT 테이블 조인을 통한 INNER/OUTER JOIN의 차이

▶ INNER JOIN

SELECT *
FROM EMP E
INNER JOIN DEPT D ON E.DEPTNO = D.DEPTNO;

 

▶ LEFT OUTER JOIN

SELECT *
FROM EMP E
LEFT OUTER JOIN DEPT D ON E.DEPTNO = D.DEPTNO;

• DEPTNO 값이 없어도 EMP에 있는 값이 모두 출력되었다. (ENAME : HONG, KIM)

 

 

 

---

▷ 예제 1) STUDENT 테이블에서 한 번도 시험을 보지 않은 학생을 구하시오

SELECT S.*
FROM STUDENT S
LEFT JOIN ENROL E ON S.STU_NO = E.STU_NO
WHERE E.ENR_GRADE IS NULL;

• <ENROL> 테이블에서 시험 성적이 없는 학생은 한 번도 시험을 보지 않은 학생이다.
• 성적의 횟수가 NULL인 값을 받아오면 한 번도 시험을 보지 않은 학생을 알 수 있다.

 

▶ 출력 결과

 

 

---

▷ 예제 2) STUDENT 테이블에서 시험을 한 번 이하로 본 학생을 구하시오.

SELECT S.*, CNT
FROM STUDENT S
LEFT JOIN (
    SELECT STU_NO, COUNT(STU_NO) AS CNT
    FROM ENROL
    GROUP BY STU_NO
) E ON S.STU_NO = E.STU_NO
WHERE CNT = 1
    OR E.CNT IS NULL;

• <ENROL> 테이블에서 시험 성적이 없거나 한 개만 있는 학생을 구한다.

 

▶ 출력 결과

 

 

 

※ 다른 풀이 방법) 시험을 한 번 보거나 안 본 사람은 1으로 잡힌다.

SELECT S.STU_NO, COUNT(*)
FROM STUDENT S
LEFT OUTER JOIN ENROL E ON S.STU_NO = E.STU_NO
GROUP BY S.STU_NO
HAVING COUNT(*) < 2;

 

 

 

 

 

다음 학습

[SQL] 시간 함수 (21일차)