07_조인

[ 조인 목차 ]

1. 조인의 필요성

2. Cross Join

3. Equi Join

4. Non-Equi Join

5. Self Join

6. Outer Join

7. ANSI Join

    1) ANSI Cross Join

    2) ANSI Inner Join

    3) ANSI Outer Join

 

* 2 ~ 6 번까지는 오라클에서만 사용하는 것

  7번은 미국 어쩌구에서 사용하는 것을 따와서 만든 것,,

  7번은 다른 데이터베이스도 공용해서 사용할 수 있는것

  ANSI Cross Join 는 Cross Join

  ANSI Inner Join 는 Equi Join

  ANSI Outer Join 는 Outer Join 과 비슷(?) 하다.

 

 

 

< 새로 알게 된 사실..!! >

[ EMP 테이블의 컬럼들 ]

EMPNO : 사원번호

ENAME : 본인의 이름

JOB : 직무

MGR : 나의 상사번호

HIREDATE : 입사일

SAL : 급여

COMM : 보너스로 받는 돈,,?

DEPTNO : 부서코드

 

[ DEPT 테이블의 컬럼들 ]

DEPTNO : 부서코드

DNAME : 부서이름

LOC : 근무지

* ex) DEPTNO = 10 즉, 부서코드가 10 이면

        DNAME = ACCOUNTING 즉, 부서이름은 ACCOUNTING 이다 .

 

 

 

 

[ 1 ] 조인의 필요성

- 특정 부서 번호에 대한 부서이름이 무엇인지는 부서(DEPT) 테이블에서 알 수 있다.

  특정 사원에 대한 부서명을 알아내기 위해서는 부서 테이블에서 정보를 얻어 와야 한다.

- SQL에서는 두 개 이상의 테이블을 결합해야만 원하는 결과를 얻을 수 있을 때

  한 번의 질의로 원하는 결가를 얻을 수 있는 조인 기능을 제공한다.

 

  * 테이블과 테이블을 연결하는 것 : 조인(JOIN)

    ex) KING 이 어느 부서에 있는지 알고 싶으면 EMP 테이블과 DEPT 테이블을 조인하면 된다!

 

  * 이렇게 테이블을 서로 연결 시키는 것을 관계형 데이터베이스(RDBMS) 라고 한다.

[ EMP 테이블과 DEPT 테이블을 연결해서 즉, 조인해서 어떤 사람이 어떤 부서명에 있는지 알고 싶다! ]

 

 

 

-- 아래 쉅 코드 내용 --

SELECT * FROM EMP; -- 부서번호만 알 수 있고 부서명은 DEPT 테이블에서 볼 수 있다.
SELECT * FROM DEPT;
-- 이 둘의 테이블을 연결하면 어떤 사람이 어떤 부서명인지 알 수 있다.

-- JOIN 조인을 안하고 SMITH가 어느 부서에 있는지 알려고 할 때 두번의 코드를 거쳐야 한다.
SELECT DEPTNO FROM EMP
WHERE ENAME='SMITH';
SELECT DNAME FROM DEPT
WHERE DEPTNO=20;

-- 조인을 하면 코드를 한번만 적어서 내가 원하는걸 알아낼 수 있다. 
SELECT EMP.ENAME, DEPT.DNAME
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO AND
EMP.ENAME = 'SMITH';


SELECT *
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO AND
EMP.ENAME = 'SMITH';
-- DEPTNO_1, DNAME, LOC 는 DEPT 테이블에 있는 컬럼, 그외 나머지는 EMP 테이블에 있는 것들

 

 

 

 

---

 

[ 2 ] Cross Join

- 특별한 키워드 없이 SELECT 문의 FROM 절에 사원(EMP) 테이블과

  부서(DEPT) 테이블을 콤마로 연결하여 연속하여 기술하는 것이다.

  * 서로의 연관관계가 없어서 잘 사용하지 않는다,,?

     서로 관계가 없는 데이터들도 조인되어버려서, 연결되어버려서

     CROSS JOIN은 잘 안사용함! 

