230612 day46 개념 정리

조인 
두 개 이상의 테이블을 연결하여 하나의 테이블처럼 출력할 때 사용 

1. 여러 테이블 사용시 from절 

from member, product
select * from emp, dept;

2. 조인 조건을 지정 

select * from emp, dept
where emp.deptno = dept.deptno;

(테이블도 별칭 주기 가능)
select * from emp e, dept d
where e.deptno = d.deptno;

select e.empno, e.ename, e.deptno, d.dname, d.loc
from emp e, dept d
where e.deptno = d.deptno;

3. 조인 종류 
등가조인, 비등가조인, 자체조인, 외부조인 

1) 등가조인(내부조인, 단순조인)

테이블을 연결한 후 출력 행을 각 테이블의 특정 열에 
일치한 데이터를 기준으로 선정하는 방식 
ex> 
select * from emp e, dept d
where e.deptno = d.deptno;

ex>
select e.empno, e.ename, e.sal, d.deptno, d.dname, d.loc
from emp e, dept d
where e.deptno = d.deptno
and sal >= 3000; 

- emp 테이블 별칭을 e로 지정, dept 테이블 별칭을 d로 지정, 등가조인 실행 

급여가 2500 이하이고 사원 번호가 9999 이하인 
사원의 정보 (empno, ename, sal, deptno, dname, loc)가 
출력되도록 sql문을 작성하시오
select e.empno, e.ename, e.sal, d.deptno, d.dname, d.loc
from emp e, dept d
where e.deptno = d.deptno
and sal <= 2500 
and empno <=9999;


2) 비등가조인

등가조인 외의 방식을 의미 
특정 열의 일치 여부가 아닌 다른 방식을 사용하여 조인에 적용 

- 급여 범위를 지정하는 조건식으로 조인하기
select * from emp e, salgrade s
where e.sal between s.losal and s.hisal;


3) 자체 조인 

하나의 테이블을 여러 개의 테이블처럼 활용하여 조인하는 방식 

- 테이블을 복사하여 조인하기 
select e.empno, e.ename, e.mgr, c.empno as mgr_no, c.ename as mgr_name 
from emp e, copy_emp c
where e.mgr = c.empno; 

- 같은 테이블을 두번 사용해서 조인하기 
select e.empno, e.ename, e.mgr, c.empno as mgr_no, c.ename as mgr_name 
from emp e, emp c
where e.mgr = c.empno; 


4) 외부조인 
두 테이블 간 조인 수행에서 조인 기준 열의 어느 한 쪽이 
null이어도 강제로 출력하는 방식의 조인 

- 외부 조인은 좌우를 따로 나누어 지정하는데 where절에서 
조인 기준 열 중 한 쪽에 (+)기호를 붙임 

외부조인의 목적 : 조인 기준 열의 null을 처리하는 것 

왼쪽 외부조인 (left outer join)
where table1.col1 = table3.col2(+)

오른쪽 외부조인 (right outer join)
where table1.col1(+) = table3.col2


왼쪽 외부조인 예시 
select e1.empno, e1.ename, e1.mgr,
e2.empno as mgr_empno, e2.ename as mgr_name
from emp e1, emp e2
where e1.mgr = e2.empno(+)
order by e1.empno;

오른쪽 외부조인 예시 
select e1.empno, e1.ename, e1.mgr,
e2.empno as mgr_empno, e2.ename as mgr_name
from emp e1, emp e2
where e1.mgr(+) = e2.empno
order by e1.empno;


세 개 이상의 테이블 조인할 때 

select * from table1, table2, table3
// 조인 관련 조건식 = 테이블 갯수 - 1
where table1.col = table2.col
and table2.col = table3.col


239p 문제 풀이

1. 급여(sal)가 2000 초과인 사원들의 부서 정보, 
사원 정보를 출력하시오 

dept 테이블 d, emp 테이블 e 조인 

select d.deptno, d.dname, e.empno, e.ename, e.sal
from emp e, dept d 
where e.deptno = d.deptno
and e.sal > 2000
order by d.deptno;

