6일차_테이블간의 관계, JOIN

----------------------------------------------
데이터베이스 관계

1. 두 테이블 간의 서로 연관된 데이터를 가지고 있는 상태. 두 테이블을 합쳐서 하나의 가상테이블로 검색하는 과정.
 
2. 제약조건 Foreign Key 제약을 지정하면 두 테이블 간에 관계를 맺을 수 있다.

3. 두 테이블 간에 관계가 있는지 확인하는 방법은 테이블에 FK 제약이 지정된 컬럼이 있는지 확인하면 된다.

4. 테이블에는 기본적인 Primary key 제약을 지정하게 된다. 유일한 키 값 지정을 위해서 사용한다.

5. 참조 테이블(PK)의 자료를 참조해서 현재 값을 지정하는 경우 Foreign key 제약을 지정한다. -> 참조값의 범위를 벗어나지 않도록 제약을 건 상태.
예를 들어, 부서 종류를 입력하는 경우 부서명 테이블의 자료를 참조해서 데이터를 입력해야 한다.

6. 제약 조건 확인
USER_CONSTRAINTS 딕셔너리 확인

--현재 소유자가 지정한 제약 조건 전체
SELECT *
 FROM user_constraints;

--CONSTRAINT_TYPE
C : CHECK
P : PRIMARY KEY
R : FOREIGN KEY (관계 설정)
U : UNIQUE

--특정 테이블에 지정한 제약 조건 확인 (테이블 이름은 대문자로 표기)
SELECT *
 FROM user_constraints
 WHERE TABLE_NAME='EMPLOYEES';

--특정 테이블에 지정한 특정 외래키 제약에 대한 참조 테이블 확인
SELECT TABLE_NAME
  FROM user_cons_columns
    WHERE constraint_name=(SELECT R_CONSTRAINT_NAME
 FROM user_constraints
 WHERE TABLE_NAME='EMPLOYEES'
    AND CONSTRAINT_NAME='EMP_JOB_FK');

--특정 테이블에 지정한 모든 외래키 제약에 대한 참조 테이블 확인
SELECT TABLE_NAME
  FROM user_cons_columns
    WHERE constraint_name IN (   
SELECT R_CONSTRAINT_NAME
 FROM user_constraints
 WHERE TABLE_NAME='EMPLOYEES'
    AND CONSTRAINT_TYPE='R');   

 

--------------------------------------------
JOIN

1. 조인을 사용하면 테이블 간의 논리적 관계를 기준으로 둘 이상의 테이블에서 데이터를 검색할 수 있다.

INNER JOIN - 공통 값을 가지는 조건을 만족하는 ROW만 출력
OUTER JOIN - 공통 값을 가지는 조건을 만족하는 경우 외에도 출력

EQUI JOIN - =(equal) 연산자를 이용한 조건을 사용한 JOIN
NON-EQUI JOIN - =(equal) 연산자 외의 연산자를 이용한 조건을 사용한 JOIN

SELF JOIN - 자기 자신을 두 개의 가상 테이블로 만들어서 JOIN하는 방법. 별칭 사용.

ANSI JOIN - JOIN 구문을 오라클 방법이 아니라 표준적인 방법으로 작성한 것.

 

2. employees, departments, job_history, jobs, locations, countries, regions 테이블에서 관계 분석

3.EQUI JOIN
두 개 이상의 테이블에 관계되는 컬럼들의 값들이 일치하는 경우에 사용하는 가장 일반적인 join 형태로 WHERE 절에 '='(등호)를 사용한다. 흔히 PRIMARY KEY, FOREIGN KEY 관계를 이용하며 오라클에서는 NATURAL JOIN이 EQUI JOIN과 동일하다. 또는 USING 절을 사용하여 EQUI JOIN과 동일한 결과를 출력 한다.

형식-1
SELECT 테이블_명1.컬럼_명, 테이블_명2.컬럼_명
 FROM 테이블_명1, 테이블_명2
 WHERE 테이블_명1.컬럼_명1=테이블_명2.컬럼_명1;