--1. ORACLE CROSS JOIN
SELECT *
FROM EMP, DEPT;
-- 두개의 테이블 EMP(14개), DEPT(4개) 에서 모든 컬럼(14*4=56개의 컬럼)을 가져온다.

 

 

---

 

[ 3 ] Equi Join

- EQUI JOIN 은 가장 많이 사용하는 조인 방법이다.

- 조인 대상이 되는 두 테이블에서 공통적으로 존재하는

  컬럼의 값이 일치하는 행을 연결하여 결과를 생성하는 조인 방법

 

  * CROSS JOIN에 WHERE 절만 적어주면 EQUI JOIN이 된다!

    

SELECT *
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO;

SELECT ENAME, DNAME
FROM EMP, DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO
AND ENAME='SCOTT';

SELECT ENAME, DNAME, DEPTNO -- 오류발생, 컬럼명의 모호성 문제
FROM EMP, DEPT 
WHERE EMP.DEPTNO = DEPT.DEPTNO
AND ENAME='SCOTT';

SELECT EMP.ENAME, DEPT.DNAME, EMP.DEPTNO -- 컬럼명의 모호성 해결
FROM EMP, DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO
AND ENAME='SCOTT';

SELECT E.ENAME, D.DNAME, E.DEPTNO, D.DEPTNO 
FROM EMP E, DEPT D -- 테이블에 별칭을 부여함
WHERE E.DEPTNO = D.DEPTNO
AND E.ENAME='SCOTT';

-- 아래 쉅 내용 코드 --
--2. ORACLE EQUI JOIN
SELECT *
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO;

SELECT ENAME, DNAME
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
AND ENAME='SCOTT';

SELECT ENAME, DNAME, DEPTNO
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
AND ENAME='SCOTT';
-- 오류 발생) ORA-00918: column ambiguously defined
-- DEPTNO가 EMP 테이블에도 DEPT 테이블에도 공통으로 있다.
-- 그래서 DEPTNO 를 가져오라고 한 코드에서 어떤 테이블에 있는 DEPTNO를 가져오는건지
-- 모호해서 오류가 발생한 것! --> 명확하게 어느 테이블에서 가져오는지 고쳐주면 해결

SELECT EMP.ENAME, DEPT.DNAME, DEPT.DEPTNO
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
AND ENAME='SCOTT';

-- 이렇게 명확하게 어느 테이블에서 가져오는지 다 적어주다보니
-- 만일 테이블 이름이 길면,, 너무 힘들다!!
-- 그럴땐 가명을 별칭을 부여해줘서 적으면 훨씬 간결하게 적을 수 있다.
SELECT E.ENAME, D.DNAME, D.DEPTNO
FROM EMP E, DEPT D  -- 별칭 부여해줌
WHERE E.DEPTNO = D.DEPTNO
AND ENAME='SCOTT';

 

 

 

 

 

 

 

---

 

 

[ 과제 07-01 ]

 

Q1. 조인을 사용하여 뉴욕에서 근무하는 사원의 이름과 급여을 출력하세요.

A1.

 

Q2. 조인을 사용하여 ACCOUNTING 부서 소속 사원의 이름과 입사일을 출력하세요.

A2.

 

 

Q3. 직급이 MANAGER인 사원의 이름, 부서명을 출력하세요.

A3.

 

 

 

 

 

---

 

[ 4 ] Non-Equi Join

- Non-Equi Join 은 조인 조건에 특정 범위 내에 있는지를 조사하기 위해

  WHERE 절에 조인 조건을 = 연산자 이외의 비교 연산자를 사용한다.

 

-- 아래는 쉅 내용 코드 --
SELECT * FROM SALGRADE;
-- SALGRADE 테이블의 GRADE 컬럼의 값이 1~5까지 있다.

SELECT E.ENAME, E.SAL, S.GRADE
FROM EMP E, SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL;
-- SAL 범위가 LOSAL과 HISAL에 있는 ENAME, SAL, GRADE 출력

SELECT E.ENAME, E.SAL, S.GRADE
FROM EMP E, SALGRADE S
WHERE E.SAL >= S.LOSAL AND E.SAL <= S.HISAL;
--WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL; 처럼 BETWEEN 대신 위처럼 비교연산자로 사용할 수 도 있다.
-- 위 처럼 <=, >= 연산자 말고도 <>, < 등 이러한 비교 연산자도 적을 수 있다.

 

 

 

 

 

