231031 (Oracle)

조인

조인의 필요성

• 여러 테이블에 걸친 데이터를 한번에 조회하기 위해서 조인 기술이 필요하다.

조인

• 여러 테이블에 흩어져 있는 정보 중에서 사용자가 필요한 정보만 가져와서 가상의 테이블처럼 나들어서 결과를 보여준다.

cartesian product

• 두개 이상의 테이블이 조인될 때 where절에 공동되는 컬럼에 의한 조인이 발생되지 않아 모든 데이터가 검색 결과로 나타나는 경우
• 테이블에 존재하는 모든 데이터가 검색 결과로 나타난다.
• 단순히 from을 나열할 경우 중복 데이터의 처리가 이루어지지 않는다.

equi join

• 조인 대상이 되는 두 테이블에서 공통적으로 존재하는 컬럼의 값이 일치되는 행을 연결하여 결과를 보여준다.
• 연결 방법은 where 테이블.일치하는 컬럼 = 테이블 . 일치하는 컬럼
• 조인된 테이블의 특정 컬럼을 보여줄 수 있다. 중복되지 않은 컬럼의 경우 컬럼명만 사용하여도 무관하지만 중복되는 컬럼의 경우 테이블.컬럼으로 작성하여야 오류가 발생하지 않는다.
• 조인할 때 별칭을 부여할 경우 별칭.컬럼으로도 접근할 수 있다.

non-equi join

• 동일 컬럼이 없이 다른 조건을 사용하여 조인할 때 사용된다.
• 보여주고자 하는 조건에 해당하는 레코드의 셀렉트 요소를 출력한다.
• 조건으로 받아오는 테이블의 각 레코드마다 보여주고자 하는 테이블의 요소가 출력된다.
• equi join과 non-equi join을 섞어서 여러가지 조인을 사용할 수 있다.

self join

• 같은 테이블의 있는 정보를 비교하여 결과를 출력할 경우 셀프조인이 필요하다.
• 셀프조인은 from에 같은 테이블을 2번 적고, 별칭을 다르게 설정하여 조인할 수 있다.

outer join

• 셀프조인에서 조건 밖에 있는 레코드도 출력하기 위한 방법이다.
• (+)를 사용하여 조인한다. 해당 테이블의 조건이 맞지 않고, 다른 테이블의 조건이 맞을 경우 출력하고 싶을 때 사용한다.
• (+)를 붙인 반대쪽은 (+)를 붙인 쪽에 해당하는 요소가 없더라도 출력된다.

서브쿼리

• select 내부에 select가 들어있는 쿼리문이다.
• 바깥의 쿼리를 메인 쿼리, 내부의 쿼리를 서브쿼리라고 한다.
• 비교연산자의 우측에 기술해야하고, 반드시 괄호 안에 넣어야 한다.
• 메인쿼리가 실행되기 이전에 한번만 실행된다.
• 서브쿼리에 그룹함수를 사용한 결과를 사용할 수 있다.

조인 문제

1. employees 와 departments 테이블을 조인하여 사원명과 부서명을 출력

select last_name, department_name from employees e, departments d where e.department_id=d.department_id;

1. employees 와 departments 테이블을 조인하여 이름이 whalen 인 사원 정보 출력.

select last_name, department_name from employees e, departments d where e.department_id=d.department_id and e.last_name='Whalen';

1. employees 와 departments 테이블을 조인하여 부서별로 2005년 이전 입사 사원의 인원수 출 력

select count(d.department_name), d. department_name from employees e, departments d where e.department_id=d.department_id and e.hire_date<to_date(20160101, 'yyyymmdd') group by d.department_name;

1. employees 와 departments, jobs 를 조인하여 last_name, salary, department_name, job_title 출력.

select e.last_name, e.salary, d.department_name, j.job_title from employees e, departments d, jobs j where e.department_id=d.department_id and e.job_id=j.job_id;

1. employees 에서 사원별 담당 관리자 출력.

select e.last_name ||'의 매니저는 '||m.last_name from employees e, employees m where e.manager_id = m.employee_id;

1. employees 에서 담당 관리자가 없는 사원도 포함하여 사원별 담당관리자 출력.

select e.last_name ||'의 매니저는 '||m.last_name from employees e, employees m where e.manager_id = m.employee_id(+);

1. employees 에서 사원명과 관리자명, 그리고 관리자의 관리자명을 출력.

select e.last_name ||'의 매니저 '||m.last_name||'의 매니저는 '||mm.last_name from employees e, employees m, employees mm where e.manager_id = m.employee_id and m.manager_id = mm.employee_id;

1. employees 와 departments 테이블을 조인하여 department_id 가 90인 사원의 last_name, department_name, department_id 출력

select e.last_name, d.department_name, d.department_id from employees e, departments d where e.department_id=d.department_id and d.department_id=90;

1. employees 에서 사원들의 평균 월급보다 많은 월급을 받는 사원 출력.

select last_name, salary from employees where salary>(select avg(salary) from employees);

1. employees 에서 부서번호가 100인 사원들 중에서 최대 월급을 받는 사원과 동일한 월급을 받 는 사원을 조회
   1. 부서번호 100에서 최고 월급과 동일한 부서번호 상관 없는 사원을 출력함

select last_name, salary, employees.department_id from employees where salary=(select max(salary) from employees where department_id = 100);

1. employees 에서 100번 부서의 최대 월급보다 많은 모든 부서 정보 출력

select department_id, max(salary) from employees GROUP BY department_id having max(salary)>(select max(salary) from employees where department_id=100);

1. employees 에서 whalen 사원보다 늦게 입사한 사원 정보 출력.

select last_name, hire_date from employees where hire_date>(select hire_date from employees where last_name='Whalen');

1. employees 에서 이름이 whalen 또는 fay 인 사원과 같은 월급을 받는 모든 사원들의 정보 출력

select last_name, salary from employees where salary in ((SELECT salary from employees where last_name in ('Whalen', 'Sully')));

1. employees 에서 월급이 13000 이상 받는 사원이 소속된 부서와 동일한 부서에서 근무하는 모든 사원 정보 출력

select last_name, salary, department_id from employees where department_id in ((select DISTINCT department_id from employees where salary>13000));

1. employees 에서 직업이 IT_prog 인 사원의 최대 월급보다 많은 월급을 받는 사원들의 정보 출력

select last_name, salary from employees where salary>(select max(salary) from employees where job_id='IT_PROG');