ORACLE Join

 

 

cross join

- 두개의테이블을 where절을 갖지 않고 모든 경우의 수로 연결한다 

 

조인의 규칙

1. Primary Key와 Foreign Key 컬럼을 통한 다른 테이블의 행과 연결한다

2. 연결 Key 사용으로 테이블과 테이블이 결합한다

3. WHERE절에서 조인 조건 사용(조인 조건 개수 = 연결 테이블 수 =1_

4.  명확성을 위해 칼럼 이름 앞에 테이블명 또는 테이블 별칭 붙임

 

하나의 테이블을 먼저 읽고(선행테이블) 조인 조건절 확인하고

나머지 테이블(후행 테이블)에 가서 데이터를 가져오는데 선행테이블의 조회할 데이터가 적아야 유리

 

oracle 전용 조인 방법

SELECT FIRST_NAME, DEPARTMENT_NAME

FROM EMPLOYEES, DEPARTMENTS

WHERE EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID;

 

- 공통컬럼 조회 시 테이블명.컬럼 또는 별칭.컬럼으로 명시해야 된다

 

 

 

Equi Join(Inner Join)

- 오라클 전용 SELECT 컬럼 FROM 테이블명, 테이블명 WHERE JOIN 조건문

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

 

- 조인시 공통컬럼을 조회하고자 할때는 반드시 테이블명.컬럼 또는 테이블별칭.컬럼으로 명시

-- ORA-00918: 열의 정의가 애매합니다
SELECT first_name, department_id, department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id;

 

- 테이블 별칭 사용

SELECT first_name, department_name, E.department_id
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.department_id = D.department_id;

 

- 조건 여러개 일땐 AND 사용

SELECT E.first_name, D.department_name
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.department_id = D.department_id AND E.first_name = 'Susan';

 

Non-Equi Join

- 두 개의 테이블 간에 칼럼 값들이 서로 정확하게 일치하지 않는 경우에 사용된다.

CREATE TABLE salarygrade(
    grade NUMBER,
    minsalary NUMBER,
    maxsalary NUMBER
);

INSERT INTO salarygrade(grade, minsalary, maxsalary) VALUES(1, 2000, 3000);
INSERT INTO salarygrade(grade, minsalary, maxsalary) VALUES(2, 3001, 4500);
INSERT INTO salarygrade(grade, minsalary, maxsalary) VALUES(3, 4501, 6000);
INSERT INTO salarygrade(grade, minsalary, maxsalary) VALUES(4, 6001, 8000);
INSERT INTO salarygrade(grade, minsalary, maxsalary) VALUES(5, 8001, 10000);
INSERT INTO salarygrade(grade, minsalary, maxsalary) VALUES(6, 10001, 13000);
INSERT INTO salarygrade(grade, minsalary, maxsalary) VALUES(7, 13001, 20000);
INSERT INTO salarygrade(grade, minsalary, maxsalary) VALUES(8, 20001, 30000);

COMMIT;

SELECT * FROM salarygrade;
-- 사원명, 급여, 등급 출력
SELECT E.first_name, E.salary, S.grade
FROM EMPLOYEES E, SALARYGRADE S
WHERE E.salary BETWEEN S.MINSALARY AND S.MAXSALARY;

 

Outer Join

- 조인 조건에 만족하지 않는 행들도 나타내기 위해 Outer Join 사용됨

- outer join을 하기 위해서 사용하는 기호는 (+)이고, 조인 조건 정보가 부족한 칼럼명 뒤에 위치하게 하면된다

SELECT E.FIRST_NAME, D.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID(+) = D.DEPARTMENT_ID;

 

 

- 모든 경우의 수 출력됨

SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE, DEPARTMENT_ID
FROM EMPLOYEES
WHERE HIRE_DATE BETWEEN '2007/01/01' AND '2007/06/30';

- 조건에 의한 출력 (NULL 값 제외됨)

SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.HIRE_DATE, D.DEPARTMENT_NAME
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
      AND HIRE_DATE BETWEEN '2007/01/01' AND '2007/06/30';

- Outer Join 에 의해 NULL 값도 출력됨

SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.HIRE_DATE, D.DEPARTMENT_NAME
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID(+)
      AND HIRE_DATE BETWEEN '2007/01/01' AND '2007/06/30';

null 값 출력

 

Self Join

- Self Join 이란, 1개의 테이블에 가상으로 별칭을 부여하여 2개의 테이블인 것처럼 간주한 뒤 JOIN하는 것이다.

- WHERE 절에도 조인 조건을 주어야 하는데 이때 서로 다른 테이블인것처럼 인식할수 있도록 하기 위해 별칭 사용

- 별칭을 사용하여 하나의 테이블을 두개의 테이블인 거처럼 사용 (별칭 부여)

SELECT WORK.FIRST_NAME 사원명, MANAGER.FIRST_NAME 매니저명
FROM EMPLOYEES WORK, EMPLOYEES MANAGER
WHERE WORK.MANAGER_ID = MANAGER.EMPLOYEE_ID;

 

 

ANSI Join

- ANSI sql은 대부분의 상용 데이터베이스 시스템에서 표준 언어

- 다른 DBMS와의 호환성을 위해 ANSI 조인 사용하는 것이 좋음

 

ANSI Cross Join

select * from employees cross join departments;

 

ANSI Inner Join

- JOIN의 기본값은 INNER JOIN

SELECT * FROM tabl1 INNER JOIN table2
ON table1.column1 = table2.column2;

SELECT first_name, department_name
FROM employees E INNER JOIN departments D
ON E.department_id = D.department_id;

 

- 세개 테이블 JOIN

SELECT E.first_name, E.job_id, J.job_title, E.department_id, D.department_name
FROM EMPLOYEES E INNER JOIN JOBS J ON E.job_id = J.job_id
                 INNER JOIN departments D ON E.department_id = D.department_id;

 

- 연결에 사용하려는 컬럼 명이 같은 경우 USING() 사용, 다른 경우 ON() 사용

SELECT E.first_name, D.department_name
FROM employees E INNER JOIN deartments D
USING(department_id);

 

- 조인의 조건과 데이터 검색을 위한 조건 여부

SELECT first_name, department_name
FROM employees E INNER JOIN departments D
ON E.department_id = D.department_id
WHERE first_name = 'Susan';

 

ANSI Outer Join

- ANSI 구문의 OUTER JOIN 에서는 이전에 지원하지 않았던 FULL까지 지원한다

SELECT * FROM table1 [LEFT|RIGHT|FULL] OUTER JOIN table2 ON 조건문

SELECT E.first_name, D.department_id, D.department_name
FROM employees E RIGHT OUTER JOIN departments D
ON E.department_id = D.department_id;