KH 46일차 - Join ( 조인 ) (***)

-- *******************************************

-- SELECT 문의 기본구조와 각 절의 실행순서

-- *******************************************

--  - Clauses -                 - 실행순서 -

--

-- SELECT clause                        (5)

-- FROM clause                          (1)

-- [ WHERE clause ]                     (2)

-- [ GROUP BY clause ]                  (3)

-- [ HAVING clause ]                    (4)

-- [ ORDER BY clause ]                  (6)

-- *******************************************

---

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

-- 0. Cartesian Product 집합연산자에 대해서 알아보자!

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

-- 두 테이블(== 집합) A, B가 있을 때, C/P = |A| x |B| (***)

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

SELECT

    count(*)

FROM

    departments;

-- 와일드카드 *를 사용하여 부서에 있는 투플(원소)의 개수가 총 몇개인지 확인하였다. (27개)

-- 하지만, 와일드카드인 *를 사용하는 것보다는 PK(기본키)를 활용하여 카운팅하는 것이 좋다.

SELECT

    count(*)

FROM

    regions;

-- 와일드카드를 활용하여 regions라는 테이블의 투플의 개수를 카운팅하였다. (4개)

-- 이때 카운팅하는 것은 스키마가 아니라 투플이다.

SELECT

    *

FROM                    -- Cartesian Product

    regions,            -- 4개의 원소

    departments;        -- 27개의 원소

-- FROM절에는 테이블이 1개만이 아니라 N개가 올 수 있으며, 이때 구분자는 ' , '이며 Cartesian Product 집합연산이 이루어진다. (***)

-- 결과셋을 보면 108개가 출력되는데, 그 이유는 regions가 4개의 원소를 가지고 있고 departments가 27개의 원소를 가지고 있어  4*27 = 108이기 때문이다.

-- 이때 가로병합이 이루어지게 되는데, 이름이 충돌하여도 가로로 병합해버린다.

 

---

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

-- 1. 부모 테이블과 자식 테이블 간의 관계

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

-- parent(=master) table, child(=slave) table

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

-- Child(= slave) table to refer to others.

DESC employees;

-- DESC는 릴레이션의 스키마를 보여준다.

SELECT

    last_name,

    department_id

FROM

    employees

ORDER BY

    department_id ASC;

-- employees 릴레이션에서 이름과 부서 속성을 가져와 부서를 기준으로 오름차순 정렬하였다.

SELECT

    department_id,

    department_name

FROM

    employees

ORDER BY

    department_id ASC;

-- employees 릴레이션에서 부서번호와 부서이름이라는 속성을 가지고 와서 부서번호를 기준으로 오름차순 정렬하였다.

-- 찾아낸 부서번호를 이용한 부서명 조회 (조인x)

SELECT

    department_name

FROM

    departments

WHERE

    department_id = 10;

-- 부서 릴레이션에서 부서번호가 10인 부서를 지정하여 이 부서의 이름을 출력하였다.

 

 

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

-- "JOIN" : 필요한 데이터가, 여러 테이블에 분산되어 있을 경우에,

-- 여러 테이블의 공통된 컬럼을 연결시켜, 원하는 데이터를 검색하는

-- 방법을 "조인"이라 한다.

--

-- 따라서, 조인은 검색하고자 하는 컬럼이, 두개 이상의 테이블에

-- 분산되어 있는 경우에 사용된다.

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

---

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

-- 2. Oracle Join (****)

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

--  가. Oracle에서만 사용되는 조인

--  나. 특징: 여러 테이블을 연결하는 조인조건을 WHERE절에 명시 (***)

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

--  a. Catesian Product (카테시안 프로덕트)

--  b. Equal(= Equi) Join (동등 조인)

--  c. Non-equal(= Non-equi) Join (비동등 조인)

--  d. Self Join (셀프 조인)

--  e. Outer Join (외부 조인)

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

---

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

-- A. Catesian Product (카테시안 프로덕트)

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

-- 두 개 이상의 테이블을 공통컬럼없이 연결하는 조인으로,

-- 모든 조인에서 가장 먼저 발생하는 조인이자 기본이 됨.

--  가. 유효한 데이터로 사용되지 못함.

--  나. 조인조건이 생략된 경우에 발생.

--

-- * 조인결과: 테이블1 x ... x 테이블n 개의 레코드 생성

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

