OUTER JOIN

조인조건(기본키 외래키관계, 조인에 사용되는 연결고리)
1.자료형과 크기 동일
2.동일한 데이터
--고정길이 문자형(CHAR)은 절대로 기본키로 쓰지않음

P.K F.K의 자료길이는 같아야함.

개체무결성 : 기본키, UNIQUE
참조무결성 : P.K. F.K 제한(RESTRICT),연쇄(CASCADE),NULL값으로 처리(NULLIFY)
도메인무결성 : 성별, 출신대학, 안경착용여부 등

 

---

실습

--R테이블 S테이블 생성
CREATE TABLE S(
    C VARCHAR2(10),
    D VARCHAR2(10),
    E VARCHAR2(10),
    CONSTRAINT PK_S PRIMARY KEY(C)
    );
    
--R테이블생성, 기본키는 A, 외래키는 C(S테이블의 C컬럼참조)

CREATE TABLE R(
    A VARCHAR2(10),
    B VARCHAR2(10),
    C VARCHAR2(10),
    CONSTRAINT PK_R PRIMARY KEY(A)
    );
--R테이블에 a1, b1,c1과 a2,b2,c2데이터입력
INSERT INTO R(A,B,C) VALUES ('a1','b1','c1');
INSERT INTO R(A,B,C) VALUES ('a2','b2','c2');

--S테이블에 데이터 입력
INSERT INTO S(C,D,E) VALUES ('c1','d1','e1');
INSERT INTO S(C,D,E) VALUES ('c2','d2','e2');

COMMIT;

--데이터확인하기
SELECT * FROM R; 
SELECT * FROM S;
--LEFT OUTER JOIN
SELECT R.A,R.B,R.C
     , S.C,S.D,S.E
     FROM R, S      
WHERE R.C=S.C(+);


SELECT R.A,R.B,R.C
     , S.C,S.D,S.E
     FROM R, S      
WHERE R.C(+)=S.C;

--ANSI표준

SELECT R.A,R.B,R.C
     , S.C,S.D,S.E
FROM R R JOIN S ON(R.C=S.C) ;

----------------------------------------------220527

--DEPART테이블 STUDENT테이블을 통해 OUTER JOIN연습
--컬럼의 크기는 학생이 결정해보자
1. DEPART테이블 생성 후 기본키는 DEP_CODE
   컬럼 : DEP_CODE, DEP_NAME

CREATE TABLE DEPART(
    DEP_CODE VARCHAR2(20),
    DEP_NAME VARCHAR2(10),
CONSTRAINT PK_DEPART PRIMARY KEY (DEP_CODE));
    
2. STUDENT테이블 생성 후 기본키는 STUD_NO, 
   외래키는 STUD_DEP(DEPART테이블의 DEP_CODE컬럼 참조)   
   컬럼 : STUD_NO, STUD_NAME, STUD_DEP
   
CREATE TABLE STUDENT(
    STUD_NO VARCHAR2(20),
    STUD_NAME VARCHAR2(20),
    STUD_DEP VARCHAR2(20),
CONSTRAINT PK_STUDENT PRIMARY KEY (STUD_NO),
CONSTRAINT FK_STUDENT FOREIGN KEY(STUD_DEP) REFERENCES DEPART(DEP_CODE));

INSERT INTO DEPART(DEP_CODE, DEP_NAME) VALUES('201','7월반1');
INSERT INTO DEPART(DEP_CODE, DEP_NAME) VALUES('202','5월반');
INSERT INTO DEPART(DEP_CODE, DEP_NAME) VALUES('203','4월반');
INSERT INTO DEPART(DEP_CODE, DEP_NAME) VALUES('204','7월반2');
INSERT INTO DEPART(DEP_CODE, DEP_NAME) VALUES('205','3월반');
INSERT INTO DEPART(DEP_CODE, DEP_NAME) VALUES('206','6월반');
INSERT INTO DEPART(DEP_CODE, DEP_NAME) VALUES('207','11월반');

SELECT
    *
FROM DEPART;

INSERT INTO STUDENT(STUD_NO,STUD_NAME,STUD_DEP) 
VALUES('2022110001','정요한','206');
INSERT INTO STUDENT(STUD_NO,STUD_NAME,STUD_DEP) 
VALUES('2022110002','조수빈','206');
INSERT INTO STUDENT(STUD_NO,STUD_NAME,STUD_DEP) 
VALUES('2022110003','홍무곤','205');
INSERT INTO STUDENT(STUD_NO,STUD_NAME,STUD_DEP) 
VALUES('2022110004','최현우','201');
INSERT INTO STUDENT(STUD_NO,STUD_NAME,STUD_DEP) 
VALUES('2022110005','구지현','202');

SELECT
    *
FROM STUDENT;

5. DEPART테이블과 STUDENT테이블을 EQUAL JOIN
, INNER JOIN 처리
--Cartesian Product
SELECT *
FROM DEPART D, STUDENT S;
--Cross Join 위와 같은 결과
SELECT *
FROM DEPART D Cross Join STUDENT S;

