SQL - scott(개념 + 실습) JOIN

JOIN : 데이터가 여러 개의 테이블에 나뉘어 저장됨

• 내부조인(INNER JOIN)
• 자체조인(SELF JOIN)
• 외부조인(OUTER JOIN)
  • LEFT OUTER JOIN
  • RIGHET OUTER JOIN
  • FULL OUTER JOIN
• 상호조인(CROSS JOIN)
• UNION/ UNION ALL/ NOT IN/ IN

내부조인과 외부조인 정확하게 구분하기

 

(1) 내부조인 

• 등가조인, 단순조인으로 부르기도함
• 가장 많이 사용 되는 조인 방식
• LEFT TABLE과 RIGHT TABLE이 일치하는 컬럼을 기준으로 값 가져오기

(2) 자체조인 : 자기 자신과 자기 자신이 조인하는 방식

(3) 외부조인

• LEFT OUTER JOIN과 RIGHT OUTER JOIN의 차이점 구별하기
  • LEFT OUTER JOIN : 일치하지 않은 데이터를 가지고 올 때 왼쪽 테이블 기준
  • RIGHT OUTER JOIN : 일치하지 않은 데이터를 가지고 올 때 오른쪽 테이블 기준
• 일치 하지 않는 값도 가지고 옴.(내부조인과의 차이점)

 

※ 내부조인 :  일치하는 컬럼을 기준으로 값 가져오기

1) emp, dept에서 deptno 출력

SELECT
    empno,
    ename,
    deptno,
    dname,
    loc 
FROM
    emp  e,
    dept d 
WHERE
    e.deptno = d.deptno;

 column ambiguously defined 에러 메세지(emp에도 deptno가 있고 dept에도 deptno가 있는데 어떤걸 가지고 와야할까?)

 

1-1) 오류 수정. 정확하게 명시 해주기

SELECT
    e.empno,
    e.ename,
    e.deptno,
    dname,
    loc  --둘다 들어있는 경우에는 확실히 알려주기
FROM
    emp  e,
    dept d --연결할 테이블 나열 , 별칭 붙이기
WHERE
    e.deptno = d.deptno;  --조인의 조건

여기도 들어있고 저기도 들어있는 컬럼은 어떤 테이블인지 명확하게 알려주기

 

1-2) 표준문법(inner 생략가능)

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

 

실습) 부서번호가 일치하는 사원들의 정보 출력. 급여가 3000 이상자만 출력

SELECT
    e.empno,
    e.ename,
    e.deptno,
    dname,
    loc
FROM
         emp e
    INNER JOIN dept d ON e.deptno = d.deptno
                         AND sal >= 3000;

 

실습) 부서번호가 일치하는 사원들의 정보 출력. 급여가 2500 이하이고, 사원번호가 9999이하인 사원 출력

SELECT
    e.empno,
    e.ename,
    e.deptno,
    d.dname,
    d.loc
FROM
    emp  e,
    dept d
WHERE
        e.deptno = d.deptno
    AND e.sal >= 2500
    AND e.empno <= 9999;

 

실습 -1) join문 사용

SELECT
    e.empno,
    e.ename,
    e.deptno,
    d.dname,
    d.loc
FROM
         emp e
    JOIN dept d ON e.deptno = d.deptno
                   AND e.sal >= 2500
                   AND e.empno <= 9999;

 

 

※ 비등가 조인 : 값이 같진 않지만 일정 범위안에 들어있는 테이블을 연결

2) emp, salgrade 조인

SELECT
    empno,
    ename,
    job,
    sal,
    comm,
    deptno,
    grade
FROM
         emp e
    JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal;

JOIN 테이블 ON 조건 BETWEEN 컬럼 AND 컬럼 

 

※ 셀프 조인 : 자기 자신 테이블 조인

3) emp 셀프조인

SELECT
    e1.empno,
    e1.ename,
    e1.mgr,
    e2.empno AS mgr_empno,
    e2.ename AS mgr_ename
FROM
    emp e1,
    emp e2
WHERE
    e1.mgr = e2.empno;

 

※ 외부 조인 : 연결이 안된 것도 가지고 옴

4) LEFT OUTER JOIN

SELECT
    e1.empno,
    e1.ename,
    e1.mgr,
    e2.empno AS mgr_empno,
    e2.ename AS mgr_ename
FROM
    emp e1
    LEFT OUTER JOIN emp e2 ON e1.mgr = e2.empno;

 

5) RIGHT OUTER JOIN

SELECT
    e1.empno,
    e1.ename,
    e1.mgr,
    e2.empno AS mgr_empno,
    e2.ename AS mgr_ename
FROM
    emp e1
    RIGHT OUTER JOIN emp e2 ON e1.mgr = e2.empno;

 

실습) 각 부서별 평균급여, 최대급여, 최소급여, 사원수 출력. 부서 코드, 부서명, 평균급여, 최대급여, 최소급여, 사원수

SELECT
    d.deptno,
    d.dname,
    floor(AVG(sal)),
    MAX(sal),
    MIN(sal),
    COUNT(*)
FROM
         emp e
    JOIN dept d ON e.deptno = d.deptno
GROUP BY
    d.deptno,
    d.dname;

 

실습) 모든 부서정보와 사원 정보를 출력(부서번호의 오름차순, 사원번호의 오름차순). 부서번호, 부서명, 사원번호, 사원명, 직책, 급여

SELECT
    d.deptno,
    d.dname,
    e.empno,
    e.ename,
    e.job,
    e.sal
FROM
    emp  e
    RIGHT OUTER JOIN dept d ON e.deptno = d.deptno
ORDER BY
    d.deptno,
    e.empno;

 

6) 세 개의 테이블 조인

SELECT
    e1.empno,
    e2.empno,
    e3.empno
FROM
         emp e1
    JOIN emp e2 ON e1.empno = e2.empno
    JOIN emp e3 ON e2.empno = e3.empno;

 

실습) 모든 부서정보와 사원 정보, 급여 등급 정보, 각 사원의 직속 상관의 정보 부서번호, 사원번호 순으로 정렬하여 출력

SELECT
    d.deptno,
    d.dname,
    e1.empno,
    e1.ename,
    e1.mgr,
    e1.sal,
    e1.deptno AS deptno_1,
    s.losal,
    s.hisal,
    s.grade,
    e2.empno  AS mgr_empno,
    e2.ename  AS mgr_ename
FROM
    emp      e1
    RIGHT OUTER JOIN dept     d ON e1.deptno = d.deptno
    LEFT OUTER JOIN salgrade s ON e1.sal BETWEEN s.losal AND s.hisal
    LEFT OUTER JOIN emp      e2 ON e1.mgr = e2.empno
ORDER BY
    d.deptno,
    e1.empno;