---

 

[ 5 ] Self Join

- 조인은 두 개 이상의 서로 다른 테이블을 서로 연결하는 것뿐만 아니라,

  하나의 테이블 내에서 조인을 해야만 원하는 결과 자료를 얻는 경우도 생긴다.

  Self Join은 말 그대로 자기 자신과 조인을 맺는 것을 말한다.

 

 

 

SELECT EMPLOYEE.ENAME || '의 매니저는 ' || MANAGER.ENAME || '입니다.'
FROM EMP EMPLOYEE, EMP MANAGER
WHERE EMPLOYEE.MGR = MANAGER.EMPNO;
-- 매니저가 상사,,?

-- 아래는 쉅 내용 코드 --
SELECT EMPNO, ENAME, MGR FROM EMP;

SELECT E.ENAME AS "사원명", M.ENAME "상사명"
FROM EMP E, EMP M
WHERE E.MGR = M.EMPNO;
-- MGR : 상사번호
-- EMPNO : 사원번호

 

[ SMITH 자신의 번호(EMPNO)는 7369이고,                     SMITH의 상사번호(MGR)는 7902이다.        KING은 상사번호MGR에 NULL값이 들어있다.        이 말은 KING의 상사는 없다는 것!]

 

 

 

 

 

 

 

 

---

 

[ 과제 07-03 ]

 

Q1.매니저가 KING인 사원들의 이름과 직급을 출력하세요.

A1.

[ 내가 푼 코드 ]

 

 

[ 강사님 코드 ]

 

 

 

 

 

Q2. SCOTT과 동일한 근무지에서 근무하는 사원의 이름을 출력하세요.

A2.

[ 내가 푼 코드 ]

---- 내가 푼 코드는 DEPTNO 으로 같다고 한것이라서 문제 의도에 맞지 않다!! -----

---- DEPTNO으로 한다는 건 이미 결과를 보고 거기에서 SCOTT의 DEPTNO가 20 임을

      보고 그걸 가지고 코드를 짠것!! 그렇게 하지 말고 애초에 아무것도 모르고 그냥

      SCOTT과 같은 근무지에서 일하는 동료들의 이름을 출력하는게 문제의 의도!! -----

 

 

[ 강사님 코드 ]

 

[ 코드 해설,,? DEPTNO 이 같으면 근무지도 같다. DEPTNO에 따라서 근무지가 결정되는 것! ]

 

 

---

 

[ 6 ] Outer Join  ( 잘 사용 안함,,)

- Outer Join ( 외부 조인 )

  : 조인 조건에 만족하지 못하지만 해당 로우를 나타내고 싶을 때 사용하는 외부 조인 방법

- 외부 조인은 NULL 값이기 때문에 배제된 행을 결과에 포함시킬 수 있으며

  "(+)" 기호를 조인 조건에서 정보가 부족한 컬럼이름 뒤에 덧붙인다.

 

 

SELECT EMPLOYEE.ENAME || '의 매니저는 ' || MANAGER.ENAME || '입니다.'
FROM EMP EMPLOYEE, EMP MANAGER
WHERE EMPLOYEE.MGR = MANAGER.EMPNO(+);

-- 아래 수업 내용 코드 --

-- 7-6. Outer Join : 조인 조건에 만족하지 못하더라도 해당 로우를 나타내려고 할 때 사용
SELECT E.ENAME, M.ENAME
FROM EMP E, EMP M
WHERE E.MGR = M.EMPNO;
-- ENAME : 사원이름 / ENAME_1 : 상사이름
-- KING 은 가장 높은 상사라서 KING의 상사는 없다!
-- 즉, KING의 상사번호(EMPNO)의 값이 NULL이라서
-- KING이 사원에는 안나타난다.
-- 그럴 때 KING은 저기에 조인 조건에 만족하지 못하지만 KING도 같이 출력하고 싶다!!
--    -> Outer Join 사용