--EQUI JOIN
SELECT D.DEP_CODE
    ,  D.DEP_NAME
    ,  S.STUD_NO
    , S.STUD_NAME
    , S.STUD_DEP
FROM DEPART D, STUDENT S
WHERE D.DEP_CODE=S.STUD_DEP;

--INNER JOIN
SELECT D.DEP_CODE
    ,  D.DEP_NAME
    ,  S.STUD_NO
    , S.STUD_NAME
    , S.STUD_DEP
FROM DEPART D INNER JOIN STUDENT S ON(D.DEP_CODE=S.STUD_DEP);

6. DEPART테이블과 STUDENT테이블을 왼쪽 외부조인
SELECT D.DEP_CODE
    ,  D.DEP_NAME
    ,  S.STUD_NO
    , S.STUD_NAME
    , S.STUD_DEP
FROM DEPART D, STUDENT S
WHERE D.DEP_CODE=S.STUD_DEP(+);

--ANSI
SELECT D.DEP_CODE
    ,  D.DEP_NAME
    ,  S.STUD_NO
    , S.STUD_NAME
    , S.STUD_DEP
FROM DEPART D LEFT OUTER JOIN STUDENT S
ON( D.DEP_CODE=S.STUD_DEP);




7. DEPART테이블과 STUDENT테이블을 오른쪽 외부조인
SELECT D.DEP_CODE
    ,  D.DEP_NAME
    ,  S.STUD_NO
    , S.STUD_NAME
    , S.STUD_DEP
FROM DEPART D, STUDENT S
WHERE D.DEP_CODE(+)=S.STUD_DEP;

SELECT D.DEP_CODE
    ,  D.DEP_NAME
    ,  S.STUD_NO
    , S.STUD_NAME
    , S.STUD_DEP
FROM DEPART D RIGHT OUTER JOIN STUDENT S
ON( D.DEP_CODE(+)=S.STUD_DEP);

8. DEPART테이블과 STUDENT테이블을 완전 외부 조인
SELECT D.DEP_CODE
    ,  D.DEP_NAME
    ,  S.STUD_NO
    , S.STUD_NAME
    , S.STUD_DEP
FROM DEPART D, STUDENT S
WHERE D.DEP_CODE=S.STUD_DEP(+)
UNION
SELECT D.DEP_CODE
    ,  D.DEP_NAME
    ,  S.STUD_NO
    , S.STUD_NAME
    , S.STUD_DEP
FROM DEPART D, STUDENT S
WHERE D.DEP_CODE(+)=S.STUD_DEP;

SELECT D.DEP_CODE
    ,  D.DEP_NAME
    ,  S.STUD_NO
    , S.STUD_NAME
    , S.STUD_DEP
FROM DEPART D FULL OUTER JOIN STUDENT S
ON( D.DEP_CODE=S.STUD_DEP);

--OUTER JOIN 문제
1. 대학정보테이블(COLLEAGE)
대학코드
대학명
3. 대학정보테이블의 대학코드를 기본키로 설정

CREATE TABLE COLLEAGE(
COLLEAGE_CODE VARCHAR2(20),
COLLEAGE_NAME VARCHAR2(30),
CONSTRAINT PK_COLLEAGE PRIMARY KEY (COLLEAGE_CODE));

2. 학과정보테이블(HAKGWA)
학과코드
학과명
대학코드
4. 학과정보테이블의 학과코드를 기본키로 설정
   학과정보테이블의 대학코드가 대학정보테이블의 대학코드를 참조하는
   외래키로 설정
CREATE TABLE HAKGWA(
 HAKGWA_CODE VARCHAR2(20),
 HAKGWA_NAME VARCHAR2(20),
 COLLEAGE_CODE VARCHAR2(20),  
CONSTRAINT PK_HAKGWA PRIMARY KEY (HAKGWA_CODE),
CONSTRAINT FK_HAKGWA FOREIGN KEY(COLLEAGE_CODE) REFERENCES COLLEAGE(COLLEAGE_CODE));

ALTER TABLE HAKGWA MODIFY HAKGWA_NAME VARCHAR2(50);
3. 대학정보테이블의 대학코드를 기본키로 설정
ALTER TABLE COLLEAGE ADD(CONSTRAINT PK_COLLEAGE PRIMARY KEY (COLLEAGE_CODE));
4. 학과정보테이블의 학과코드를 기본키로 설정
   학과정보테이블의 대학코드가 대학정보테이블의 대학코드를 참조하는
   외래키로 설정
ALTER TABLE HAKGWA 
ADD(CONSTRAINT PK_HAKGWA PRIMARY KEY (HAKGWA_CODE)
  , CONSTRAINT FK_HAKGWA FOREIGN KEY (COLLEAGE_CODE)REFERENCES COLLEAGE(COLLEAGE_CODE));
 

5. 대학정보테이블에 다음 데이터를 INSERT 하기
W01 솔아시아매니지먼트
W02 외식조리
W03 철도물류
W04 디지털미디어
W05 보건복지


