2.Oralce SQL (조인)JOIN

2022.07.21.목

---

가.조인

 -개념 :여러 테이블을 연결해서 필요한 데이터를 조회하는 방법

 

나.오라클 조인

 -Equi 조인

=>가장 많이 사용되는 조인 방법으로 조인 대상이 되는 두 테이블에서 공통적으로

     존재하는 컬럼의 값이 반드시 일치하는 행을 연결하여 데이터를 반환하는 조인

=>일치하지 않는 데이터가 존재하는 경우에는 제외되며 대부분 기본키(Primary Key)을

    가진 테이블(master)과 참조키(Foreign Key)을 가진 테이블(slave)을 조인할 때 사용

/*
문법
SELECT 테이블1.컬럼 , 테이블2.컬럼
FROM 테이블1 , 테이블2
WHERE 테이블1.공통컬럼 = 테이블2.공통컬럼;
*/


--사원테이블과 부서 테이블을 Equi 조인 사용하여 사원명과 부서명 출력
SELECT last_name,department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id;

/*
SELECT절에 사용된 컬럼은 양쪽 테이블에 모두 존재하는 컬럼이 아니기
때문에 컬럼명 앞에 테이블명을 지정하지 않아도 됨
->양쪽 테이블에 모두 존재하면 컴럼명 앞에 명시 해야함
*/

--별칭사용

/*
문법
SELECT alias1.컬럼 , alias2.컬럼
FROM 테이블1 alias1, 테이블2 alias2
WHERE alias1.공통컬럼 = alias2.공통컬럼;
->별칭사용시 반드시 별칭을 사용하여 컬럼을 참조해야 함
*/

SELECT emp.last_name,department_name, emp.department_id
FROM employees emp, departments dept
WHERE emp.department_id = dept.department_id;

--검색 조건 추가

SELECT emp.last_name,salary,department_name
FROM employees emp, departments dept
--조인 조건 먼저 명시 후 검색 조건 명시
WHERE emp.department_id = dept.department_id --조인조건
AND last_name='Whalen'; --검색조건

 

-Non_Equi 조인

=>Non-Equi 조인은 WHERE절에 조인 조건을 지정할 때

    동등 연산자(=) 이외의 비교 연산자를 사용하는 조인

SELECT last_name, salary, grade_level
FROM employees e, job_grades g
WHERE e.salary BETWEEN g.lowest_sal AND g.highest_sal;

SELECT last_name, salary, department_name, grade_level
FROM employees e, departments d, job_grades g
WHERE e.department_id = d.department_id
AND e.salary BETWEEN g.lowest_sal AND g.highest_sal;

 

 -cartesian Product

=>cartesian product는 조인할 때 일치하는 데이터만 반환하지 않고 조인하는

     각 테이블의 행 개수를 서로 곱한 결과가 반환

=>유효한 데이터로 사용되지 못하며 일반적으로 조인 조건이 생략된 경우에 발생

 

 -self 조인

=>하나의 테이블만 사용하여 자기 자신을 조인

--사원과 담당 관리자의 이름을 출력
SELECT e.last_name 사원명, m.last_name 관리자명
FROM employees e, employees m
WHERE e.manager_id = m.employee_id;

 -Outer 조인

=>Outer 조인은 조인 조건에 만족하지 않아도 결과 값에 포함시키는 조인 방법으로 (+) 연산자를 사용

=>(+) 연산자는 조인하고자 하는 테이블 중에서 한번만 사용 할 수 있으며

     일치하는 데이터가 없는 쪽에 지정

=>(+)연산자를 지정하면 내부적으로 한 개 이 상의 널(null)을 가진 행이 생성

=>생성된 널(null)행들과 데이터를 가진 테이블의 행들이

     조인하게 되어 조건이 일치하지 않아도 결과 값에 포함

/*
문법
SELECT 테이블1.컬럼 , 테이블2.컬럼
FROM 테이블1 , 테이블2
WHERE 테이블1.공통컬럼 = 테이블2.공통컬럼 (+);

테이블2에 (+)를 지정했기 때문에 조인조건과 일치하는 않는
테이블1의 데이터도 결과에 포함되어 출력된다. 
만약 테이블1에 (+)를 지정하면 조인조건과 일치하지 않는
테이블2의 데이터가 결과에 포함되어 출력된다. 
양쪽 테이블에 (+) 연산자를 동시에 사용할 수는 
없으며 반드시 하나의 테이블에만 지정해야 된다.
*/

