2022-11-04 조인, 서브쿼리

Join

 

---

- 관계형 데이터베이스에서  원하는 결과를 여러 테이블로부터 가져오고 싶은 경우 즉, 여러 테이블을 이용해서
  원하는 결과를 만들어내는 것을 조인이라 한다.

- 조인은 어떤 테이블을 기준으로 다른 테이블에 있는 레코드의 값을 참조하는 것을 조인이라 한다.

- 기본키 테이블과 외래키 테이블 관계를 이용해서 조인해야 한다.
  ------------------     -----------------
   ㄴ어떤 정보를                  ㄴ기본키테이블에 정리된
   중복없이 정리                     컬럼의 값을 참조해서
   해둔 테이블                         사용하는 테이블  

 

 

 

조인방법

 

- 반드시 연관성있는 테이블끼리 조인해야 한다.

- 조인조건을 정의해야 한다.

- where 기본키테이블명.컬럼명 = 외래키테이블명. 컬럼명 (테이블 이름. 열이름)
  => where emp.DEPTNO = dept.DEPTNO;  // 중복된 결과만 가지고 오겠다는 뜻

- 테이블에 alias정의하고 모든 컬럼은 alias를 통해 접근한다.

- select절에서 두 개 이상의 테이블의 값을 사용하고 있지 않아도 where절에서 사용하고 있으면
  조인조건을 명시해야 한다.

- 두 개 이상의 테이블을 조인하는 경우 and로 조인조건을 정의한다. 
select e.deptno, e.ename, d.dname
from emp e, dept d  (from절이 가장 먼저 실행되기 때문에 select, where절에서 명령어 사용가능하다.)
where e.deptno = d.deptno;

 

 

 

 

조인 종류

 

- 등가 조인 : 기본키와 외래키가 정확하게 일치함
- 비등가 조인 

- 외부조인

1) 왼쪽 외부 조인 : 오른쪽 내용이 없어도 왼쪽은 출력시킬 것

2) 오른쪽 외부 조인 : 왼쪽 내용이 없어도 오른쪽은 출력시킬 것

 

 

1. 등가 조인

select e.ename, d.dname, l.city
from emp e, dept d, locations l (테이블 3개)
where e.deptno = d.deptno
and d.loc_code = l.loc_code;

select e.ename, e.sal, e.job, e.hiredate, e.comm
from emp e, dept d, locations l
where e.deptno = d.deptno and d.loc_code = l.loc_code and
l.city = 'DALLAS' and e.sal >= 1500;

select d.deptno, d.dname, l.city
from dept d, locations l
where  d.loc_code = l.loc_code;

select j.job_title as JOB, sum(e.salary) as 급여
from employees e, jobs j
where e.job_id=j.job_id and j.job_title not like '%Representative%'

 

 

<블로그 과제>

(1)

select  d.DEPARTMENT_name, count(e.employee_id)
from employees e, DEPARTMENTS d
where e.DEPARTMENT_ID = d.DEPARTMENT_ID
group by d.DEPARTMENT_name;

 

(2)

select e.first_name || '' || e.last_name || '의 연봉은 ' || e.salary || '입니다.' as 결과
  from employees e, DEPARTMENTS d
  where e.DEPARTMENT_ID = d.DEPARTMENT_ID and  DEPARTMENT_NAME = 'IT'
  order by  e.salary;

위의 문제를 concat을 연습하기 위해 이용해서 해보았다.

select concat(concat(concat(concat(e.first_name, ' '), e.last_name),'의 연봉은 '), 
concat(e.salary, ' 입니다.'))
from employees e, DEPARTMENTS d
where e.DEPARTMENT_ID = d.DEPARTMENT_ID and  DEPARTMENT_NAME = 'IT'
order by  e.salary;

CONCAT(CONCAT(CONCAT(CONCAT(E.FIRST_NAME,''),E.LAST_NAME),'의연봉은'),CONCAT(E.SALARY,'입니다.'))
-------------------------------------------------------------------------------------------
Diana Lorentz의 연봉은 4200 입니다.
David Austin의 연봉은 4800 입니다.
Valli Pataballa의 연봉은 4800 입니다.
Bruce Ernst의 연봉은 6000 입니다.
Alexander Hunold의 연봉은 9000 입니다.

 

(3)

select e.employee_id, e.first_name, j.job_title, d.department_name
from employees e, DEPARTMENTS d, jobs j, locations l
where d.location_id = l.location_id and  e.DEPARTMENT_ID = d.DEPARTMENT_ID and e.job_id=j.job_id // join은 컬럼-1 개
and l.CITY = 'Seattle';

* 위의 문제에서 주의할 점!!

사용하는 테이블의 갯수가 4개이기 때문에 where절에 n-1개의 조인을 작성해줘야 함

 

 

<블로그 과제2>

(1)

select j.job_title as JOB, sum(e.salary) as 급여
from employees e, jobs j
where e.job_id=j.job_id and j.job_title not like '%Representative%'
group by j.job_title
having sum(e.salary)>30000
order by sum(e.salary);

* 위의 문제에서 주의할 점!!

 j.job_title<> 'Sales Representative'  이렇게 입력해도 같은 결과가 나오지만, 데이터에 Representative인 데이터가 없고 XXXXRepresentative이기 때문에 Representative가 들어가는 것을 제외하도록 명령문을 써줘야 한다.

+ select에서 as 급여 안 쓰고 "order by 급여"로 해도 됨

 