INSERT INTO COLLEAGE(COLLEAGE_CODE,COLLEAGE_NAME) 
VALUES('W01','솔아시아매니지먼트');
INSERT INTO COLLEAGE(COLLEAGE_CODE,COLLEAGE_NAME) 
VALUES('W02','외식조리');
INSERT INTO COLLEAGE(COLLEAGE_CODE,COLLEAGE_NAME) 
VALUES('W03','철도분류');
INSERT INTO COLLEAGE(COLLEAGE_CODE,COLLEAGE_NAME) 
VALUES('W04','디지털미디어');
INSERT INTO COLLEAGE(COLLEAGE_CODE,COLLEAGE_NAME) 
VALUES('W05','보건복지');


6. 학과정보테이블에 다음 데이터를 INSERT 하기
H001 외식조리학과 W02
H002 호텔관광경영학과 W02
H003 철도경영학과 W03
H004 간호학과 W05

INSERT INTO HAKGWA(HAKGWA_CODE,HAKGWA_NAME,COLLEAGE_CODE) 
VALUES('H001','외식조리학과','W02');
INSERT INTO HAKGWA(HAKGWA_CODE,HAKGWA_NAME,COLLEAGE_CODE) 
VALUES('H002','호텔관광경영학과','W02');
INSERT INTO HAKGWA(HAKGWA_CODE,HAKGWA_NAME,COLLEAGE_CODE) 
VALUES('H003','철도경영학과','W03');
INSERT INTO HAKGWA(HAKGWA_CODE,HAKGWA_NAME,COLLEAGE_CODE) 
VALUES('H004','간호학과','W05');

7. 대학정보테이블과 학과정보테이블을 내부조인하여 SELECT
SELECT C.COLLEAGE_CODE 대학코드
    ,  C.COLLEAGE_NAME 대학이름
    ,  H.HAKGWA_CODE 학과코드
    ,  H.HAKGWA_NAME 학과명
    ,  H.COLLEAGE_CODE 대학코드
FROM COLLEAGE C, HAKGWA H
WHERE C.COLLEAGE_CODE=H.COLLEAGE_CODE;

--ANSI
SELECT C.COLLEAGE_CODE 대학코드
    ,  C.COLLEAGE_NAME 대학이름
    ,  H.HAKGWA_CODE 학과코드
    ,  H.HAKGWA_NAME 학과명
    ,  H.COLLEAGE_CODE 대학코드
FROM COLLEAGE C INNER JOIN HAKGWA H
ON(C.COLLEAGE_CODE=H.COLLEAGE_CODE);


8. 대학정보테이블의 모든 데이터를 출력하도록 
  학과정보테이블과 외부조인 SELECT
 --LEFT 
SELECT C.COLLEAGE_CODE 대학코드
    ,  C.COLLEAGE_NAME 대학명
    ,  H.HAKGWA_CODE 학과코드
    ,  H.HAKGWA_NAME 학과명
    ,  H.COLLEAGE_CODE 대학코드
FROM COLLEAGE C, HAKGWA H
WHERE C.COLLEAGE_CODE=H.COLLEAGE_CODE(+)
UNION
SELECT C.COLLEAGE_CODE--RIGHT
    ,  C.COLLEAGE_NAME
    ,  H.HAKGWA_CODE
    ,  H.HAKGWA_NAME
    ,  H.COLLEAGE_CODE
FROM COLLEAGE C, HAKGWA H
WHERE C.COLLEAGE_CODE(+) = H.COLLEAGE_CODE;

SELECT C.COLLEAGE_CODE 대학코드
    ,  C.COLLEAGE_NAME 대학명
    ,  H.HAKGWA_CODE 학과코드
    ,  H.HAKGWA_NAME 학과명
    ,  H.COLLEAGE_CODE 대학코드
FROM COLLEAGE C FULL OUTER JOIN HAKGWA H
ON(C.COLLEAGE_CODE=H.COLLEAGE_CODE);

--학사부총장 요청
--대학별 학과개수구하기

SELECT C.COLLEAGE_CODE
    ,  C.COLLEAGE_NAME
    ,  COUNT(H.COLLEAGE_CODE)
FROM COLLEAGE C, HAKGWA H
WHERE C.COLLEAGE_CODE = H.COLLEAGE_CODE(+)
GROUP BY C.COLLEAGE_CODE, C.COLLEAGE_NAME
ORDER BY 1;
    
--ANSI

SELECT C.COLLEAGE_CODE
    ,  C.COLLEAGE_NAME
    ,  COUNT(H.COLLEAGE_CODE)
FROM COLLEAGE C LEFT OUTER JOIN HAKGWA H
ON(C.COLLEAGE_CODE = H.COLLEAGE_CODE)
GROUP BY C.COLLEAGE_CODE, C.COLLEAGE_NAME
ORDER BY 1;

--P246
--전체상품의 2005년 1월 입고수량을 검색조회
SELECT B.BUY_DATE 
    ,  B.BUY_QTY
    ,COUNT(*)
FROM BUYPROD B
WHERE BUY_DATE BETWEEN '050101' AND '050131';