표준 조인

-- 처음 한 번 실행: 기존에 동일한 테이블이 없는 연습 스키마를 가정
CREATE TABLE WATER_STATION (
    STATION_ID   VARCHAR2(3 CHAR) PRIMARY KEY,
    STATION_NAME VARCHAR2(30 CHAR) NOT NULL,
    STATUS       VARCHAR2(10 CHAR) NOT NULL
);

CREATE TABLE FIELD_AGENT (
    AGENT_ID     NUMBER(3) PRIMARY KEY,
    AGENT_NAME   VARCHAR2(30 CHAR) NOT NULL,
    STATION_ID   VARCHAR2(3 CHAR),
    STATUS       VARCHAR2(10 CHAR) NOT NULL,
    CONSTRAINT FK_FA_STATION FOREIGN KEY (STATION_ID)
        REFERENCES WATER_STATION (STATION_ID)
);

INSERT INTO WATER_STATION VALUES ('R10', '북천', 'READY');
INSERT INTO WATER_STATION VALUES ('R20', '남천', 'READY');
INSERT INTO WATER_STATION VALUES ('R30', '서천', 'CLOSED');

INSERT INTO FIELD_AGENT VALUES (301, '하윤', 'R10', 'READY');
INSERT INTO FIELD_AGENT VALUES (302, '지수', 'R10', 'OFF');
INSERT INTO FIELD_AGENT VALUES (303, '민재', 'R20', 'READY');
INSERT INTO FIELD_AGENT VALUES (304, '다온', NULL, 'READY');
COMMIT;

-- INNER는 생략 가능: 지점에 배정된 담당자 3명
SELECT A.AGENT_ID, A.AGENT_NAME, S.STATION_NAME
  FROM FIELD_AGENT A
       JOIN WATER_STATION S ON A.STATION_ID = S.STATION_ID
 ORDER BY A.AGENT_ID;

-- CROSS: 담당자 4명 X 지점 3곳 = 총 12가지 조합
SELECT A.AGENT_NAME, S.STATION_NAME
  FROM FIELD_AGENT A CROSS JOIN WATER_STATION S
 ORDER BY A.AGENT_ID, S.STATION_ID;

-- INNER JOIN: 양쪽 매칭 행만, 3행
SELECT A.AGENT_NAME, S.STATION_NAME
  FROM FIELD_AGENT A
       INNER JOIN WATER_STATION S ON A.STATION_ID = S.STATION_ID
 ORDER BY A.AGENT_ID;

-- LEFT JOIN: 담당자 4명 보존, 4행
SELECT A.AGENT_NAME, S.STATION_NAME
  FROM FIELD_AGENT A
       LEFT OUTER JOIN WATER_STATION S ON A.STATION_ID = S.STATION_ID
 ORDER BY A.AGENT_ID;

-- RIGHT JOIN: 담당자가 없는 서천도 보존, 4행
SELECT A.AGENT_NAME, S.STATION_NAME
  FROM FIELD_AGENT A
       RIGHT JOIN WATER_STATION S ON A.STATION_ID = S.STATION_ID
 ORDER BY A.AGENT_ID NULLS LAST, S.STATION_ID;

-- FULL JOIN: 양쪽의 미매칭을 모두 보존, 5행
SELECT A.AGENT_NAME, S.STATION_NAME
  FROM FIELD_AGENT A
       FULL OUTER JOIN WATER_STATION S ON A.STATION_ID = S.STATION_ID
 ORDER BY A.AGENT_ID NULLS LAST, S.STATION_ID;

-- NATURAL: STATION_ID, STATUS 두 동명 열을 모두 비교, 2행
SELECT STATION_ID, STATUS, A.AGENT_NAME, S.STATION_NAME
  FROM FIELD_AGENT A NATURAL JOIN WATER_STATION S
 ORDER BY A.AGENT_ID;

-- USING: STATION_ID만 비교, 3행
SELECT STATION_ID, A.AGENT_NAME, S.STATION_NAME
  FROM FIELD_AGENT A JOIN WATER_STATION S USING (STATION_ID)
 ORDER BY A.AGENT_ID;

-- ON: 비교 조건을 직접 지정, 3행
SELECT A.STATION_ID, A.AGENT_NAME, S.STATION_NAME,
       A.STATUS AS AGENT_STATUS, S.STATUS AS STATION_STATUS
  FROM FIELD_AGENT A JOIN WATER_STATION S
    ON A.STATION_ID = S.STATION_ID
 ORDER BY A.AGENT_ID;

-- USING과 FULL JOIN을 결합하면 공통 키를 한 열로 반환, 5행
SELECT STATION_ID, A.AGENT_NAME, S.STATION_NAME
  FROM FIELD_AGENT A FULL OUTER JOIN WATER_STATION S
       USING (STATION_ID)
 ORDER BY STATION_ID NULLS LAST, A.AGENT_ID;

-- A. ON: READY인 담당자만 매칭, 지점은 전부 유지 (3행)
SELECT S.STATION_NAME, A.AGENT_NAME
  FROM WATER_STATION S
       LEFT JOIN FIELD_AGENT A
         ON S.STATION_ID = A.STATION_ID
        AND A.STATUS = 'READY'
 ORDER BY S.STATION_ID, A.AGENT_ID;

-- B. WHERE: JOIN 결과에서 READY가 아닌 행 제거 (2행)
SELECT S.STATION_NAME, A.AGENT_NAME
  FROM WATER_STATION S
       LEFT JOIN FIELD_AGENT A
         ON S.STATION_ID = A.STATION_ID
 WHERE A.STATUS = 'READY'
 ORDER BY S.STATION_ID, A.AGENT_ID;

-- C. 미배정 담당자가 아닌, 담당자가 전혀 없는 지점 찾기 (1행)
SELECT S.STATION_NAME
  FROM WATER_STATION S
       LEFT JOIN FIELD_AGENT A ON S.STATION_ID = A.STATION_ID
 WHERE A.AGENT_ID IS NULL
 ORDER BY S.STATION_ID;

-- Oracle 전통적 외부 조인: 담당자 4명 모두 보존
SELECT A.AGENT_NAME, S.STATION_NAME
  FROM FIELD_AGENT A, WATER_STATION S
 WHERE A.STATION_ID = S.STATION_ID(+)
 ORDER BY A.AGENT_ID;

-- 위와 동등한 ANSI 표준 표기: 담당자 4명 모두 보존
SELECT A.AGENT_NAME, S.STATION_NAME
  FROM FIELD_AGENT A
       LEFT OUTER JOIN WATER_STATION S
         ON A.STATION_ID = S.STATION_ID
 ORDER BY A.AGENT_ID;

-- 반대 방향: 담당자가 없는 서천을 포함해 4행
SELECT A.AGENT_NAME, S.STATION_NAME
  FROM FIELD_AGENT A
       RIGHT JOIN WATER_STATION S
         ON A.STATION_ID = S.STATION_ID
 ORDER BY A.AGENT_ID NULLS LAST, S.STATION_ID;