40일차 오라클조인/ 안시조인

쿠우·2022년 5월 22일
0

조인

관계데이터모델에서 조인의 종류 - 세타 동등 자연 등등

조인이란 뭐냐 ? - 테이블을 합치는 것이다

"JOIN" : 필요한 데이터가, 여러 테이블에 분산되어 있을 경우에,
여러 테이블의 공통된 컬럼을 연결시켜, 원하는 데이터를 검색하는
방법을 "조인"이라 한다.

따라서, 조인은 검색하고자 하는 컬럼이, 두개 이상의 테이블에
분산되어 있는 경우에 사용된다.

두 테이블이 소위 "조인"이 가능하려면 , 아래의 조건을 만족해야 한다.
"합병가능해야 함" (Union Compatible) ==> "두 테이블이 성질이 동일한 컬럼을 가지고 있어야 한다!"

테이블의 속성의 이름이 달라도 , 컬럼의 타입과 도메인은 같아야한다
ex) age(0~120) , myAge(1~250) 이 둘은 안된다.

위 조건이 만족되는 대표적인 케이스는 바로 참조무결성제약조건을 가지는 두테이블 간의 PK와 FK를 이용한 조인을 의미한다.
데이터 표준화 -> 속성에 대한 일관된 이름 -> 대부분의 개발자의 실수 (해당 조건이 아니다.)

결국 부모,자식 자식테이블의 FK와 부모테이블의 PK의 조인해야 엉뚱한 데이터가 나오지 않는다.

조인이란 두테이블의 모든 속성을 합쳐서 보여준다. 따라서 PK와 FK의 동일한 정보가 두번 보여준다. -> 동등 조인 / 근데 왜 두번 보여주냐 중복을 제거하자 -> 자연조인


오라클조인: 오라클에서만 사용되는 조인

오라클 조인에 종류 -카테시안 프로덕트, 이퀄, 논 이퀄 등등

1)카테시안 프로덕트 (x테이블 속성 * y테이블 속성 )

  • 조인 조건이 생략 되었을 때 연관없는 테이블을 무조건 합친다. 무효한 데이터
  • 셀렉절에서 중복된 컬럼이면 도트 연산자를 통해 어떤 테이블의 컬럼인지 그 소속을 밝혀야한다. (Pk로 카운트하는게 가장 좋다.)
-- ------------------------------------------------------
-- A. Catesian Product (카테시안 프로덕트)
-- ------------------------------------------------------
-- 두 개 이상의 테이블을 공통컬럼없이 연결하는 조인으로,
-- 모든 조인에서 가장 먼저 발생하는 조인이자 기본이 됨.
--  가. 유효한 데이터로 사용되지 못함.
--  나. 조인조건이 생략된 경우에 발생.
--
-- * 조인결과: 테이블1 x ... x 테이블n 개의 레코드 생성
-- ------------------------------------------------------
-- Basic Syntax)
--  SELECT 테이블1.컬럼 , 테이블2.컬럼
--  FROM 테이블1, 테이블2
-- ------------------------------------------------------
SELECT
    count(*)
FROM
    employees;


SELECT
    count(*)
FROM
    departments;


SELECT
    107 * 27
FROM
    dual;    



SELECT
    count(*) 
FROM
    employees,
    departments;


SELECT
    last_name,
    department_name
FROM
    employees,
    departments;

2)이(에)퀄 조인 동등 조인

  • 일치하는 값들만 조인하는 유의미한 조인
  • 참조 무결성 제약조건을 성립하게 조인할 때 사용
  • WHERE 절에서 (조인)동등조건을 가지게 한다.
    (행구별 조건인지 조인조건을 구별하기위해 안시조인이 나옴 안시조인은 조인조건이 프럼절에 들어간다.)
-- ------------------------------------------------------
-- B. Equal(= Equi) Join (동등 조인)
-- ------------------------------------------------------
-- 가. 가장 많이 사용하는 조인
-- 나. 두 테이블에서, 공통으로 존재하는 컬럼의 값이 일치하는 행들을
--     연결하여 데이터를 반환.
--     일치하지 않는 데이터는 제외됨.
-- 다. 대부분, 기본키(PK)를 가진 테이블(Parent, Master)과
--     참조키(FK)를 가진 테이블(Child, Slave)을 조인할 때 사용
-- ------------------------------------------------------
-- Basic Syntax)
--  SELECT 테이블1.컬럼 , 테이블2.컬럼
--  FROM 테이블1, 테이블2
--  WHERE 테이블1.공통컬럼 = 테이블2.공통컬럼;
-- ------------------------------------------------------
SELECT
    last_name,

    employees.department_id,
    departments.department_id,

    department_name