형식-ANSI SQL
SELECT 테이블_명1.컬럼_명, 테이블_명2.컬럼_명
 FROM 테이블_명1 JOIN 테이블_명2
 ON 테이블_명1.컬럼_명1=테이블_명2.컬럼_명1;

SELECT * FROM departments;
-->27개 부서아이디(부서명) 존재, PK

SELECT DISTINCT department_id FROM employees;
-->11개 부서아이디 존재, FK, departments 테이블에 존재하는 27개 부서아이디 중에서 11개 참조하는 중이다.
-->department_id 컬럼이 공통컬럼이다. 조인 조건에서 사용 가능.

SELECT e.first_name, e.last_name, e.phone_number
      , d.department_name
  FROM departments d, employees e
  WHERE d.department_id=e.department_id;
--> 106명
--> 1명은 department_id가 NULL인 상태이므로 조인조건에 맞지 않는다.

 

employees, departments 테이블. first_name이 'Nancy', last_name이 'Greenberg'인 직원의 department_name을 같이 출력.

형식-1
SELECT employees.first_name, employees.last_name, departments.department_name
 FROM employees, departments
 WHERE employees.department_id = departments.department_id
  AND 조건 추가;

SELECT e.first_name, e.last_name, d.department_name
 FROM employees e, departments d
 WHERE e.department_id = d.department_id
  AND e.first_name='Nancy'
  AND e.last_name='Greenberg';

형식-ANSI SQL
SELECT employees.first_name, employees.last_name, departments.department_name
 FROM employees JOIN departments
 ON employees.department_id = departments.department_id;

SELECT e.first_name, e.last_name, d.department_name
 FROM employees e JOIN departments d
 ON e.department_id = d.department_id
 WHERE e.first_name='Nancy'
  AND e.last_name='Greenberg';

employees, jobs 테이블. job_title이 'Finance Manager'인 직원 정보 출력.
형식-1
SELECT e.first_name, e.last_name, j.job_title
 FROM employees e, jobs j
 WHERE e.job_id = j.job_id
  AND j.job_title='Finance Manager';

형식-ANSI SQL
SELECT e.first_name, e.last_name, j.job_title
 FROM employees e JOIN jobs j
 ON e.job_id = j.job_id
 WHERE j.job_title='Finance Manager';

 

4. Non-Equi Join
- Join 조건에서 = 연산이 아닌 모든 경우를 말한다.
- 비교 대상인 컬럼 간에 연관성이 없어도 가능하다.

SELECT 컬럼리스트(별칭 사용 필요)
 FROM 테이블1 별칭1, 테이블2 별칭2
 WHERE 별칭1.컬럼 <= 별칭2.컬럼;

SELECT 컬럼리스트(별칭 사용 필요)
 FROM 테이블1 별칭1, 테이블2 별칭2
 WHERE 별칭1.컬럼 >= 별칭2.컬럼;

HR 스키마에서 employees.salary의 금액이 10000~20000 범위에 들어오는 직원의 정보 출력. jobs.job_title, departments.department_name 이 같이 출력되도록 한다.

SELECT e.first_name, e.last_name, e.salary, j.job_title, d.department_name
 FROM employees e, jobs j, departments d
 WHERE e.job_id=j.job_id
  AND e.department_id=d.department_id
  AND e.salary BETWEEN 10000 AND 20000;

HR 스키마에서 employees.salary의 금액이 특정 직무(jobs.job_title이 'Finance Manager')의 jobs.max_salary의 금액(16000)보다 큰 경우만 출력.
-->3명

--서브 쿼리
SELECT e.first_name, e.last_name, e.salary
 FROM employees e
 WHERE e.salary>=(SELECT max_salary FROM jobs WHERE job_title='Finance Manager');

--조인 쿼리
SELECT e.first_name, e.last_name, e.salary --, j.job_title, j.max_salary
 FROM employees e, jobs j
 WHERE e.salary>=j.max_salary
  AND j.job_title='Finance Manager';

HR 스키마에서 employees.salary의 금액이 특정 직무(jobs.job_title이 'Finance Manager')의 jobs.min_salary의 금액(8200)보다 작은 경우만 출력.

