[학습일지]JAVA교육일지 49일차

인천일보아카데미/- 학습일지

[학습일지]JAVA교육일지 49일차

w1z 2022. 6. 21. 15:05

조인, JOIN

[ 정의 ]

- (서로 관계를 맺고 있는) 2개(1개) 이상의 테이블의 내용을 동시에 가져와서 1개의 결과셋을 만드는 작업

- 조인에서 컬럼은 반드시 테이블명(소유주)을 적는게 좋다.(가독성때문에..)

[ 종류 ]

1. 단순 조인, CROSS JOIN

2. 내부 조인, INNER JOIN

3. 외부 조인, OUTER JOIN

4. 전체 외부 조인, FULL OUTER JOIN

5. 셀프 조인, SELF JOIN

1. 단순 조인, CROSS JOIN

[ 정의 ]

- 사용을 거의 안한다. --> WHY? 결과셋에 유효한 레코드와 유효하지 않은 레코드가 뒤섞여 있기 때문이다.

- 가끔씩 개발자가 테스트 용도의 큰 데이터가 필요한 경우에 사용한다.(더미 데이터 - 유효성과 무관)

방법1. 
SELECT * FROM tableA CROSS JOIN tableB;

방법2.
SELECT * FROM tableA, tableB;

[ 예제 ]

< 단순 조인, CROSS JOIN >

SELECT * FROM tableA; -- 3건
SELECT * FROM tableB;  -- 9건

SELECT * FROM tableA CROSS JOIN tableB; --27건
SELECT * FROM tableA, tableB; --27건

해설)
tableA 테이블에 해당되는 3건의 데이터가 A,B,C 라고 하고
tableB 테이블에 해당되는 9건의 데이터가 a,b,c,d,e,f,g,h,i 라고 한다면,
A -> a,b,c,d,e,f,g,h,i
B -> a,b,c,d,e,f,g,h,i
C -> a,b,c,d,e,f,g,h,i

총 27행이 출력된다.

2. 내부 조인, INNER JOIN

[ 정의 ]

- 단순 조인의 결과셋에서 유효한 레코드만 추출한 조인이다.

- 2개의 테이블에 ON 조건을 만족하는 레코드만 반환한다.

- 내부 조인의 결과는 대부분 자식 레코드 수 만큼 나온다.

- 두 테이블의 교집합

SELECT * FROM tableA INNER JOIN tableB ON tableA.PK = tableB.FK;

[ 예제 ]

< 내부 조인, INNER JOIN >

예제1) 2개의 테이블에서(tblCustomer, tblSales) 고객 정보와 판매 내역을 동시에 가져오기
SELECT  
    C.NAME,    --고객 이름
    C.ADDRESS, --고객 주소
    S.ITEM,    --상품명
    S.QTY      --상품수량
FROM tblCustomer C
    INNER JOIN tblSales S
        ON C.SEQ = S.CSEQ; --> SEQ는 tblCustomer의 PK값이며, CSEQ는 tblSales의 FK값이다.

예제2) 2개의 테이블에서(tblStaff, tblProject) 직원의 이름, 급여, 프로젝트명을 가져오기
SELECT
    S.NAME AS 직원명,
    S.SALARY AS 급여,
    P.PROJECT AS 프로젝트명
FROM tblSaff S 
    INNER JOIN tblProject P
        ON S.SEQ = P.STAFF_SEQ; --> SEQ는 S의 PK값, STAFF_SEQ는 P의 FK값


예제3) 3개의 테이블(EMPLOYEES, DEPARTMENTS, LOCATIONS)에서 직원의 이름, 부서명, 지역(+ 도로주소) 가져오기

SELECT
    E.FIRST_NAME || ' ' || E.LAST_NAME AS NAME,
    D.DEPARTMENT_NAME, 
    L.CITY || ' ' || L.STREET_ADDRESS
FROM EMPLOYEES E
    INNER JOIN DEPARTMNETS D
        ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
            INNER JOIN LOCATIONS L
                ON D.LOCATION_ID = L.LOCATION_ID;


예제4) 3개의 테이블(tblMember, tblRent, tblVideo)에서 손님이름, 비디오제목, 반납여부 가져오기
SELECT
    M.NAME AS 이름,
    V.NAME AS 비디오제목,
    R.RETDATE AS 반납여부
FROM tblMember M
    INNER JOIN tblRent R
        ON M.SEQ = R.MEMBER --> SEQ는 M의 PK값, MEMBER는 R의 FK값
            INNER JOIN tblVideo V
                ON R.VIDEO = V.SEQ; --> VIDEO는 R의 PK값, SEQ는 V의 FK값

3. 외부 조인, OUTER JOIN

[ 정의 ]

- 내부 조인 + 부모 테이블

- 내부 조인 + 차집합(부모 테이블)

- 내부 조인의 결과셋에 참조되지 않은 부모 레코드를 더한 결과셋이다.

- 일반적으로 부모 테이블을 가르킨다. (LEFT or RIGHT)

SELECT * FROM tableA LEFT (RIGHT) OUTER JOIN tableB ON tableA.PK = tableB.FK;

[ 예제 ]

< 외부 조인, OUTER JOIN >

예제1) 모든 손님 정보와 대여 기록 유무 이력
SELECT
    M.NAME AS 이름,
    R.RENTDATE AS 대여일자,
    R.RETDATE AS 반납여부
FROM tblMember M
    LEFT OUTER JOIN tblRent R
        ON M.SEQ = R.MEMBER;
        
