SQL 기초 (4) - 내부조인, 외부조인, 카티션 곱

1. 내부조인

: 여러 테이블의 연결고리 컬럼(각 테이블의 공통컬럼)을 WHERE 절에서 각종 조건 연산자를 사용해 SELECT 문을 작성하는 방법.

예시)
(1) 사원정보 테이블(EMP_MASTER)

CREATE TABLE emp_master
(
  emp_id     NUMBER        NOT NULL,    --사원번호
  emp_name   VARCHAR2(100) NOT NULL,    --사원명
  gender     VARCHAR2(10),              --성별
  age        NUMBER,                    --나이
  hire_date  DATE,                      --입사일자
  dept_id    NUMBER,                    --부서아이디
  address_id NUMBER,                    --주소아이디
  CONSTRAINT emp_master_pk PRIMARY KEY (emp_id)
);

(2) 부서정보 테이블(DEPT_MASTER),

CREATE TABLE dept_master (
  dept_id    NUMBER NOT NULL,         --부서아이디
  dept_name  VARCHAR2(50),            --부서 명
  use_yn     VARCHAR2(2) DEFAULT 'Y', --사용여부
  dept_desc  VARCHAR2(100),           --부서설명
  CONSTRAINT dept_master_pk PRIMARY KEY (dept_id)
);

(3) 주소정보 테이블(ADDRESS_MASTER)

CREATE TABLE address_master (
  address_id   NUMBER NOT NULL,       --주소아이디 
  city         VARCHAR2(100),         --도시 명 
  gu           VARCHAR2(50),          --구 명
  address_name VARCHAR2(100),         --나머지 주소
  CONSTRAINT address_master_pk PRIMARY KEY (address_id)
);

• ANSI 구문으로 작성한 내부조인(INNER JOIN)

SELECT a.emp_id, a.emp_name, a.gender, a.age, a.dept_id,
       b.dept_id, b.dept_name, b.use_yn
  FROM emp_master a
 INNER JOIN dept_master b
    ON a.dept_id = b.dept_id
 ORDER BY a.emp_id;

• ANSI 구문으로 내부조인에서 '남성'만 조회

SELECT a.emp_id, a.emp_name, a.gender, a.age, a.dept_id,
       b.dept_id, b.dept_name, b.use_yn
  FROM emp_master a
 INNER JOIN dept_master b
    ON a.dept_id = b.dept_id
 WHERE a.gender = '남성'
 ORDER BY a.emp_id;

• ANSI 구문으로 주소정보 테이블까지 조인

 SELECT a.emp_id, a.emp_name, a.gender, a.age,
        b.dept_id, b.dept_name, b.use_yn,
        c.address_id, c.city, c.gu, c.address_name
  FROM  emp_master a
 INNER JOIN dept_master b
    ON a.dept_id = b.dept_id
 INNER JOIN address_master c
    ON a.address_id = c.address_id
 ORDER BY a.emp_id;

2. 외부조인

: 여러 테이블 중 한 테이블의 조인 컬럼 값이 없더라도 없는 건까지 모두 포함해 조회하는 방법.

사원정보 테이블에 추가 데이터를 입력해 보겠다.

INSERT INTO emp_master
	   (emp_id, emp_name, gender, age, hire_date, 
       	    dept_id, address_id)
VALUES     (6, '왕건', '남성', 35, 
            TO_DATE('2018-01-01', 'YYYY-MM-DD'), NULL, 4)
ORDER BY 1;
COMMIT;

• ANSI 구문으로 작성한 사원정보 테이블 기준 외부조인 (LEFT JOIN)

SELECT    a.emp_id, a.emp_name, a.gender, a.age, a.dept_id,
          b.dept_id, b.dept_name, b.use_yn
FROM      emp_master a
LEFT JOIN dept_master b
       ON a.dept_id = b.dept_id
ORDER BY  a.emp_id;

• ANSI 구문으로 작성한 부서정보 테이블 기준 외부조인 (RIGHT JOIN)
  먼저 부서정보 테이블에 추가 데이터 입력

INSERT INTO dept_master ( dept_id, dept_name )
VALUES (5, 'IT팀');
COMMIT;

SELECT a.emp_id, a.emp_name, a.gender, a.age, a.dept_id,
       b.dept_id, b.dept_name, b.use_yn
  FROM emp_master a
 RIGHT JOIN dept_master b
    ON a.dept_id = b.dept_id
 ORDER BY a.emp_id;

• 조인에 참여하는 모든 테이블의 값을 조회(FULL OUTER JOIN)

SELECT     a.emp_id, a.emp_name, a.gender, a.age, a.dept_id,
           b.dept_id, b.dept_name, b.use_yn
FROM       emp_master a
FULL OUTER JOIN dept_master b
       ON  a.dept_id = b.dept_id
ORDER BY   a.emp_id;

3. 카티션 곱

: WHERE 절에 INNER JOIN, LEFT JOIN 등 조인 조건을 주지 않는 것

SELECT    a.emp_id, a.emp_name, a.gender, a.age, a.dept_id,
          b.dept_id, b.dept_name, b.use_yn
FROM      emp_master a, 
          dept_master b
ORDER BY  a.emp_id;

사원정보 테이블의 총 로우 수는 6건,
부서정보 테이블의 총 로우 수는 5건이므로 총
30개의 데이터가 조회됨.