FROM
    employees,
    departments
WHERE
    employees.department_id = departments.department_id;

3) 자연조인

  • 셀렉절과 웨어절에 소속을 적어줌으로 모호성을 없엔다.
  • 한쪽 소속이 NULL이면 비교연산자로 연산이 안되기 때문에 알아서 걸러진다?..

-- ------------------------------------------------------
-- 공통컬럼 사용시, 모호성 제거
-- ------------------------------------------------------
SELECT
    last_name,
    department_name,
    departments.department_id    -- ORA-00918: column ambiguously defined
FROM
    employees,
    departments
WHERE
    employees.department_id = departments.department_id;


SELECT
    last_name,
    department_name,
    -- 공통컬럼 사용시, 소속테이블명 기재로 모호성 제거
    employees.department_id  
FROM
    employees,
    departments
WHERE
    employees.department_id = departments.department_id;

ㄱ)테이블 별칭

  • 테이블 별칭을 붙일때는 AS안쓴다.
  • 테이블 별칭을 부여하면 테이블명은 쓰면 안된다. 일관성있게 테이블 별칭으로 명시해야한다. 테이블명을 별칭으로 인식
  • 모호성이 없는 명확한 컬럼은 별칭으로 굳이 소속을 안밝혀도 된다.
-- ------------------------------------------------------
-- 테이블에 별칭 사용
-- ------------------------------------------------------
-- 가. SELECT 절에서, 컬럼 별칭(Column Alias)을 사용했듯이,
--     FROM 절에서도, 테이블 별칭(Table Alias)을 사용가능하다.
-- 나. 테이블명이 길거나, 식별이 힘든 경우에 유용하다.
-- 다. (*주의*) 테이블 별칭을 지정한 경우에는, 반드시 이 별칭을
--     사용하여, 컬럼을 참조해야 한다.
--     만일, 테이블 별칭을 사용하지 않고, 테이블명으로 컬럼을
--     참조하면, 테이블명을 별칭(Alias)으로 인식하기 때문에,
--     오류 발생.
-- ------------------------------------------------------
-- Basic Syntax)
--      SELECT alias1.컬럼 , alias2.컬럼
--      FROM 테이블1 alias1, 테이블2 alias2
--      WHERE alias1.공통컬럼 = alias2.공통컬럼;
-- ------------------------------------------------------

-- 테이블 별칭(alias) 사용
SELECT
    emp.last_name,          -- 성
    dept.department_name,        -- 부서명
    employees.department_id       -- 부서번호

FROM
    employees emp,          -- emp : 테이블 별칭(alias)
    departments dept        -- dept: 테이블 별칭(alias)
WHERE
    emp.department_id = dept.department_id;


-- 테이블 별칭(alias) 사용시 주의할 점
-- ORA-00904: "EMPLOYEES"."DEPARTMENT_ID": invalid identifier
SELECT
    emp.last_name,
    department_name,
    emp.department_id
FROM
    employees emp,          -- emp : 테이블 별칭(alias)
    departments dept        -- dept: 테이블 별칭(alias)
WHERE
    employees.department_id = dept.department_id; -- 조인조건

ㄴ) 검색조건 추가 (WHERE)(관례)

-조인조건 우선명시, 검색조건 후위명시
-조인조건의 결과 중에서 검색조건으로 필터링 된 결과를 반환한다.


-- ------------------------------------------------------
-- 검색조건 추가
-- ------------------------------------------------------
-- 가. Oracle 조인에서는, WHERE절에 AND / OR 연산자를 사용하여
--     조인조건에 검색조건을 추가할 수 있다.
-- 나. 이로인해, WHERE의 어떤 조건이 조인조건이고, 어떤 조건이
--     검색조건인지, 쉽게 파악이 안되어, 가독성이 떨어짐
-- 다. (*주의*) 따라서, 조인조건을 우선 명시하고, 나중에 검색조건
--     을 명시하는 방법으로, 가독성을 향상 시켜야 한다.
-- 라. 결과: 조인조건의 결과 중에서, 검색조건으로 필터링 된 결과
--          를 반환
-- ------------------------------------------------------
SELECT
    emp.last_name,
    salary,
    department_name