sql-99 방식 
select deptno, d.dname, e.empno, e.ename, e.sal
from emp e natural join dept d
where e.sal > 2000;

join on 방식
select d.deptno, d.dname, e.empno, e.ename, e.sal
from emp e join dept d on (e.deptno = d.deptno)
where e.sal > 2000;


2. 각 부서별 평균 급여, 최대 급여, 최소 급여, 사원 수를 출력하시오

select d.deptno, d.dname, trunc(avg(e.sal)) as AVG_SAL,
max(e.sal) as MAX_SAL, min(e.sal) as MIN_SAL,
count(*) as cnt
from emp e, dept d
where e.deptno = d.deptno
group by d.deptno, d.dname;

sql-99 방식 
select deptno, d.dname, trunc(avg(e.sal)) as AVG_SAL,
max(e.sal) as MAX_SAL, min(e.sal) as MIN_SAL,
count(*) as cnt
from emp e join dept d using(deptno)
group by deptno, d.dname;

join-on 방식 
select d.deptno, d.dname, trunc(avg(e.sal)) as AVG_SAL,
max(e.sal) as MAX_SAL, min(e.sal) as MIN_SAL,
count(*) as cnt
from emp e join dept d on(d.deptno = e.deptno)
group by d.deptno, d.dname;


3. 모든 부서 정보와 사원 정보를 부서 정보, 사원 이름 순으로 
정렬해서 출력하시오

select d.deptno, d.dname, e.empno, e.ename, e.job, e.sal
from emp e, dept d
where e.deptno(+) = d.deptno 
order by d.deptno, e.ename;

sql-99 구문
select d.deptno, d.dname, e.empno, e.ename, e.job, e.sal
from emp e right outer join dept d on (e.deptno = p.deptno)
order by d.deptno, e.ename;


4. 모든 부서 정보, 사원 정보, 급여 등급 정보, 각 사원의 직속 상관 정보를 
부서 번호, 사원 번호 순서로 정렬해서 출력하시오

dept d, emp e1, emp e2, salgrader s

select d.deptno, d.dname, e1.empno, e1.ename, e.mgr, 
e.sal, e.deptno as deptno_1, s.losal, s.hisal, s.grade, 
e2.empno as mgr_empno, e2.ename as mgr_ename
from emp e1, dept d, salgrade s, emp e2
where e1.deptno(+) = d.deptno
and e1.sal between s.losal(+) and s.hisal(+)
and e1.mgr = e2.empno(+)
order by d.deptno, e.empno;

select d.deptno, d.dname, e.empno, e.ename, e.mrg, e.sal, e.deptno,
s.losal, s.hisal, s.grade,
e2.empno as mgr_empno, e2.ename as mgr_ename
from emp e right outer join dept d on (e.deptno = d.deptno)
left outer join salgrade s on (e.sal between s.losal and s.hisal)
left outer join emp e2 on (e.mgr = e2.empno);


SQL-99 표준 문법 
오라클 9i 버전부터 SQL-99 방식의 문법을 지원 
다른 데이터베이스 제품에서도 사용 가능 

1. natural join
등가조인 대신해서 사용 
조인 대상이 되는 테이블에서 이름과 자료형이 같은 열을 찾은 후 
그 열을 기준으로 등가조인하는 방식 
조인 기준열은 select절에 명시할 때 테이블 이름을 붙이면 안됨 

구문 >
select 열 이름 
from 테이블 이름 별칭 natural join 테이블 이름 별칭 

select e.empno, e.ename, e.job, deptno,
d.dname, d.loc
from emp e natural join dept d;


2. join~ using(조인에 사용할 기준 열 지정) 
등가조인을 대신하는 조인 방식 
조인 기준열로 명시된 열은 select절에서 테이블 이름을 붙이지 않음

구문 >
select 열 이름 
from table1 join table2 using(기준열)

select e.empno, e.ename, e.job, deptno,
d.dname, d.loc
from emp e join dept d using(deptno);


3. join~ on (가장 많이 쓰는 구문)
조인 기준 조건식을 on에 명시해줌 
(기준 열에도 테이블 이름 붙여줘야함)

구문 > 
from table1 join table2 on (조인 조건식)

