조인

-- Oracle: 빈 연습 스키마에서 처음 한 번만 실행
CREATE TABLE STUDY_CENTER (
    CENTER_ID   VARCHAR2(3 CHAR) PRIMARY KEY,
    CENTER_NAME VARCHAR2(30 CHAR) NOT NULL
);

CREATE TABLE CENTER_STAFF (
    STAFF_ID    NUMBER(3) PRIMARY KEY,
    STAFF_NAME  VARCHAR2(30 CHAR) NOT NULL,
    CENTER_ID   VARCHAR2(3 CHAR),
    MONTHLY_PAY NUMBER(8) NOT NULL,
    MANAGER_ID  NUMBER(3),
    CONSTRAINT FK_CS_CENTER FOREIGN KEY (CENTER_ID)
        REFERENCES STUDY_CENTER (CENTER_ID),
    CONSTRAINT FK_CS_MANAGER FOREIGN KEY (MANAGER_ID)
        REFERENCES CENTER_STAFF (STAFF_ID)
);

CREATE TABLE PAY_BAND (
    BAND_CODE VARCHAR2(2 CHAR) PRIMARY KEY,
    MIN_PAY   NUMBER(8) NOT NULL,
    MAX_PAY   NUMBER(8) NOT NULL,
    CONSTRAINT CK_PAY_BAND CHECK (MIN_PAY <= MAX_PAY)
);

CREATE TABLE SHIFT_SLOT (
    SLOT_CODE VARCHAR2(2 CHAR) PRIMARY KEY,
    SLOT_NAME VARCHAR2(20 CHAR) NOT NULL
);

INSERT INTO STUDY_CENTER VALUES ('C10', '꿈빛');
INSERT INTO STUDY_CENTER VALUES ('C20', '파란');
INSERT INTO STUDY_CENTER VALUES ('C30', '온유');

-- 관리자 참조가 성립하도록 상위 직원부터 입력
INSERT INTO CENTER_STAFF VALUES (104, '현우', NULL, 5300, NULL);
INSERT INTO CENTER_STAFF VALUES (103, '유빈', 'C10', 3900, 104);
INSERT INTO CENTER_STAFF VALUES (101, '서아', 'C10', 2500, 103);
INSERT INTO CENTER_STAFF VALUES (102, '도현', 'C20', 3300, 104);

INSERT INTO PAY_BAND VALUES ('G1', 2000, 2999);
INSERT INTO PAY_BAND VALUES ('G2', 3000, 3999);
INSERT INTO PAY_BAND VALUES ('G3', 4000, 6000);
INSERT INTO SHIFT_SLOT VALUES ('AM', '오전');
INSERT INTO SHIFT_SLOT VALUES ('PM', '오후');
COMMIT;

-- ANSI 방식: CENTER_ID가 같은 행만 짝지음
SELECT S.STAFF_ID, S.STAFF_NAME, C.CENTER_NAME
  FROM CENTER_STAFF S
       INNER JOIN STUDY_CENTER C
         ON S.CENTER_ID = C.CENTER_ID
 ORDER BY S.STAFF_ID;

-- Oracle의 전통적 내부 조인 표기: 위 ANSI 쿼리와 같은 3행
SELECT S.STAFF_ID, S.STAFF_NAME, C.CENTER_NAME
  FROM CENTER_STAFF S, STUDY_CENTER C
 WHERE S.CENTER_ID = C.CENTER_ID
 ORDER BY S.STAFF_ID;

-- 비동등 조인: 직원의 급여가 어느 구간에 속하는가?
SELECT S.STAFF_ID, S.STAFF_NAME, S.MONTHLY_PAY, B.BAND_CODE
  FROM CENTER_STAFF S
       JOIN PAY_BAND B
         ON S.MONTHLY_PAY BETWEEN B.MIN_PAY AND B.MAX_PAY
 ORDER BY S.STAFF_ID;

-- 셀프 INNER JOIN: 관리자가 없는 현우는 제외 (3행)
SELECT S.STAFF_NAME AS STAFF_NAME, M.STAFF_NAME AS MANAGER_NAME
  FROM CENTER_STAFF S
       JOIN CENTER_STAFF M
         ON S.MANAGER_ID = M.STAFF_ID
 ORDER BY S.STAFF_ID;