FROM
    employees emp,
    departments dept
WHERE
    emp.department_id = dept.department_id  -- 조인조건
    AND last_name='Whalen';                 -- 검색조건


SELECT
    -- d.department_name AS 부서명,
    department_name AS 부서명,
    -- count(e.employee_id) AS 인원수
    count(employee_id) AS 인원수
FROM
    employees e,
    departments d
WHERE
    e.department_id = d.department_id           -- 조인조건
    AND to_char( hire_date , 'YYYY') <= 2005    -- 검색조건
GROUP BY
    -- d.department_name;
    department_name;

4)Non - equal 조인

-동등비교연산자 외의 비교연산자를 사용하는 조인
-범위를 가지고 있을 때 동등비교연산자로는 구할 수 없다.> 이 때 사용


-- ------------------------------------------------------
-- C. Non-equal(= Non-equi) Join (비동등 조인)
-- ------------------------------------------------------
-- 가. WHERE절에 조인조건을 지정할 때, 동등연산자(=) 이외의,
--     비교 연산자(>,<,>=,<=,!=)를 사용하는 조인
-- ------------------------------------------------------
-- * Please refer to the chapter 05, page 13.
-- ------------------------------------------------------
DROP TABLE job_grades PURGE;    -- 테이블 삭제

CREATE TABLE  job_grades (
    grade_level VARCHAR2(3) -- 월급여등급
        CONSTRAINT job_gra_level_pk PRIMARY KEY,
    lowest_sal NUMBER,      -- 최소 월급여
    highest_sal NUMBER      -- 최대 월급여
);

DESC job_grades;

INSERT INTO job_grades VALUES('A', 1000, 2999);
INSERT INTO job_grades VALUES('B', 3000, 5999);
INSERT INTO job_grades VALUES('C', 6000, 9999);
INSERT INTO job_grades VALUES('D', 10000, 14999);
INSERT INTO job_grades VALUES('E', 15000, 24999);
INSERT INTO job_grades VALUES('F', 25000, 40000);

COMMIT;

DESC job_grades;

SELECT * FROM job_grades;


-- 2개의 테이블 조인
SELECT
    last_name,
    salary,
    grade_level
FROM
    employees e,
    job_grades g
WHERE
    -- e.salary BETWEEN g.lowest_sal AND g.highest_sal;
    e.salary BETWEEN 1000 AND 3000;


-- 3개의 테이블 조인
SELECT
    last_name,
    salary,
    department_name,
    grade_level
FROM
    employees e,
    departments d,
    job_grades g
WHERE
    e.department_id = d.department_id                       -- Equal Join
    AND e.salary BETWEEN g.lowest_sal AND g.highest_sal;    -- Non-equal Join

5) self 조인 (테이블 내의 데이터의 관계를 맺어줄 때 )

  • 테이블에 구하고자하는 값으로 관련되는 주요 속성을 키로 테이블을 복제 (구하고자하는 값을 나타내어주는 키를 FK로 빼서 , 복제 테이블의 PK와 연결 해버렷)
  • 별칭만 다르게해서 복제한다.

    셀렉절에서 해당 테이블마다 필요한 속성을 뽑아낸다.
    WHERE절에서 조인조건으로(ex>부모별칭.PK = 자식별칭.FK ) 관계를 맺어준다.

  • 같은 기본키를 가진 후 외래키를 가지고 기본키를 참조받는 테이블이 자식테이블 /기본키를 참조하도록 내어주는 테이블이 부모테이블
-- ------------------------------------------------------
-- D. Self Join (셀프 조인)
-- ------------------------------------------------------
-- 하나의 테이블만 사용하여, 자기자신을 조인할 수도 있는데, 이를
-- Self Join 이라고 한다.
--  가. FROM 절에 같은 테이블을 사용해야 함
--  나. 따라서, 반드시 테이블 별칭을 사용해야 함
--  다. 테이블 하나를, 두 개 이상으로 Self 조인가능
--  라. 하나의 테이블을, 마치 여러 테이블을 사용하는 것처럼,
--      테이블 별칭을 사용하여, 조인하는 방법을 의미
-- ------------------------------------------------------

