오라클 데이터 베이스 6일차 (#3 조인함수)

/*
 조인
조인의 종류

사용방법에 따른 분류

ANSI 조인
국제 표준 조인 방법(ANSI/ISO 에서 재정)
DBMS 종류와 관계없이 모든 DBMS 에서 사용 가능

ORACLE 조인
ORACLE 내 조인 방법

조인동작에 따른 분류

조건 연산자에 따른 구분
동등 조인
안티 조인

조인 대상에 따른 구분
셀프 조인


조인 조건에 따른 구분
내부 조인
외부 조인
세미 조인
카타시안 조인
*/

/*
동등 조인 (EQUAL 조인)
가장 기본이 되며 일반적인 조인 방법
WHERE 절에서 등호('=') 연산자를 사용해 2개 이상의 테이블이나 뷰를 연결한 조인
이때 WHERE 절에 기술한 조건을 조인 조건
*/

-- 선수가 무슨 팀에 속하였는지 선수 이름과 팀 이름을 표시
SELECT 
PLAYER_NAME AS "선수이름" ,
t.TEAM_NAME AS "팀이름" 
FROM
PLAYER p, TEAM t
WHERE
p.TEAM_ID = t.TEAM_ID;

SELECT * FROM TEAM;


/*
세미 조인(SEMI JOIN)
서브 쿼리를 사용해 서브 쿼리에 존재하는 데이터만 메인 쿼리에서 추출하는 조인 방법
IN과 EXISTS 연산자를 사용하는법
*/

-- 팀 추가
INSERT INTO 
TEAM 
(TEAM_ID, REGION_NAME, STADIUM_ID) 
VALUES
('X01' , '서울' , '데아터베이스' , 'D03')

SELECT * FROM TEAM;

/*
IN(서브쿼리)
서브쿼리로 출력되는 결과가 존재하는 레코드를 출력
*/

SELECT 
*
FROM 
TEAM t
WHERE 
TEAM_ID IN
(
  SELECT TEAM_ID FROM PLAYER p WHERE t.TEAM_ID = p.TEAM_ID
);

/*
EXISTS(서브쿼리)
서브쿼리의 결과가 존재하면 참
결과가 존재하지 않으면 거짓을 반환
단 서브쿼리의 조건에 맞는 데이터만 출력

*/

SELECT 1 FROM DUAL WHERE EXISTS *SELECT 1 FROM DUAL WHERE 1 = 0);

SELECT 
*
FROM
TEAM t
WHERE 
EXISTS
(
SELECT * FROM PLAYER p WHERE p.TEAM_ID = t.TEAM_ID 
);

SELECT * FROM TEAM;

/*
안티 조인(ANTI JOIN)
세미조인의 반대 개념으로 서브 쿼리에 존재하지 않고 메인 쿼리에만 존재하는
데이터만 추출하는 방법
NOT IS 이나 NOT EXISTS
*/


SELECT 
*
FROM 
TEAM t
WHERE 
NOT EXISTS 
(
  SELECT * FROM PLAYER p WHERE p.TEAM_ID = t.TEAM_ID
);


SELECT 
*
FROM 
TEAM t
WHERE 
TEAM_ID NOT IN 
(
  SELECT * TEAM_ID FROM PLAYER p WHERE p.TEAM_ID = t.TEAM_ID
);

/* 
 셀프 조인 (SELF JOIN)
  서로 다른 두 테이블이 아닌 동일한 테이블 조인하여 사용하는 방법 
 */

-- 원정팀의 다음 경기를 같이 조회
SELECT 
s1.STADIUM_ID AS "경기장아이디" , 
s1.SCHE_DATE  AS "경기일자" , 
s1.HOMETEAM_ID AS "홈팀아이디",
s1.AWAYTEAM_ID AS "원정팀아이디",
s2.SCHE_DATE AS "원정팀다음경기일자"
FROM 
SCHEDULE s1, SCHEDULE s2
WHERE
s1.AWAYTEAM_ID = s2.HOMETEAM_ID ;

/*
외부 조인(OUTER JOIN)
일반 조인을 확장한 개념으로 , 조인 조건에 만족하는 데이터 뿐만 아니라
어느 한쪽 테이블에 데이터가 없더라도 NULL 값을 이용해 데이터를 모두 추출
데이터가 없는 테이블에 (+) 붙여야 한다.

1. 조인 대상 테이블 중 데이터가 없는 테이블 조인 조건에 (+)를 붙인다.
*/
-- 팀 테이블과 선수 테이블의 팀 아이디가 일치하는
-- 즉 모두 팀아이디가 존재하는 데이터만 출력 
SELECT
t.TEAM_NAME AS "팀 이름" , 
PLAYER_NAME AS "선수 이름"
FROM
TEAM t, PLAYER p 
WHERE
t.TEAM_ID = p.TEAM_ID(+);


-- 팀 테이블을 모두 표시한 상태로 선수 테이블의 팀아이디가 일치하는 데이터만 출력
SELECT
t.TEAM_NAME AS "팀 이름" , 
PLAYER_NAME AS "선수 이름"
FROM
TEAM t, PLAYER p 
WHERE
t.TEAM_ID = p.TEAM_ID(+);

-- 열린 모든 경기에 대한 경기장 이름, 경기일자, 홈팀이름, 원정팀이름 , 홈팀점수, 원정팀 점수

-- 1. 열린 모든 경기에 대한 => SCHEDULE //


SELECT
st.STADIUM_NAME AS "경기장이름" , 
sc.SCHE_DATE AS "경기일자" , 
t1.HOMETEAM_ID AS "홈팀이름",
t2.AWAYTEAM_ID AS "원정팀이름",
sc.HOME_SCORE AS "홈팀점수",
sc.AWAY_SCORE AS "원정팀점수"
FROM
SCHEDULE sc, STADIUM st, TEAM t1, TEAM t2
WHERE
sc.STADIUM_ID  = st.STADIUM_ID(+) AND
sc.HOMETEAM_ID = t1.TEAM_ID(+) AND
sc.AWAYTEAM_ID = t2.TEAM_ID(+) AND
sc.GUBUN = 'Y';

SELECT * FROM TEAM;