SELECT e.first_name, e.last_name, e.salary --, j.job_title, j.min_salary
 FROM employees e, jobs j
 WHERE e.salary<=j.min_salary
  AND j.job_title='Finance Manager';

 

5. Outer Join
- 양쪽 테이블에 동일한 값이 존재하는 경우만 출력되는 경우는 Equi Join(Inner Join)이고,
동일한 값이 없어도 한 쪽 테이블의 자료는 모두 출력되는 경우는 Outer Join입니다.

--LEFT OUTER JOIN(테이블2가 NULL이 출력되는 상태)
SELECT 컬럼리스트
 FROM 테이블1 별칭1, 테이블2 별칭2
 WHERE 별칭1.컬럼 = 별칭2.컬럼(+);

--RIGHT OUTER JOIN(테이블1이 NULL이 출력되는 상태)
SELECT 컬럼리스트
 FROM 테이블1 별칭1, 테이블2 별칭2
 WHERE 별칭1.컬럼(+) = 별칭2.컬럼;

--ANSI LEFT OUTER JOIN(테이블1의 자료는 모두 출력)
SELECT 컬럼리스트
 FROM 테이블1 별칭1 LEFT OUTER JOIN 테이블2 별칭2
 WHERE 별칭1.컬럼 = 별칭2.컬럼;

HR 스키마에서 employees 테이블에 first_name이 'Kimberely'인 직원은 department_id가 NULL인 상태이다.
-> INNER JOIN을 하게 되면 이 직원은 명단에 출력되지 않는다.
SELECT * FROM employees;
--> 107명

SELECT e.first_name, e.last_name, e.department_id, d.department_name
 FROM employees e, departments d
 WHERE e.department_id = d.department_id;
-->106명

SELECT e.first_name, e.last_name, e.department_id, d.department_name
 FROM employees e, departments d
 WHERE e.department_id = d.department_id(+);
-->107명

--서브쿼리인 경우
SELECT first_name, last_name
    , e.department_id
    , (SELECT department_name
  FROM departments
  WHERE department_id=e.department_id) AS department_name
  FROM employees e;

 

SELECT e.first_name, e.last_name, e.department_id, d.department_name
 FROM employees e FULL OUTER JOIN departments d
 ON e.department_id = d.department_id;

6. Self Join
- 일반적인 Join은 두 개의 서로 다른 테이블을 대상으로 Join을 하지만,
Self Join은 자기 자신을 가상의 테이블(테이블1, 테이블2가 모두 자기 자신인 상태)로 생각하고 Join을 진행하는 것.
- 별칭 사용 필수

SELECT 컬럼리스트
 FROM 테이블 별칭1, 테이블 별칭2
 WHERE 별칭1.컬럼 = 별칭2.컬럼;

SELECT 컬럼리스트
 FROM 테이블 별칭1, 테이블 별칭2
 WHERE 별칭1.컬럼 <= 별칭2.컬럼;

SELECT 컬럼리스트
 FROM 테이블 별칭1, 테이블 별칭2
 WHERE 별칭1.컬럼 >= 별칭2.컬럼;

HR 스키마에서 특정 직원(employees.first_name이 'Jonathon', employees.last_name이 'Taylor' 인 직원)의
employees.department_id와 같은 department_id를 가진 직원 정보 출력.
SELECT e2.first_name, e2.last_name, e2.department_id
 FROM employees e1, employees e2
 WHERE e1.department_id = e2.department_id
 AND e1.first_name='Jonathon'
 AND e1.last_name='Taylor';

HR 스키마에서 특정 직원(employees.first_name이 'Janette', employees.last_name이 'King' 인 직원)의
employees.salary와 같은 salary를 가진 직원 정보 출력.
SELECT e2.first_name, e2.last_name, e2.salary
 FROM employees e1, employees e2
 WHERE e1.salary = e2.salary
 AND e1.first_name='Janette'
 AND e1.last_name='King';