-- 1) 사원이름과 담당관리자 사원번호를 필요로 하는 경우
SELECT
    last_name, 
    employee_id,        -- 이건 나의 사번
    manager_id          -- 이건 나의 관리자의 사번
FROM 
    employees;


-- 2) 사원이름과 담당관리자 이름을 필요로 하는 경우
--    불가능
-- 
--    But, 사원테이블과 사원테이블과 동일한 구조의 담당관리자
--    테이블이 있다고 가정한다면? 
--    : 두 테이블 조인을 통해, 원하는 데이터의 조회가능
--      실제 존재하지 않는 관리자 테이블 생성은, 테이블 별칭(alias)
--      을 사용하여, 가상의 관리자 테이블을 생성하면 됨. (***)
SELECT
    manager_id,     -- 관리자 사번
    last_name       -- 나의 이름
FROM
    employees e
ORDER BY
    2 ASC;


SELECT employee_id, last_name
FROM EMPLOYEES e
ORDER BY 1 ASC;


-- --------------------------------
DROP TABLE manager PURGE;

CREATE TABLE manager        -- 관리자 테이블
AS
    SELECT
        DISTINCT 
            employee_id,    -- 관리자 사번
            last_name       -- 관리자 이름
    FROM
        employees;

-- 대전제!!!! : 관리자도 사원이다!! 
--              그래서 관리자도 사번이 있다!!

SELECT
    e.employee_id AS 직원사번,
    e.last_name AS 사원명,
    e.manager_id AS 관리자사번1,
    m.employee_id AS 관리자사번2,
    m.last_name AS 관리자명
FROM
    employees e,
    employees m
WHERE
    e.manager_id = m.employee_id;    


-- self 조인을 위한 가상 테이블 생성
SELECT
    -- 사원 테이블의 컬럼들
    e.employee_id AS 사원번호,
    e.last_name AS 사원명,
    e.manager_id AS 관리자번호,

    -- 관리자 테이블의 컬럼들
    m.employee_id AS 사원번호,
    m.last_name AS 관리자명
FROM
    employees e,        -- 사원 정보
    employees m         -- 관리자 정보(가상)
WHERE
    e.manager_id = m.employee_id;

6) 외부조인

-조인조건에서 일치된 값이 없어 NULL로 파기된 본래 테이블의 데이터를 합친 속성의 NULL값을 표시하여 살린다.

  • 살리고싶은 테이블 반대쪽에 (+) 연산자를 속성명 오른쪽에 붙이면 살린다. 거~~의 메딕

-하나만 살릴 수 있는 오라클에 비해 안시조인에서는 왼쪽오른쪽 둘다 살리는 풀 아웃풋 조인을 지원한다. (안시와 오라클의 차이)

-- ------------------------------------------------------
-- E. Outer Join (외부 조인)
-- ------------------------------------------------------
-- Join 조건에 부합하지 않아도, 결과값에 누락된 데이터를 포함시키
-- 는 방법:
--  가. Inner Join (Equal, Non-Equal, Self Join):
--      조인결과는 반드시, 조인조건을 만족하는 데이터만 포함하는 조인
--  나. (+) 연산자를 사용한다.
--  다. (+) 연산자는, 조인대상 테이블들 중에서, 한번만 사용가능
--  라. (+) 연산자는, 일치하는 데이터가 없는 쪽에 지정
--  마. (+) 연산자의 지정:
--      내부적으로, 한 개 이상의 NULL 가진 행이 생성되고,
--      이렇게 생성된 NULL 행들과 데이터를 가진 테이블들의 행들
--      이 조인하게 되어, 조건이 부합하지 않아도, 결과값에 포함됨
-- ------------------------------------------------------
-- Basic Syntax)
--
--  SELECT 테이블1.컬럼 , 테이블2.컬럼
--  FROM 테이블1 , 테이블2
--  WHERE 테이블1.공통컬럼 = 테이블2.공통컬럼 (+);
-- ------------------------------------------------------
-- * Please refer to the chapter 05, page 19.
-- ------------------------------------------------------
SELECT
    e.employee_id AS 사원번호,
    e.manager_id AS 관리자번호,
    e.last_name AS 사원명,
    m.last_name AS 관리자명