-- Basic Syntax)

--  SELECT 테이블1.컬럼 , 테이블2.컬럼

--  FROM 테이블1, 테이블2

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

SELECT

    last_name,

    department_name

FROM

    employees,

    departments;

-- 이름과 부서명을 각각 EMPLOYEES테이블과 DEPARTMENTS테이블에서 가져온다.

-- 이 경우 결과셋을 보면 데이터의 일관성이 깨진 상태로 출력이 된다.

-- 만약 두 테이블 중 한쪽에만 존재하는 컬럼으로 구성되어 있으면,

-- 테이블.컬럼에서 테이블을 생략하여 컬럼만 작성해도 괜찮다.

 

---

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

-- B. Equal(= Equi) Join (동등 조인)

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

-- 가. 가장 많이 사용하는 조인

-- 나. 두 테이블에서, 공통으로 존재하는 컬럼의 값이 일치하는 행들을

--     연결하여 데이터를 반환. (조인조건 : 병합이 가능해야 한다, UNION Compatible)

--     일치하지 않는 데이터는 제외됨.

-- 다. 대부분, 기본키(PK)를 가진 테이블(Parent, Master)과

--     참조키(FK)를 가진 테이블(Child, Slave)을 조인할 때 사용

-- 이때 공통으로 존재하는 컬럼은 이름이 같은 컬럼이 아니라 속성의 성질이 같은 컬럼을 의미한다.

-- Join을 하기 위해서는 속성의 성질이 같아야 한다는 조건이 있다.

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

-- Basic Syntax)

--  SELECT 테이블1.컬럼 , 테이블2.컬럼

--  FROM 테이블1, 테이블2

--  WHERE 테이블1.공통컬럼 = 테이블2.공통컬럼; (***)

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

SELECT

    last_name,

    employees.department_id,            --  FK

    departments.department_id,          --  PK

    department_name

FROM

    employees,

    departments

WHERE

    employees.department_id = departments.department_id;

-- 만약 두 테이블 간에 PK와 FK의 관계가 있다면 반드시 테이블.컬럼의 형태로 작성해야 한다.

-- employees와 departments 릴레이션에서 서로 부서번호가 같은 컬럼만 가지고 와 이름, 부서번호, 부서이름을 출력한다.

-- 이때 컬럼의 이름이 달라도, 속성의 성질이 같으면 이와 같은 단계를 거쳐야 한다.

-- 이렇게 작성해야만 일관성이 있는 유의미한 데이터가 출력된다.

-- FK의 경우 null을 허용하는데, PK는 null을 허용하지 않기에 동등비교연산자로 필터링을 하게 되면 출력되지 않는 직원이 나올 수도 있다.

DESC DEPARTMENTS;

DESC EMPLOYEES;

SELECT

    last_name,

    department_id

FROM

    employees

WHERE

    department_id IS NULL;

-- 부서번호가 NULL로 되어 있는 직원을 출력하는 문을 작성하였다.

 

---

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

-- 공통 칼럼 사용 시, 모호성 제거

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

SELECT

    last_name,

    department_name,

    departments.department_id

FROM

    employees,

    departments

WHERE

    employees.department_id = departments.department_id;

-- departments.department_id로 작성한 이유는 employees와 departments 테이블에서 모두 department_id가 존재하기 때문이다.

-- department_id만 작성할 시에는 모호성이 존재하기에, 오류가 발생할 수 있다.

 

---

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

-- 테이블에 별칭 사용

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

-- 가. SELECT 절에서, 컬럼 별칭(Column Alias)을 사용했듯이,

--     FROM 절에서도, 테이블 별칭(Table Alias)을 사용가능하다.

-- 나. 테이블명이 길거나, 식별이 힘든 경우에 유용하다.

-- 다. (*주의*) 테이블 별칭을 지정한 경우에는, 반드시 이 별칭을

--     사용하여, 컬럼을 참조해야 한다.

--     만일, 테이블 별칭을 사용하지 않고, 테이블명으로 컬럼을

--     참조하면, 테이블명을 별칭(Alias)으로 인식하기 때문에,

--     오류 발생.

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

-- Basic Syntax)

--      SELECT alias1.컬럼 , alias2.컬럼

--      FROM 테이블1 alias1, 테이블2 alias2