-- Outer Join 사용
SELECT E.ENAME, M.ENAME
FROM EMP E, EMP M
WHERE E.MGR = M.EMPNO(+); -- LEFT OUTER JOIN 과 같은 결과
--> ENAME 에 KING도 포함해서 출력됨

SELECT E.ENAME, M.ENAME
FROM EMP E, EMP M
WHERE E.MGR(+) = M.EMPNO; -- RIGHT OUTER JOIN과 같은 결과

 

[ Outer Join 사용 안하고 출력한 결과 --> ENAME 사원 이름에 KING은 없다,, 왜냐하면 KING의 상사번호 EMPNO에 NULL이 있어서 MGR과 EMPNO이      같다는 조건에 KING은 충족하지 못해 KING은 출력되지 않는다. ]

 

 

[ Outer Join 사용한 결과 출력  --> ENAME 사원이름에 KING도 있다!! ]

 

 

 

 

---

 

[ 7 ] ANSI Join

- 오라클뿐만 아니라 현재 대부분의 상용 데이터베이스 시스템에서

  표준 언어로 ANSI(미국표준협회) SQL에서 제시한 표준 기능을 대부분

  준수하고 있다.

 

 

1. ASNI Cross Join

 

-- 아래는 쉅 내용 코드 --

-- ANSI CROSS JOIN = ORACLE CROSS JOIN
-- ORACLE CROSS JOIN (오라클에서만 사용가능)
SELECT *
FROM EMP, DEPT;

-- ANSI CROSS JOIN (다른 데이터베이스 ex. mysql 등... 에서도 사용 가능)
SELECT *
FROM EMP CROSS JOIN DEPT;

 

[ ORACLE CROSS JOIN 사용한 결과 ]

 

[ ANSI CROSS JOIN 사용한 결과 는 위의 ORACLE CROSS JOIN한 결과가 동일하다. ]

 

 

 

 

 

2. ANSI Inner Join

- Using 을 이용한 조인 조건(공통컬럼) 을 지정하기

- Natural Join

  : 자동적으로 모든 컬럼을 대상으로 공통 컬럼을 조사하여 내부적으로 조인문을 생성한다.

-- 아래는 쉅 내용 코드 --

-- ANSI INNER JOIN = ORACLE EQUI JOIN
-- ORACLE EQUI JOIN
SELECT E.ENAME, D.DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO;


-- ANSI INNER JOIN
-- : , 대신 INNER JOIN 사용하고 WHERE 대신 ON 사용함 그런데 ON 대신 USING을 사용할 수도 있음
SELECT E.ENAME, D.DNAME
FROM EMP E INNER JOIN DEPT D
ON E.DEPTNO = D.DEPTNO;

-- USING 사용
SELECT E.ENAME, D.DNAME
FROM EMP E INNER JOIN DEPT D
USING(DEPTNO);


-- ANSI NATURAL JOIN : 같은 이름으로 조인하는거니까 이름이 같은 것 끼리 조인시켜 주는 것
SELECT E.ENAME, D.DNAME
FROM EMP E NATURAL JOIN DEPT D;
-- 조건이 없어도 같은 DEPTNO끼리 조인시켜준다! NATURAL 짱 싱기,,,

 

[ ORACLE EQUI JOIN 사용 결과 출력 ]

 

[ ANSI INNER JOIN 사용 결과는 위의 ORACLE EQUI JOIN 결과와 동일하다.]

 

 

[ INNER JOIN에서 ON 대신 USING을      사용해서 적을 수도 있다. ]

 

[ NATURAL JOIN 코드 사용 -> NATURAL JOIN은 공통되는 컬럼의 조건문을 적지 않아도 알아서 공통된것 찾아 조인시켜준다!! ]

 

 

 

 

 

 

3. ANSI Outer Join (잘 사용 안함,,,)

- 새로운 ANSI 구문에서 Outer Join 은 LEFT Outer Join, RIGHT Outer Join 그리고

  FULL Outer Join 총 세가지 타입의 조인을 제공한다.

 

-- ANSI OUTER JOIN
DROP TABLE DEPT01;