FROM
    employees e,        -- 사원 정보
    employees m         -- 관리자 정보(가상)
WHERE
    e.manager_id = m.employee_id;


SELECT
    e.last_name AS 사원명,
    m.last_name AS 관리자명
FROM
    employees e,        -- 사원 정보
    employees m         -- 관리자 정보(가상)
WHERE
    e.manager_id = m.employee_id (+);

-- ------------------------------------------------------

-- 이 쿼리의 목적은, 
-- 각 사원의 "관리자의 관리자"가 누구인지를 찾아내기위해
-- 셀프조인을 2번 하는 것임.
SELECT
    e.last_name AS 사원명,
    m.last_name AS 관리자명,
    mm.last_name AS "관리자의 관리자명"
FROM
    employees e,
    employees m,
    employees mm
WHERE
    e.manager_id = m.employee_id
    AND m.manager_id = mm.employee_id;


SELECT
    e.last_name AS 사원명,
    m.last_name AS 관리자명,
    mm.last_name AS "관리자의 관리자명"
FROM
    employees e,
    employees m,
    employees mm
WHERE
    -- 사원 중에서, 관리자가 없는 사원까지 포함
    e.manager_id = m.employee_id (+)
    -- 관리자 중에서, 관리자가 없는 관리자까지 포함
    AND m.manager_id = mm.employee_id (+);

-- =======================================

SELECT
    -- 사원테이블의 컬럼들
    e.employee_id,
    e.last_name,
    e.department_id,

    -- 부서테이블의 컬럼들
    d.department_id,
    d.department_name
FROM
    employees e,
    departments d
WHERE
    e.department_id = d.department_id;  -- 동등조인

안시조인

*별칭이 있으면 테이블명으로 소속을 밝히면 안된다.

특징:
가. Oracle 이외의 관계형 데이터베이스에서도 사용가능한 표준
나. 여러 테이블의 조인 조건을, WHERE 절에 명시하지 않고,
다른 방법을 통해(주로, FROM절에 기재) 기술
다. 검색조건을 WHERE 절에 기재(조인조건과 검색조건을 분리)
라. 가독성 향상


종류:
a. Cross Join
b. Natural Join
c. USING(Common Columns) or ON
d. JOIN ~ ON
e. { LEFT | RIGHT | FULL } OUTER JOIN
f. Self Join

1)크로스조인(오라클에서는 카테시안 프로덕트)

  • FROM절에서 CROSS JOIN로 표시 양 테이블의 속성을 곱한만큼 생김
    -모호성이 있을 때는 도트연산자 앞에 별칭으로 테이블을 명시해줘야한다.
-- ------------------------------------------------------
-- A. Cross Join
-- ------------------------------------------------------
-- The same as Oracle Cartesian Product.
-- 조인에 참여한 각 테이블의 레코드의 갯수를 모두 곱한 결과 반환
--
-- * 조인결과: 테이블1 x ... x 테이블n 개의 레코드 생성
-- ------------------------------------------------------
-- Basic Syntax)
--  SELECT 테이블1.컬럼 , 테이블2.컬럼
--  FROM 테이블1 CROSS JOIN 테이블2
-- ------------------------------------------------------
SELECT
    count(*)            -- 107
FROM
    employees;


SELECT
    count(*)            -- 27
FROM
    departments;


SELECT
    -- count(*)
    *
FROM
    employees CROSS JOIN departments;


SELECT
    last_name,
    department_name
FROM
    employees CROSS JOIN departments;


SELECT
    -- employees(t1) 에 있는 컬럼
    t1.last_name,           -- OK: 테이블 별침(t1)을 통한 컬럼 지정
    last_name               -- OK: t1에만 있는 컬럼이므로, 모호성 없음

    -- departments(t2) 에 있는 컬럼
    t2.department_name,     -- OK: 테이블 별침(t2)을 통한 컬럼 지정
    department_name         -- OK: t2에만 있는 컬럼이므로, 모호성 없음
    
    -- employees(t1), departments(t2) 모두에 있는 공통컬럼
    t1.manager_id,          -- OK: 테이블 별침(t1)을 통한 컬럼 지정
    -- manager_id,             -- XX: ORA-00918: 열의 정의가 애매합니다

    -- employees(t1), departments(t2) 모두에 있는 공통컬럼
    t2.department_id        -- OK: 테이블 별침(t2)을 통한 컬럼 지정
    -- department_id           -- XX: ORA-00918: 열의 정의가 애매합니다