--      WHERE alias1.공통컬럼 = alias2.공통컬럼;

-- FROM절에서 별칭을 생성해야 한다.

-- 테이블에 별칭을 만들때에는 AS를 붙여서는 안된다. (*)

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

SELECT

    emp.last_name,

    dept.department_name,

    -- employees.department_id      -- 오류발생

    emp.department_id

FROM

    employees emp,                  -- emp라는 별칭 생성

    departments dept                -- dept라는 별칭 생성

WHERE

    emp.department_id = dept.department_id;

-- 테이블의 경우 별칭을 생성하였으면, 그 테이블의 별칭을 사용해야 한다. (***)

-- PK와 FK의 관계가 있는 테이블은 무조건 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';                   -- 검색조건

-- 조건이 여러 개일 경우에는 조인조건을 먼저 작성해야 한다. (**)

-- salary나 department_name 칼럼의 경우, 각각 emp와 dept에 겹치지 않고 따로 존재하기에 테이블명을 작성하지 않아도 괜찮다.

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

    department_name;

-- 집계함수와 단순컬럼을 함께 사용하기 위해서는 GROUP BY절을 생성해야 한다.

-- 테이블의 별칭은 주로 d나 e처럼 단일 문자로 형성하는 경우가 많다.

-- department_name는 DEPARTMENTS 릴레이션에만 존재하기에 별칭이나 테이블명을 지정하지 않아도 사용할 수 있다.

 

 

---

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

-- C. Non-equal(= Non-equi) Join (비동등 조인)

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

-- 가. WHERE절에 조인조건을 지정할 때, 동등연산자(=) 이외의,

--     비교 연산자(>,<,>=,<=,!=)를 사용하는 조인

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

-- 테이블 생성 및 데이터 넣기

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;   -- job-grades의 모든 컬럼을 출력한다.

-- 2개의 테이블 조인

SELECT

    last_name,

    salary,

    grade_level

FROM

    employees e,

    job_grades g

WHERE

    e.salary BETWEEN 1000 AND 3000

ORDER BY

    last_name;

-- employees와 job_grades의 테이블을 조인하고 각각 별칭을 생성한 후, salary가 1000이상 3000이하의 직원을 출력한다.

-- 하지만 결과셋을 보면, 직원의 등급이 해당 월급에 해당하는 등급이 아닌 여러 등급이 나오는 것을 알 수 있다.

SELECT

    last_name,

    salary,

    grade_level

FROM

    employees e,

    job_grades g

WHERE

    e.salary BETWEEN g.lowest_sal AND g.highest_sal;

-- where절에서 월급이 해당 등급 상한선과 하한선 내에 존재하는 것만 출력하라고 필터링을 하였기에, 해당 등급만 출력된다.

-- 3개의 테이블 조인 (***)

SELECT

    last_name,

    salary,

    department_name,

    grade_level

FROM

    employees e,

    departments d,

    job_grades j

WHERE

    e.department_id = d.department_id

    AND e.salary BETWEEN j.lowest_sal AND j.highest_sal

ORDER BY

    grade_level ASC;

--  e.department_id = d.department_id는 동등조인

-- e.salary BETWEEN j.lowest_sal AND j.highest_sal는 Non-equal Join에 해당한다.

-- 조인조건은 조인대상인 테이블의 수 - 1개가 나온다. (***)

-- 이 경우 부서의 번호가 같고 상한선과 하한선 사이에 속하면 결과셋이 출력한다.

-- job_grades는 범위로 구성되어 있기에, WHERE절에서 조인할 때에도 범위에 맞게 범위연산자로 구성해야 한다.

 

---

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

-- D. Self Join (셀프 조인)

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

-- 하나의 테이블만 사용하여, 자기자신을 조인할 수도 있는데, 이를

-- Self Join 이라고 한다.

--  가. FROM 절에 같은 테이블을 사용해야 함

--  나. 따라서, 반드시 테이블 별칭을 사용해야 함 (**)

--  다. 테이블 하나를, 두 개 이상으로 Self 조인가능

--  라. 하나의 테이블을, 마치 여러 테이블을 사용하는 것처럼,

--      테이블 별칭을 사용하여, 조인하는 방법을 의미

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

-- 1 ) 사원이름과 담당관리자 사원번호를 필요로 하는 경우