HR 스키마 employees 테이블에서 상급자, 부하 직원 정보 출력.
SELECT e1.employee_id, e1.first_name, e1.last_name
 , e2.employee_id, e2.first_name, e2.last_name, e2.manager_id
 FROM employees e1, employees e2
 WHERE e1.employee_id = e2.manager_id
 ORDER BY e1.employee_id;

HR 스키마 employees 테이블에서 특정 직원(first_name이 'Gerald', last_name이 'Cambrault'인 직원)의 부하 직원 정보 출력.
SELECT e1.employee_id, e1.first_name, e1.last_name
 , e2.employee_id, e2.first_name, e2.last_name, e2.manager_id
 FROM employees e1, employees e2
 WHERE e1.employee_id = e2.manager_id
 AND e1.first_name='Gerald'
 AND e1.last_name='Cambrault';

 

--------------------------------------------
JOIN 문제 풀이

1. departments, employees 테이블. 부서명(department_name), 부서장의 이름(first_name, last_name) 출력. 조인 쿼리 이용.
--> departments 테이블의 manager_id를 employees 테이블의 employee_id와 비교

--서브 쿼리의 경우
SELECT d.department_name
 --, 부서장이름 -->employees 테이블 검색 -> manager_id와 employee_id를 비교
 FROM departments d;

SELECT d.department_name
 , ( SELECT first_name FROM employees WHERE employee_id=d.manager_id  ) AS first_name
 , ( SELECT last_name FROM employees WHERE employee_id=d.manager_id ) AS last_name
 FROM departments d;

--JOIN 쿼리의 경우
SELECT d.department_name, e.first_name, e.last_name
 FROM departments d, employees e
 WHERE d.manager_id = e.employee_id;

--위 결과에서 특정부서('IT')만 출력.
SELECT d.department_name, e.first_name, e.last_name
 FROM departments d, employees e
 WHERE d.manager_id = e.employee_id
 AND department_name='IT';

 

2. employees, jobs 테이블. 'Neena', 'Kochhar' 직원의 직무명(job_title)까지 출력. 조인 쿼리 이용.

--서브 쿼리 이용한 경우

--JOIN 쿼리 이용한 경우

 

3. employees, jobs 테이블. 직위명(job_title)별 최소급여(min_salary)를 받는 직원 출력. 조인 쿼리 이용.
-->3명

SELECT e.first_name, e.last_name, j.job_title, j.min_salary
 FROM employees e, jobs j
 WHERE e.job_id = j.job_id
  AND e.salary = j.min_salary;

SELECT e.first_name, e.last_name, j.job_title, j.min_salary
 FROM employees e JOIN jobs j
  ON e.job_id = j.job_id
 WHERE e.salary = j.min_salary;

4. jobs, employees 테이블. 직위명(job_title)별 직원의 수 출력. 조인 쿼리 이용.

--서브 쿼리 이용한 경우

--JOIN 쿼리 이용한 경우

 

5. employees, departments 테이블에서 'David', 'Austin' 직원이 속한
부서명(department_name), 부서장(manager)이름(first_name, last_name) 출력. 조인 쿼리 이용.

--서브 쿼리 이용한 경우

--JOIN 쿼리 이용한 경우

 

 

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

6. employees 테이블에서 'Steven', 'King' 직원의 부하 직원 출력. 자체 조인.

SELECT e2.first_name, e2.last_name
 FROM employees e1, employees e2
 WHERE e1.employee_id = e2.manager_id
  AND e1.first_name='Steven'
  AND e1.last_name='King';

 

7. employees, jobs, job_history 테이블. employee_id가 101인 직원의 잡히스토리(first_name, last_name, start_date, end_date, job_title) 출력.

 

8. employees 테이블에서 부하 직원이 없는 직원 출력.
SELECT e1.employee_id, e1.first_name, e1.last_name
  FROM employees e1, employees e2
  WHERE e1.employee_id=e2.manager_id(+)
  AND e2.manager_id IS NULL;

 

9. employees 테이블에서 특정 직원(first_name이 'Gerald', last_name이 'Cambrault'인 직원)의 salary보다 급여를 더 많이 받는 직원 정보 출력.

 

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