FROM
    employees t1 CROSS JOIN departments t2;

2) 자연조인 = 동등조인 + 공통컬럼의 중복제거

  • FROM 절에서 NATURAL JOIN 으로 표시 양테이블의 공통컬럼 즉 PK FK의 중복제거
    -모호성을 제거해준다.
    -NATUAL JOIN은 이름만 같다면 동등조인으로 연결 시킨다. 합병가능한 조건을 따지지 않는다.
    -공통컬럼에는 별칭을 붙이면 안된다.(한정되면 안된다.->오라클조인과 차이점 ) 나머지 컬럼은 상관없다.
-- ------------------------------------------------------
-- B. Natural Join (자연조인)
-- ------------------------------------------------------
-- The same as Oracle Equal(= Equi) Join 
-- with implicit columns automatically searched.
-- ------------------------------------------------------
-- ** 자연조인 = 동등조인 + 공통컬럼의 중복제거
-- ------------------------------------------------------
-- 가. 두 테이블의 같은 이름을 가진 컬럼에 기반하여 동작.
-- 나. 두 테이블에 반드시 하나의 공통컬럼이 있어야 함.
-- 다. (*주의*) 만일, 두 개 이상의 공통컬럼이 존재하는 경우,
--     엉뚱한 결과를 생성할 수 도 있음.
--     즉, 두 개 이상의 공통컬럼 값이 동일한 레코드만 조회.
-- 라. 테이블 별칭(Table Alias)도 사용가능.
-- 마. (*주의*) SELECT절에 컬럼 나열시, 두 테이블의 공통컬럼을
--     나열할 때, 테이블명(또는 테이블별칭)을 사용하는 경우 오류발생
--
--     ** ORA-25155: NATURAL 조인에 사용된 열은 식별자를 가질 수 없음
-- ------------------------------------------------------
-- Basic Syntax) FROM절에, NATURAL JOIN 키워드 사용
--
--  SELECT 테이블1.컬럼 , 테이블2.컬럼
--  FROM 테이블1 NATURAL JOIN 테이블2
--  [WHERE 검색조건];
-- ------------------------------------------------------

1.동등조인

-- ------------------------------------------------------
-- 1. Oracle Equal Join
-- ------------------------------------------------------
SELECT
    last_name,
    employees.department_id,
    departments.department_id,
    employees.manager_id,
    departments.manager_id,
    department_name
FROM
    employees,
    departments
WHERE
    -- 두 테이블의 공통컬럼 department_id 으로 연결
    employees.department_id = departments.department_id;    -- 올바른 조인조건
-- WHERE
--     -- 두 테이블의 공통컬럼으로 department_id, manager_id 으로 연결하면,
--     -- 의도하지 않은 결과 도출
--     employees.department_id = departments.department_id
--     AND employees.manager_id = departments.manager_id;

2. 자연조인

-- ------------------------------------------------------
-- 2. ANSI Natural Join
-- ------------------------------------------------------
DESC employees;
DESC departments;


SELECT
    last_name,
    department_id,      -- 공통컬럼1
    manager_id,         -- 공통컬럼2
    department_name
FROM
    -- 공통컬럼: manager_id, department_id    -> 엉뚱한 결과 도출
    employees NATURAL JOIN departments;


-- -------------------------------------------------------
-- ANSI JOIN 수행시, 
-- FROM절과 SELECT절에 테이블 별칭(Table Alis)를 사용하는 경우
-- -------------------------------------------------------
-- SELECT 절에, 테이블 별칭(table alias)이 적용된, 두 테이블의 컬럼 나열시, 
-- 테이블명.컬럼 형식으로 나열하면 오류발생 (테이블 별칭이 적용되었으면, 테이블 별칭 사용가능(옵션))
SELECT
    -- employees.last_name,    -- XX: ORA-00904: "EMPLOYEES"."LASTNAME": 부적합한 식별자
    last_name,              -- OK: 테이블 별칭 없이도 사용가능
    -- t1.last_name,           -- OK: 테이블 별칭 사용가능

    department_name         -- OK: 테이블 별칭 없이도 사용가능
    -- t1.department_name,        -- OK: 테이블 별칭 사용가능