SELECT

    last_name,

    employee_id,

    manager_id

FROM

    employees;

-- employees에 있는 직원의 이름과 사원번호, 관리자 사원번호를 결과셋에 출력한다.

-- 2 ) 사원이름과 담당관리자 이름을 필요로 하는 경우,

--    1개의 쿼리만으로는 불가능

--

--    But, 사원테이블과 사원테이블과 동일한 구조의 담당관리자

--    테이블이 있다고 가정한다면?

--    : 두 테이블 조인을 통해, 원하는 데이터의 조회가능

--      실제 존재하지 않는 관리자 테이블 생성은, 테이블 별칭(alias)

--      을 사용하여, 가상의 관리자 테이블을 생성하면 됨. (***)

SELECT

    manager_id,

    last_name

FROM

    employees e

ORDER BY

    last_name ASC;

-- 이 경우에는 단순히 employees에서 메니저 id와 이름을 가지고 온 것 뿐이다. (셀프 조인이 아니다.)

-- 대전제!!!! : 관리자도 사원이다!!

--              그래서 관리자도 사번이 있다!!

-- self 조인을 위한 가상 테이블 생성

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;

-- 셀프 조인에서는 자기자신의 테이블을 복사해서 조인하기 때문에, 별칭을 따로 만들어야 한다.

-- e.manager_id = m.employee_id에서 둘은 서로 이름이 서로 다르지만,

-- 같은 속성의 성질을 지니고 있으며 PK와 FK로 이루어져있기에 동등조인해야 한다. (**)

 

---

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

-- E. Outer Join (외부 조인)

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

-- Join 조건에 부합하지 않아도, 결과값에 누락된 데이터를 포함시키

-- 는 방법:

--  가. Inner Join (Equal, Non-Equal, Self Join):

--      조인결과는 반드시, 조인조건을 만족하는 데이터만 포함하는 조인

--  나. (+) 연산자를 사용한다.

--  다. (+) 연산자는, 조인대상 테이블들 중에서, 한번만 사용가능

--  라. (+) 연산자는, 일치하는 데이터가 없는 쪽에 지정

--  마. (+) 연산자의 지정:

--      내부적으로, 한 개 이상의 NULL 가진 행이 생성되고,

--      이렇게 생성된 NULL 행들과 데이터를 가진 테이블들의 행들

--      이 조인하게 되어, 조건이 부합하지 않아도, 결과값에 포함됨

-- 외부조인은 동등조인에서 (조건을 만족하지 못하여) 탈락한 테이블의 행들도 보이게 한다.

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

-- Basic Syntax)

--

--  SELECT 테이블1.컬럼 , 테이블2.컬럼

--  FROM 테이블1 , 테이블2

--  WHERE 테이블1.공통컬럼 = 테이블2.공통컬럼 (+);

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

SELECT

    e.employee_id AS 사원번호,

    e.manager_id AS 관리자번호,

    e.last_name AS 사원명,

    m.last_name AS 관리자명

FROM

    employees e,        -- 사원 정보

    employees m         -- 관리자 정보(가상)

WHERE -- FK         -- PK

    e.manager_id = m.employee_id -- 동등조인

ORDER BY

    e.employee_id DESC;

-- 사원정보 테이블에서의 관리자사번은 관리자 정보 테이블에서의 사원번호와 같기에 동등조인을 해주었다.

-- 이때 KING의 경우에는 KING의 관리자가 없기에 탈락하게 된다. 그렇다면 KING을 살리기 위해서는 어떻게 해야 할까?

SELECT

    e.last_name AS 사원명,

    m.last_name AS 관리자명

FROM

    employees e,

    employees m

WHERE

    e.manager_id = m.employee_id (+);       -- Left 외부조인 연산자

    --  e.manager_id (+) = m.employee_id;   -- Right 외부조인 연산자

-- 외부조인의 경우 (+)가 붙은 편이 아니라, 반대편을 살리게 된다.

-- 즉 이 경우에는 m.employee_id에 붙었기 때문에, e.manager_id의 데이터를 살리게 된다.

-- 살리지 않은( (+)가 붙은 ) 쪽에는 NULL이 들어가게 된다.

-- Oracle에서는 오른쪽, 왼쪽을 살리는 외부조인은 있으나, full은 없다. ( 양쪽에 붙이지 못한다. )