예시 >
select e.empno, e.job, e.ename, e.deptno, d.dname, d.loc
from emp e join dept d on (e.deptno = d.deptno)


4. outer join
from 절에서 외부조인을 선언해줌

왼쪽 외부 조인 
where table1.col = table2.col(+)
from table1 left outer join table2 on (조인 조건식)

ex>
select e1.empno, e1.mgr,
e2.empno as mgr_empno, 
e2.ename as mgr_name
from emp e1 left outer join emp e2 on (e1.mrg = e2.empno)
order by e1.empno;


오른쪽 외부 조인 
where table1.col(+) = table2.col
from table1 right outer join table2 on (조인 조건식)


세 개 이상의 테이블 조인 시 구문 
from table1 join table on (조건식)
join table3 on (조건식)


서브쿼리 
SQL문을 실행하는데 필요한 데이터를 추가로 조회하기 위해서 
SQL문 내부에서 사용하는 select문을 의미함 

단일행 서브쿼리, 다중행 서브쿼리

서브쿼리 특징 
1. 서브쿼리는 연산자와 같은 비교 또는 조회 대상의 오른쪽에 놓이며 
()로 묶어서 사용함

2. 서브쿼리의 select절에 명시한 열은 메인쿼리의 비교 대상과 
같은 자료형과 같은 개수로 지정해야함 

구문 >
select * from 테이블 이름 
where 조건식 (select 조회할 열 from 테이블 이름)

사원 중 WARD의 급여보다 더 높은 사원 이름, 사원 번호, 급여, 직책을 출력

select ename, empno, sal, job
from emp
where sal > WARD의 급여(select sal from emp where ename = 'WARD');


서브쿼리를 사용하여 emp테이블 사원 정보 중 이름이 ALLEN인
사원의 추가수당보다 많이 받은 사원 정보(*)를 출력하시오

select * from emp 
where comm > (select comm from emp where ename = 'ALLEN' );

1) 단일행 서브쿼리 
실행결과가 단 하나의 행으로 나오는 서브쿼리

출력되는 결과가 하나이므로 메인쿼리와 서브쿼리 결과는 
단일행 연산자를 사용하여 비교 
단일행 연산자 : >, >=, <, <=, <>, ^=, !=

SCOTT보다 일찍 입사한 사원 목록 

select * from emp
where hiredate < (select hiredate from emp where ename = 'SCOTT');


20번 부서에 속한 사원 중 전체 사원의 평균 급여보다 
높은 급여를 받는 사원 정보, 소속 부서 정보를 조회 

select e.empno, e.ename, e.job, e.sal, d.deptno, d.dname, d.loc
from emp e, dept d
where e.deptno = d.deptno
and e.deptno = 20
and e.sal > (select avg(sal) from emp);

select e.empno, e.ename, e.job, e.sal, d.deptno, d.dname, d.loc
from emp e join dept d on (e.deptno = d.deptno)
where e.deptno = 20
and e.sal > (select avg(sal) from emp);


2) 다중행 서브쿼리
실행 결과 행이 여러개가 나오는 서브쿼리 

메인쿼리와 비교 시 단일행 연산자를 사용할 수 없고 
다중행 연산자를 사용해야 함 

- in 연산자 
메인쿼리 데이터가 서브쿼리의 결과 중 
하나라도 일치하는 데이터가 있다면 true

select * from emp 
where job in ('MANAGER', 'SALESMAN');

select * from emp 
where deptno in (10, 20);

각 부서별 최고 급여와 동일한 급여를 받는 사원 정보 출력하기 
(ex > 10번 1000 20번 2000 30번 3000)

select max(sal) from emp ---> 3000

select max(sal) from emp
group by deptno;

select * from emp
where sal in (select max(sal) from emp
group by deptno);


- any, some 연산자
메인쿼리의 조건식을 만족하는 서브쿼리의 결과가 하나 이상이면 true

- all 연산자
메인쿼리 조건식을 서브쿼리의 결과가 모두 만족하면 true

- exists 연산자 
서브쿼리의 결과가 존재하면 (즉, 행이 1개 이상일 때) true