CREATE TABLE DEPT01(
 DEPTNO NUMBER(2),
 DNAME VARCHAR2(14)
);

INSERT INTO DEPT01 VALUES(10,'ACCOUNTING');
INSERT INTO DEPT01 VALUES(20,'RESEARCH');
SELECT * FROM DEPT01;
-- DEPT01에는 DEPTNO=10, 20 인 애들이 들어있다

DROP TABLE DEPT02;

CREATE TABLE DEPT02(
 DEPTNO NUMBER(2),
 DNAME VARCHAR2(14)
);

INSERT INTO DEPT02 VALUES(10,'ACCOUNTING');
INSERT INTO DEPT02 VALUES(30,'SALES');
SELECT * FROM DEPT02;
-- DEPT02에는 DEPTNO=10, 30 인 애들이 들어있다

-- ANSI Left Outer Join
SELECT *
FROM DEPT01 LEFT OUTER JOIN DEPT02
ON DEPT01.DEPTNO = DEPT02.DEPTNO;
-- 왼쪽에 있는 DEPT01을 중심으로 DEPT02의 부족한 것들이 나온다,,?
-- DEPT01을 중심으로 DEPT01에는 DEPTNO=10,20인 애들이 있다
-- 그런데 DEPT02에는 DEPTNO=10은 있지만 DEPTNO = 20이 없어서
-- DEPTNO=20인 것에 대해서는 그냥 NULL 값으로 나온다.

-- ANSI Right Outer Join
SELECT *
FROM DEPT01 RIGHT OUTER JOIN DEPT02
USING(DEPTNO);
-- 오른쪽에 있는 DEPT02를 중심으로 왼쪽 DEPT01에 없는 것이 나온다,,?
-- DEPT02에는 DEPTNO=10,30인 애들이 있다.
-- 그래서 기준인 DEPT02에 대한 DEPTNO =10, 30에 대해서 출력될것이다.
-- 그런데 DEPT01에는 DETPNO=10은 있지만 DEPTNO=30는 없다
-- 그래서 DEPTNO=30 에 대해서는 DNAME이 NULL로 나온 것이다.
-- DEPTNO 은 DEPT02이 기준이므로 10과 30이 출력
-- DNAME 은 DEPT01에 대한 값 출력
-- DNAME_1 은 DEPT02에 대한 값 출력
-- DEPT02는 DEPTNO 10과 30에 대한 DNAME 값이 있으므로 그 해당하는 값 출력
-- DEPT01은 DEPTNO 10에 대한 값 ACCOUNTING 은 있지만 DEPTNO 30에 대한 DNAME 값은 없으므로 NULL로 나옴


-- Full Outer Join
SELECT *
FROM DEPT01 FULL OUTER JOIN DEPT02
USING(DEPTNO);
-- 서로간에 부족한 것들을 다 보여줘!!
-- DEPT01에는 DEPTNO= 30 인 애가 없고, DEPT02에는 DEPTNO=20인 애가 없다
-- 얘네 다 포함해서 출력함

 

[ DEPT01 테이블에 대한 컬럼 출력 ]

 

[ DEPT02 테이블에 대한 컬럼 출력 ]

 

[ LEFT OUTER JOIN 사용, DEPT01 테이블 기준 ]

 

 

[ RIGHT OUTER JOIN 사용, DEPT02 테이블 기준 ]

 

[ FULL OUTER JOIN 사용 ]

 

 

 

 

 

 

---

 

[ 과제 07-02 ]

 

Q1. 직급이 MANAGER인 사원의 이름, 부서명을 출력하는 SQL문을 작성 하세요.

       (ORACLE EQUI JOIN, ANSI INNER JOIN, ANSI NATURAL JOIN을 사용하여 처리)

 

A1.

[ ORACLE EQUI JOIN 사용 ]

 

[ ANSI INNER JOIN 사용 ]

 

[ ANSI NATURAL JOIN 사용 ]

 

 

 

 

Q2. SMITH와 동일한 직급(JOB컬럼)을 가진 사원의 이름과 직급을 출력하는 SQL문을 작성 하세요

A2.

[ 내가 푼 코드 ]