--> 대여를 했든, 안했든 모든 손님 정보가 출력된다.


예제2) 모든 손님 정보와 대여 기록 유무 이력
SELECT
    DISTINCT M.NAME AS 이름,
    CASE
        WHEN R.RENTDATE IS NOT NULL THEN '빌려간 회원'
        WHEN R.RENTDATE IS NULL THEN '안빌려간 회원'
    END
FROM tblMember M
    LEFT OUTER JOIN tblRent R
        ON M.SEQ = R.MEMBER
            ORDER BY 종류 DESC, NAME ASC;
        
--> 에제1을 이용해서 누가 빌려가고, 누가 안빌려간지 알 수 있다.

예제3) 어떤 비디오를 몇번 빌려갔는지 확인하기
SELECT
    V.NAME,
    COUNT(R.RENTDATE) AS 대여횟수
FROM tblVideo V
    LEFT OUTER JOIN tblRent R
        ON V.SEQ = R.VIDEO
            GROUP BY V.NAME
                ORDER BY COUNT(R.RENTDATE) DESC, V.NAME ASC;
 
--> 손님이 빌려간 비디오를 몇번씩 빌려갔는지 확인하여, 잘나가는 비디오와 잘 안나가는 비디오를 구분할 수 있다.

4. 전체 외부 조인, FULL OUTER JOIN

[ 정의 ]

- 외부 조인의 LEFT OUTER 와 RIGHT OUTER를 합친것과 동일하다.

- LEFT + RIGHT OUTER JOIN

[ 예제 ]

< 전체 외부 조인, FULL OUTER JOIN >

SELECT * FROM tblMen;
SELECT * FROM tblWomen;

예제1) 모든 남자 중에서 여자친구가 있으면 여자친구 이름도 같이 가져오시오.
SELECT 
    M.NAME AS 남자, 
    W.NAME AS 여자 
FROM tblMen M
    LEFT OUTER JOIN tblWomen W
        ON M.COUPLE = W.NAME;

--> 여자친구가 없는 남자는 여자이름이 NULL

예제2) 모든 여자 중에서 남자친구가 있으면 여자친구 이름도 같이 가져오시오.
SELECT 
    M.NAME AS 남자, 
    W.NAME AS 여자 
FROM tblMen M
    RIGHT OUTER JOIN tblWomen W
        ON M.COUPLE = W.NAME;

--> 남자친구가 없는 여자는 남자이름이 NULL

예제3) 모든 남자, 모든 여자 중에서 커플이 있으면 커플 이름도 같이 가져오시오.
SELECT
    M.NAME AS 남자,
    W.NAME AS 여자
FROM tblMen M
    FULL OUTER JOIN tblWomen W
        ON M.COUPLE = W.NAME;

--> 예제1과 예제2를 합친것이 예제3이다.
--> 남자 중 여자친구가 없으면 NULL, 여자 중 남자친구가 없으면 NULL, 이외에 모든 남자, 여자 이름이 출력된다.

5. 셀프 조인, SELF JOIN

[ 정의 ]

- 1개의 테이블을 사용해서 조인한다.

- 셀프 조인 + 단순 조인

- 셀프 조인 + 내부 조인

- 셀프 조인 + 외부 조인

CREATE TABLE tblSelf (
    SEQ NUMBER PRIMARY KEY,                                 --직원번호(PK)
    NAME VARCHAR2(30) NOT NULL,                             --직원명
    DEPARTMENT VARCHAR2(50) NULL,                           --부서명
    SUPER NUMBER NULL REFERENCES tblSelf(SEQ)               --상사번호(FK). 자기참조
);

INSERT INTO tblSelf VALUES (1, '홍사장', NULL, NULL);
INSERT INTO tblSelf VALUES (2, '김부장', '영업부', 1);
INSERT INTO tblSelf VALUES (3, '이과장', '영업부', 2);
INSERT INTO tblSelf VALUES (4, '정대리', '영업부', 3);
INSERT INTO tblSelf VALUES (5, '최사원', '영업부', 4);

INSERT INTO tblSelf VALUES (6, '박부장', '개발부', 1);
INSERT INTO tblSelf VALUES (7, '하과장', '개발부', 6);



예제1) 각 사원의 소속부서, 상사명 가져오기
SELECT
    S1.NAME AS "직원명"
    S1.DEPARTMENT AS "소속부서"
    S2.NAME AS "상사명"
FROM tblSelf S1 -- 직원(부하)
    INNER JOIN tblSeLf S2 -- 상사
        ON S1.SUPER = S2.SEQ  --> 이부분이 중요하다. 자바의 재귀 형태와 비슷하다.

MEMO>

# 오늘 하루 JOIN에 대해서만 배웠다. JOIN 과 SUB QUERY는 데이터베이스의 절반을 차지 할 정도로 매우 중요하다!!

# JOIN으로 만든 것을 SUB QUERY로도 만들어 보고, SUB QUERY로 만든 것을 JOIN으로도 만들어보자!!

--> BUT, JOIN으로만 해결해야 하는것이 있고, SUB QUEREY로만 해결해야 하는 것도 있다. (굉장히 도움 많이 된다.)

# 현재 내가 듣는 수업은 입시공부를 하는것이 아니다. 무조건 취업을 위해 공부를 하는것이다.

--> 전체적인 공부도 중요하지만, 실무에서 자주 쓰이고 중요한 것 위주로 많이 공부하자!!

# JOIN에서 INNER JOIN(내부 조인) , OUTER JOIN(외부 조인) 공부를 가장 많이 하도록 하자.