FROM
    -- 공통컬럼: manager_id, department_id    -> 엉뚱한 결과 도출
    employees t1 NATURAL JOIN departments t2;


-- ANSI JOIN 수행시, 
-- FROM절과 SELECT절에 테이블 별칭(Table Alis)를 사용하는 경우
SELECT
    t1.last_name,
    t2.department_name,

    manager_id              -- 두 테이블의 공통컬럼 기재시, 테이블 별칭은 제거해야 함

    -- ORA-25155: column used in NATURAL join cannot have qualifier
    -- t2.manager_id              -- 두 테이블의 공통컬럼 기재
FROM
    -- 공통컬럼: manager_id, department_id    -> 엉뚱한 결과 도출
    employees t1 NATURAL JOIN departments t2;


SELECT
    last_name,
    department_name,
    department_id
FROM
    -- 공통컬럼: manager_id, department_id    -> 엉뚱한 결과 도출
    employees t1 NATURAL JOIN departments t2    -- 조인조건
WHERE
    department_id = 90;                         -- 검색조건

3)USING OR ON (유싱절과 온절)

-자연조인에서 이름만 같다고 중복을 제거하며 엉뚱한 결과가 나오니까 사용자가 지정해서 사용하게끔
-유싱은 사용자의해 공통컬럼을 1개 이상 정하고 동등조인 형식으로 실행
-온은 조인조건을 직접정해라 여기서 중복을 제거 (결국 오라클조인과 같아진다. )
(Inner Join은 서로 매칭되는 것만 엮어 조회한다.)

-- ------------------------------------------------------
-- C. USING(column) or ON <Join Condition>
-- ------------------------------------------------------
-- The same as Oracle Equal Join
-- with explicit columns manually determined.
-- ------------------------------------------------------
-- 가. Natural Join 에서 발생했엇던, 두 개 이상의 공통컬럼에 의해
--    발생가능한 엉뚱한 결과를 예방하기 위해, 명시적으로 조인할 컬럼
--    을 지정하는 방식의 조인
-- 나. Natural Join 과 마찬가지로, 두 테이블의 공통컬럼을 SELECT
--    절에 나열시, 테이블 별칭(Table Alis)이나 테이블명을 앞에
--    붙이는 경우, 오류발생
-- 다. USING(Common Columns):
--    반드시 공통컬럼 값이 일치하는 동등조인(Equal Join) 형식으로
--    실행된다.
-- 라. ON <Join condition>:
--    Non-equal Join 이나, 임의의 조건으로 Join 할 경우에 사용 
-- ------------------------------------------------------
-- Basic Syntax1) USING(Common Columns):
--  FROM절에, [INNER] JOIN / USING 키워드 사용
--
--  SELECT 테이블1.컬럼 , 테이블2.컬럼
--  FROM 테이블1 [INNER] JOIN 테이블2 USING(공통컬럼)
--  [WHERE 검색조건];
-- ------------------------------------------------------
-- Basic Syntax2) ON <Join condition>:
--  FROM절에, [INNER] JOIN / ON 키워드 사용
--
--  SELECT 테이블1.컬럼 , 테이블2.컬럼
--  FROM 테이블1 [INNER] JOIN 테이블2 ON 조인조건
--  [WHERE 검색조건];
-- ------------------------------------------------------

1.USING절

-- ------------------------------------------------------
-- 1. USING(Common Column1, Common Column1, ..)
-- ------------------------------------------------------
-- a. 반드시 공통컬럼값이 일치하는 동등조인(Equal Join) 형식으로 실행
-- b. column part of USING clause cannot have qualifier
-- ------------------------------------------------------
SELECT
    t1.last_name,
    -- last_name,

    t2.department_name,
    -- department_name,
    
    department_id                -- OK: 공통컬럼

    -- ORA-00904: "DEPARTMENTS"."DEPARTMENT_ID": invalid identifier
    -- 두 테이블의 공통컬럼은 식별자를 가질 수 없음.
    -- departments.department_id    -- XX: 공통컬럼

    -- ORA-25154: column part of USING clause cannot have qualifier
    -- t2.department_id             -- XX: 공통컬럼