-- 비동등 조인 + 셀프 LEFT JOIN: 표의 4행을 모두 조회
SELECT S.STAFF_ID, S.STAFF_NAME, S.MONTHLY_PAY,
       B.BAND_CODE, M.STAFF_NAME AS MANAGER_NAME
  FROM CENTER_STAFF S
       JOIN PAY_BAND B
         ON S.MONTHLY_PAY BETWEEN B.MIN_PAY AND B.MAX_PAY
       LEFT JOIN CENTER_STAFF M
         ON S.MANAGER_ID = M.STAFF_ID
 ORDER BY S.STAFF_ID;

-- 1. INNER: 양쪽이 일치하는 직원만
SELECT S.STAFF_NAME, C.CENTER_NAME
  FROM CENTER_STAFF S
       INNER JOIN STUDY_CENTER C ON S.CENTER_ID = C.CENTER_ID
 ORDER BY S.STAFF_ID;

-- 2. LEFT: 직원 4명 전원 보존
SELECT S.STAFF_NAME, C.CENTER_NAME
  FROM CENTER_STAFF S
       LEFT OUTER JOIN STUDY_CENTER C ON S.CENTER_ID = C.CENTER_ID
 ORDER BY S.STAFF_ID;

-- 3. RIGHT: 센터 3곳 보존 (꿈빛은 직원 두 명이므로 4행)
SELECT S.STAFF_NAME, C.CENTER_NAME
  FROM CENTER_STAFF S
       RIGHT OUTER JOIN STUDY_CENTER C ON S.CENTER_ID = C.CENTER_ID
 ORDER BY S.STAFF_ID NULLS LAST, C.CENTER_ID;

-- 4. FULL: 미배치 직원과 비어 있는 센터 모두 보존
SELECT S.STAFF_NAME, C.CENTER_NAME
  FROM CENTER_STAFF S
       FULL OUTER JOIN STUDY_CENTER C ON S.CENTER_ID = C.CENTER_ID
 ORDER BY S.STAFF_ID NULLS LAST, C.CENTER_ID;

-- ON 조건: 급여가 기준 미달인 직원은 매칭에서 제외하되 센터는 보존
SELECT C.CENTER_NAME, S.STAFF_NAME
  FROM STUDY_CENTER C
       LEFT JOIN CENTER_STAFF S
         ON C.CENTER_ID = S.CENTER_ID
        AND S.MONTHLY_PAY >= 3000
 ORDER BY C.CENTER_ID, S.STAFF_ID;

-- WHERE 조건: 조인 후 결과 행을 다시 걸러 온유가 사라짐
SELECT C.CENTER_NAME, S.STAFF_NAME
  FROM STUDY_CENTER C
       LEFT JOIN CENTER_STAFF S
         ON C.CENTER_ID = S.CENTER_ID
 WHERE S.MONTHLY_PAY >= 3000
 ORDER BY C.CENTER_ID, S.STAFF_ID;

-- 직원이 전혀 배치되지 않은 센터만 찾기
SELECT C.CENTER_NAME
  FROM STUDY_CENTER C
       LEFT JOIN CENTER_STAFF S ON C.CENTER_ID = S.CENTER_ID
 WHERE S.STAFF_ID IS NULL
 ORDER BY C.CENTER_ID;

-- Oracle 전통적 (+) 외부 조인: 직원 전원을 보존 (ANSI LEFT와 동일)
SELECT S.STAFF_NAME, C.CENTER_NAME
  FROM CENTER_STAFF S, STUDY_CENTER C
 WHERE S.CENTER_ID = C.CENTER_ID(+)
 ORDER BY S.STAFF_ID;

-- CROSS JOIN: 3개 센터 × 2개 시간대 = 6행
SELECT C.CENTER_ID, C.CENTER_NAME, T.SLOT_CODE, T.SLOT_NAME
  FROM STUDY_CENTER C CROSS JOIN SHIFT_SLOT T
 ORDER BY C.CENTER_ID, T.SLOT_CODE;

-- 3개 테이블 조인: 센터가 있는 직원 3명에 등급을 연결
SELECT S.STAFF_NAME, C.CENTER_NAME, B.BAND_CODE
  FROM CENTER_STAFF S
       JOIN STUDY_CENTER C ON S.CENTER_ID = C.CENTER_ID
       JOIN PAY_BAND B ON S.MONTHLY_PAY BETWEEN B.MIN_PAY AND B.MAX_PAY
 ORDER BY S.STAFF_ID;

-- 쉼표 나열 후 조인 조건이 없다면 의도치 않은 카티션 곱
SELECT COUNT(*) AS ROW_COUNT
  FROM CENTER_STAFF S, STUDY_CENTER C;
-- 결과: 4명 × 3센터 = 12행