[국비지원] KH 정보교육원 40일차

kh day 040

 오늘은 SQL의 조인을 배웠다. 모두의 SQL만 보고 조인은 오라클 조인만 있는줄 알았는데, ANSI 조인이라는 개념도 있다고 한다! 모두의 SQL은 아무래도 입문자용 기초만 나와있는 책이다보니 해당 내용이 빠져 있는것 같았다. 강사님께서 말씀하시길 혼동이 오면 오라클 조인만 확실하게 알아두라고 하셨는데, 찾아보니 아무래도 ANSI도 확실히 해두는 편이 좋을 것 같다. 주말에 SQL 배운거 다 누적복습해야지! 오늘 자바 시간엔 좋은 말씀을 많이 해주셨다. 자바가 고급수준으로 올라가니 이제 포기하고 싶은 생각이 들 수도 있는데, 마음을 다잡고 책이 너덜너덜해질 때까지 공부하고, 손에 익을 때까지 실습하면 원하는 곳에 이를 수 있다고 격려해주셨다. 이런 강사님을 만난 것은 참 행운이라고 생각한다.

 

---

 

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

 : WHERE절에 조인조건을 지정할 때, 동등연산자(=) 이외의, 비교 연산자(>,<,>=,<=,!=)를 사용하는 조인
------------------------------------------------------

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

-- 테이블에 데이터 입력
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);

 

-- 2개의 테이블 조인 (비동등조인)
-- WHERE절에서 조인조건이 '테이블갯수 - 1개' 나와야함 = 1개!
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; (얘는 조인이 아님!! WHERE절의 체크조건임!)

 

-- 3개의 테이블 조인 (Equi Join + Non-equi Join)
-- WHERE절에서 조인조건이 '테이블갯수 - 1개' 나와야함 = 2개!
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

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

하나의 테이블만 사용하여, 자기자신을 조인할 수도 있는데, 이를 Self Join 이라고 한다.
 가. FROM 절에 같은 테이블을 사용해야 함
 나. 따라서, 반드시 테이블 별칭을 사용해야 함
 다. 테이블 하나를, 두 개 이상으로 Self 조인가능
 라. 하나의 테이블을 마치 여러 테이블을 사용하는 것처럼 테이블 별칭을 사용하여, 조인하는 방법을 의미
------------------------------------------------------


1) 사원이름과 담당관리자 사원번호를 필요로 하는 경우 : 하나의 쿼리로 충분히 가능

SELECT
    last_name,           -- 나의 이름
    employee_id,        -- 나의 사번
    manager_id          -- 나의 관리자의 사번
FROM 
    employees;


2) 사원이름과 담당관리자 이름을 필요로 하는 경우 : 하나의 쿼리로 불가능

사원테이블과 사원테이블과 동일한 구조의 담당관리자 테이블이 있다고 가정하고 두 테이블 조인을 통해, 원하는 데이터의 조회가능
실제 존재하지 않는 관리자 테이블 생성은, 테이블 별칭(alias)을 사용하여, 가상의 관리자 테이블을 생성하면 됨. (***)


-- 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;

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

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

 가. cf) Inner Join (Equi, Non-Equi, Self Join):
     조인결과는 반드시, 조인조건을 만족하는 데이터만 포함하는 조인
 나. (+) 연산자를 사용한다.
 다. (+) 연산자는, 조인대상 테이블들 중에서, 한번만 사용가능 (양쪽에 사용불가)
 라. (+) 연산자는, 일치하는 데이터가 없는 쪽에 지정
 마. (+) 연산자의 지정:
     내부적으로, 한 개 이상의 NULL 가진 행이 생성되고,
     이렇게 생성된 NULL 행들과 데이터를 가진 테이블들의 행들
      이 조인하게 되어, 조건이 부합하지 않아도, 결과값에 포함됨
------------------------------------------------------
SELECT 테이블1.컬럼 , 테이블2.컬럼
FROM 테이블1 , 테이블2
WHERE 테이블1.공통컬럼 = 테이블2.공통컬럼 (+);