--관리자가 없는 사원 정보도 모두 포함하여 사원의 이름과
--담당 관리자의 사원번호를 조회
SELECT e.last_name 사원명, m.last_name 관리자명
FROM employees e, employees m
WHERE e.manager_id = m.employee_id(+);

---

다.ANSI 조인

=>ANSI 조인은 MS-SQL,DB2 같은 오라클 DBMS가 아닌 환경에서도 사용 가능한 표준화된 조인을 의미

 

 -Natural 조인

=>오라클 조인의 Equi 조인과 기능이 동일

=>조인에 참여하는 테이블에는 반드시 한 개의 공통 컬럼이 있어야함

=>두 개 이상의 공통 컬럼이 있다면 오류는 아니지만 엉뚱한 실행결과가 출력

/*
문법
SELECT 테이블1.컬럼 , 테이블2.컬럼
FROM 테이블1 NATURAL JOIN 테이블2
[WHERE 검색조건];
*/

SELECT last_name,department_name, department_id
FROM employees NATURAL JOIN departments;

/*
--공통컬럼에는 별칭사용 불가
SELECT last_name,department_name, e.department_id
FROM employees e NATURAL JOIN departments d;
*/

--검색조건 추가
SELECT last_name,department_name, department_id
FROM employees e NATURAL JOIN departments d --From 절에 조인조건 명시
WHERE department_id=90;                     --Where 절에 검색조건 명시

 

 -Cross 조인

=>조인할 때 일치하는 데이터만 반환하지 않고 조인하는 각 테이블의 행 개수를 서로 곱한 결과가 반환

=>오라클 조인의 cartesian Product와 동일하게 동작

=>데이터로서의 가치 없음

SELECT last_name,department_name, e.department_id
FROM employees e CROSS JOIN departments d;

 

 

 -USING(컬럼) 절

=>Natural 조인 사용시 만약 두 개 이상의 공통 컬럼이 있다면 오류는 아니지만 엉뚱한 실행 결과 출력

=>using 절을 사용하여 명시적으로 어떤 컬럼으로 조인할지를 지정

/*
문법
SELECT 테이블1.컬럼 , 테이블2.컬럼
FROM 테이블1 [INNER] JOIN 테이블2 USING(공통컬럼)
[WHERE 검색조건];
*/

--공통 컬럼에 별칭사용 불가
SELECT last_name,department_name, department_id
FROM employees e JOIN departments d USING(department_id);
/* = */
SELECT last_name,department_name, department_id
FROM employees e INNER JOIN departments d USING(department_id);

 

 -ON 절

=>Natural 조인이나 USING 절은 조인할 때 내부적으로 공통 컬럼값이 반드시 일치하는 Equi 조인 형식으로 실행

=>Non-Equi 조인이나 임의의 조건으로 조인할 경우에는 ON 절을 사용

/*
문법
SELECT 테이블1.컬럼 , 테이블2.컬럼
FROM 테이블1 [INNER] JOIN 테이블2 ON 조인조건
[WHERE 검색조건];
*/

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

--self 조인
SELECT e.last_name 사원명, m.last_name 관리자
FROM employees e INNER JOIN employees m
ON e.manager_id = m.employee_id;

--3개 테이블 조인
SELECT e.last_name 사원명, d.department_name 부서명, g.grade_level 등급
FROM employees e INNER JOIN departments d
ON e.department_id = d.department_id
INNER JOIN job_grades g
ON e.salary BETWEEN g.lowest_sal AND g.highest_sal;

 

 -LEFT OUTER | RIGHT OUTER | FULL OUTER 조인

/*
문법
SELECT 테이블1.컬럼 , 테이블2.컬럼
FROM 테이블1 LEFT|RIGHT|FULL OUTER JOIN 테이블2
ON 조인조건 | USING(컬럼)
[WHERE 검색조건];
*/

/*
LEFT OUTER 조인
->LEFT로 지정된 테이블1의 데이터를 테이블2의 조인조건 일치 여부와 상관없이 모두 출력
RIGHT OUTER 조인
->RIGHT로 지정된 테이블2의 데이터를 테이블1의 조인조건 일치 여부와 상관없이 모두 출력
FULL OUTER 조인
->LEFT OUTER 조인 결과와 RIGHT OUTER 조인 결과를 합친 결과
  양쪽 테이블1과 테이블2의 데이터를 조인 조건 일치여부와 상관없이 모두 출력
*/

SELECT e.last_name 사원명, m.last_name 관리자명
FROM employees e LEFT OUTER JOIN employees m
ON e.manager_id = m.employee_id;