(2) 

select d.department_name, count(e.employee_id)
from employees e, DEPARTMENTS d
where e.DEPARTMENT_ID = d.DEPARTMENT_ID and to_char(e.hire_date, 'yyyy') < 2005
group by d.department_name;

 

(3) 복잡한 문제

select d.department_id 부서번호, d.department_name 부서명, count(e.employee_id) 인원수,
max(e.salary) 최고급여, min(e.salary) 최저급여, trunc(avg(e.salary),0) 평균급여, sum(e.salary) 급여총액
from employees e, DEPARTMENTS d
where e.DEPARTMENT_ID = d.DEPARTMENT_ID
group by d.department_name, d.department_id
having count(e.employee_id)>=3
order by  count(e.employee_id) desc;

ㄴ> 노트에 어떤 조건들이 있는지 정리해놓고 작성하기. 훨 쉬워진다.

 

(4) 프로그래머스 select 문제

SELECT i.FLAVOR
from ICECREAM_INFO i, FIRST_HALF f
where i.FLAVOR = f.FLAVOR and f.TOTAL_ORDER>3000 and i.INGREDIENT_TYPE = 'fruit_based'
order by f.TOTAL_ORDER desc

 

2. 외부 조인

<블로그과제>

부서별 근무하는 인원 수를 나타내도록 하세요.단, 사원이 없는 부서명도 같이 나타내도록 한다.

select  d.DEPARTMENT_name, count(e.employee_id)
from employees e, DEPARTMENTS d
where e.DEPARTMENT_ID(+) = d.DEPARTMENT_ID 
group by d.DEPARTMENT_name
order by d.DEPARTMENT_name;


DEPARTMENT_NAME                                              COUNT(E.EMPLOYEE_ID)
------------------------------------------------------------ --------------------
Accounting                                                                      2
Administration                                                                  1
Benefits                                                                        0
Construction                                                                    0
Contracting                                                                     0

=> 왼쪽에 부서 이름이, 오른쪽에 사원의 수가 출력된다. 문제에서 "사원이 없는 부서명도 나타낸다" 라는 조건이 있기 때문에 오른쪽 데이터가 null값이라는 것을 유추할 수 있다. 오른쪽 내용이 없어도 왼쪽 내용은 출력시키는 왼쪽 외부 조인을 사용하여 해결하도록 한다.

 

 

 

 

 

 

 

서브쿼리

 

---

메인쿼리  
      (   서브쿼리   )

- 다른 select문에 삽입된 select문
- 서브쿼리는 괄호로 묶어야 한다.
- 비교를 위해 사용하는 연산자 오른쪽에 정의한다.
- 서브쿼리에서는 order by하지 않는다(top-N쿼리 제외).
- 서브쿼리가 먼저 실행되고 그 결과를 메인쿼리에서 사용

 

 

 

서브쿼리 종류

 

1. 단일행 서브쿼리
- 서브쿼리의 실행결과가 1행 1열인 쿼리
- = > < >= <= != <> 연산자를 이용해서 작업

2. 다중행 서브쿼리
- 서브쿼리의 결과가 다중행인 경우
- 비교연산자 사용할 수 없다.
- in, any, all
* 컬럼 in (값1, 값2...) : or연산
* 컬럼 < any - 최대값보다 작은 값을 조회
select ename, sal
from emp
where sal < any(800, 1250, 1600)   // 세 개 중 하나만 true이면 true반환
* 컬럼 > any - 최소값보다 큰 값을 조회
select ename, sal
from emp
where sal < any(800, 1250, 1600)
* 컬럼 < all - 최소값보다 작은 값을 조회
select ename, sal
from emp
where sal < all(900, 1250, 1600);
* 컬럼 > all - 최대값보다 큰 값을 조회

3. 다중컬럼 서브쿼리

4. 상호연관 서브쿼리

5. top-N쿼리

 

- 사원들의 급여의 평균보다 많이 받는 사원의 정보를 출력하기

select *
from emp
where sal> (select avg(sal)
            from emp);

 

 

1. 단일행 서브쿼리

<블로그 과제>

(1) 이름에 'T'가 들어가는 사원의 부서 번호와 같은 부서에 해당하는 사원의 이름과 사원번호 출력하기

select ename, empno
from emp
where deptno in (select deptno
                 from emp
                 where ename like '%T%');

* 위의 문제에서 주의할 점!!

 

서브쿼리의 결과가 1개 이상이 나오기 때문에  '=' 사용하면 "ORA-01427: single row subquery returns more than one          row" 라는 에러가 발생한다. 단일행 서브쿼리이기 때문에 여러 개의 결과와 비교할 수 없다.

 

(2) 부서 번호가 20인 사원들의 최고급여보다 더 높은 급여를 받는 사원의 이름과 부서 번호, 급여를 출력하기

select ename, deptno, sal
from emp
where sal> (select max(sal)
            from emp
            where deptno = 20);

 

(3)

select ename, hiredate,sal
from emp
where sal > (select avg(sal)
             from emp
             where hiredate between '1982/1/1' and '1982/12/31');

ㄴ> to_char(hiredate,'yyyy')='1982'); 이것보다는 위의 방식으로 하자.

 

 

2. 다중행 서브쿼리

 select ename, sal
 from emp
 where sal < all(900, 1250, 1600);

 select ename, sal
 from emp
 where sal > any(800, 1250, 1600);