* 데이터가 부족한 쪽에 (+)를 붙인다
------------------------------------------------------

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

-- 회장님은 관리자가 없으므로 탈락!

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

-- 외부조인으로 관리자가 없는 회장님도 포함시키자
-- 사원정보를 살려야하니 관리자 정보에 (+)를 붙인다
-- 다른방법 : 데이터가 부족한 쪽에 (+) 붙인다

 

 

------------------------------------------------------
ANSI Join

가. Oracle 이외의 관계형 데이터베이스에서도 사용가능한 표준
나. 여러 테이블의 조인 조건을, WHERE 절에 명시하지 않고, 다른 방법을 통해(주로, FROM절에 기재) 기술
다. 검색조건을 WHERE 절에 기재(조인조건과 검색조건을 분리)
라. 가독성 향상
------------------------------------------------------
ANSI Join의 종류
------------------------------------------------------
a. Cross Join 
   : 오라클의 Cartesian Product와 동일
b. Natural Join    
  : 오라클의 Equi Join과 동일
       with implicit columns automatically searched.
c. USING(Common Columns) or ON <Join Condition>
    : 오라클의 Equi Join과 동일
       with explicit columns manually determined.
d. JOIN ~ ON
    : 오라클의 Non-equi Join과 동일
e. { LEFT | RIGHT | FULL } OUTER JOIN
    : 오라클의 Outer Join과 동일
f. Self Join
   : 오라클의 Self Join과 동일

------------------------------------------------------
Cross Join
  : 오라클의 Cartesian Product와 동일

조인에 참여한 각 테이블의 레코드의 갯수를 모두 곱한 결과 반환
* 조인결과: 테이블1 x ... x 테이블n 개의 레코드 생성
------------------------------------------------------
SELECT 테이블1.컬럼 , 테이블2.컬럼
FROM 테이블1 CROSS JOIN 테이블2
------------------------------------------------------
SELECT
    count(*)            -- 107
FROM
    employees;


SELECT
    count(*)            -- 27
FROM
    departments;


SELECT 
    count(*)           --  107 * 27 = 2889
FROM
    employees CROSS JOIN departments;

---

별칭 사용 문제 : 컬럼의 모호성

------------------------------------------------------
Natural Join (자연조인)
  : 오라클의 Equi Join과 동일 + 중복된 컬럼을 자동으로 제거
    자연조인 = 동등조인 + 공통컬럼의 중복제거

가. 두 테이블의 같은 이름을 가진 컬럼에 기반하여 동작.
나. 두 테이블에 반드시 하나의 공통컬럼이 있어야 함.
다. (*주의*) 만일, 두 개 이상의 공통컬럼이 존재하는 경우, 엉뚱한 결과를 생성할 수 도 있음.
     즉, 두 개 이상의 공통컬럼 값이 동일한 레코드만 조회.
라. 테이블 별칭(Table Alias)도 사용가능.
마. (*주의*) SELECT절에 컬럼 나열시, 두 테이블의 공통컬럼을 나열할 때, 테이블명(또는 테이블별칭)을 사용하는 경우 오류발생

** ORA-25155: NATURAL 조인에 사용된 열은 식별자를 가질 수 없음
------------------------------------------------------
SELECT 테이블1.컬럼 , 테이블2.컬럼
FROM 테이블1 NATURAL JOIN 테이블2
[WHERE 검색조건];
------------------------------------------------------

SELECT
    last_name,
    department_id,      -- 공통컬럼1
    manager_id,         -- 공통컬럼2
    department_name
FROM
    employees NATURAL JOIN departments;

-- 공통컬럼: manager_id, department_id    -> 엉뚱한 결과 도출


-------------------------------------------------------
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
    employees t1 NATURAL JOIN departments t2;
    -- 공통컬럼: manager_id, department_id    -> 엉뚱한 결과 도출




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