FROM
    employees t1 INNER JOIN departments t2
    -- employees t1 JOIN departments t2    -- INNER 단어 생략가능
    USING(department_id);


SELECT
    last_name,
    department_name,

    -- 두 테이블의 공통컬럼은 식별자를 가질 수 없음.
    department_id,          -- 공통컬럼1
    manager_id              -- 공통컬럼2
FROM
    employees t1 INNER JOIN departments t2
    -- employees t1 JOIN departments t2    -- INNER 단어 생략가능
    USING(department_id, manager_id)
WHERE
    department_id = 90;  -- 검색조건

-- ORA-25154: column part of USING clause cannot have qualifier
-- WHERE
--     t2.department_id = 90;  -- 검색조건

2. ON절

-- ------------------------------------------------------
-- 2. ON <Join Condition>
-- ------------------------------------------------------
-- Non-equal Join 이나, 임의의 조건으로 Join 할 경우에 사용 
-- ------------------------------------------------------
SELECT
    t1.last_name,
    -- last_name,
    t2.department_name,
    -- department_name,

    -- ORA-00918: column ambiguously defined
    -- department_id           -- XX: 공통컬럼
    t1.department_id        -- OK: 공통컬럼
    -- t2.department_id        -- OK: 공통컬럼
    -- ORA-00904: "DEPARTMENTS"."DEPARTMENT_ID": invalid identifier
    -- departments.department_id        -- XX: 공통컬럼
FROM
    employees t1 INNER JOIN departments t2
    -- employees t1 JOIN departments t2    -- INNER 키워드 생략가능
    -- 명시적으로 조인조건 지정
    ON t1.department_id = t2.department_id;


-- WHERE 절을 이용한 검색조건 추가
SELECT
    last_name,
    department_name,
    t1.department_id
FROM
    employees t1 INNER JOIN departments t2
    -- employees t1 JOIN departments t2            -- INNER 키워드 생략가능
    ON t1.department_id = t2.department_id      -- 조인조건
WHERE
    t1.department_id = 90;                      -- 검색조건


-- ON 절에 검색조건 추가
SELECT
    last_name,
    department_name,
    t1.department_id
FROM
    employees t1 INNER JOIN departments t2
    -- employees t1 JOIN departments t2            -- INNER 키워드 생략가능
    ON t1.department_id = t2.department_id      -- 조인조건
    AND t1.department_id = 90;                  -- 검색조건 (가독성 저하)


-- ON절을 이용한, Self Join
SELECT
    e.last_name AS 사원명,
    m.last_name AS 관리자명
FROM
    employees e INNER JOIN employees m
    -- employees e JOIN employees m                -- INNER 키워드 생략가능
    ON e.manager_id = m.employee_id;            -- 조인조건


-- ANSI Join 에서도, 2개 이상의 테이블 조인 가능.
-- ON 절을 추가로 사용하여, 여러 테이블 조인 수행
-- 3개의 테이블 조인
SELECT
    e.last_name AS 사원명,
    d.department_name AS 부서명,
    g.grade_level AS 등급
FROM
    -- employees e INNER JOIN departments d
    employees e JOIN departments d              -- INNER 키워드 생략가능
    ON e.department_id = d.department_id
    
    -- INNER JOIN job_grades g
    JOIN job_grades g                           -- INNER 키워드 생략가능
    ON e.salary BETWEEN g.lowest_sal AND g.highest_sal; -- Non-equal 조인조건


-- ANSI Join 에서도, 2개 이상의 테이블 조인 가능.
-- Eqaul(= Equi) 조인조건은 ON절 대신에, USING절 사용가능
-- 3개의 테이블 조인
SELECT
    e.last_name AS 사원명,
    d.department_name AS 부서명,
    g.grade_level AS 등급
FROM
    -- employees e INNER JOIN departments d
    employees e JOIN departments d              -- INNER 키워드 생략가능
    USING(department_id)                        -- Equal(= Equi) 조인조건

    -- INNER JOIN job_grades g
    JOIN job_grades g                           -- INNER 키워드 생략가능
    ON e.salary BETWEEN g.lowest_sal AND g.highest_sal; -- Non-equal 조인조건
    
profile
일단 흐자

0개의 댓글