[Oracle] join, view, subquery

JOIN
-두개 이상의 테이블에 나눠져 있는 관련 데이터들을 하나의 테이블로 모아서 조회하고자 할 때 사용하는 명령

 

1. cross join
-두개 이상의 테이블이 조인될 때 where 절에 의해 공통되는 컬럼에 의한 결합이 발생하지 않는 경우

create table testA(
	fieldA1 varchar2(10),
	fieldA2 varchar2(10),
	fieldA3 varchar2(10)
);
insert into testA values('value11','value12','value13');
insert into testA values('value21','value22','value23');
insert into testA values('value31','value32','value33');

create table testB(
	fieldB1 number(10),
	fieldB2 number(10)
);
insert into testB values(11,12);
insert into testB values(21,22);

select*from testA; --레코드 3, 필드3
select*from testB; --레코드 2, 필드2
-- 크로스 조인결과
select*from testA, testB; --레코드 6, 필드 5

 

2. equi join
-조인 대상이 되는 두테이블에서 공통적으로 존재하는 컬럼의 값이 일치하는 행을 연결하여 결과 생성

select*from dept --레코드4, 필드3
select*from emp --레코드14, 필드8
select*from emp, dept --레코드56, 필드11

-- 크로스 조인으로 조합된 52개의 레코드 중 
-- emp의 deptno와 detp의 deptno가 같은 것만 골라내어 13개를 골라냄
select*from emp, dept where emp.deptno=dept.deptno;
-- 실행의 결과는 해당 사원의 부서번호에 따른 부서명과 지역이 
-- 해당 사원 옆에 나란히 위치해서 표시됨

-- 각 사원의 이름, 부서번호, 부서명, 지역만을 출력
select emp.ename, emp.deptno, dept.dname, dept.loc from emp, dept 
where emp.deptno=dept.deptno;
-- select와 from 사이에 필요한 필드만 표시하려한다면 
-- 위와 같이 필드명 뿐만 아니라 
-- 그 필드의 소속 테이블 이름을 '.' 점으로 구분해서 이어줘야 
-- 정확히 어느 테이블의 필드를 표시할지가 결정됨.
-- 물론 이름만으로 그것이 구분된다면 
-- 그 필드는 테이블 이름을 안써도 무방함. 
-- 그러나 deptno 같이 두 테이블에 동일한 이름으로 존재하는 필드는 
-- 반드시 테이블명까지 같이 써줘야 함.

-- 이름이 SCOTT인 사원의 이름, 부서번호, 부서명, 위치 출력
select emp.ename, emp.deptno, dept.dname, dept.loc from emp, dept 
where emp.deptno=dept.deptno and emp.ename='SCOTT';

-- rentlist의 bnum에 해당하는 도서제목을 
-- rentlist의 rentdate, num, bnum과 함께 출력하세요
select rentlist.rentdate, rentlist.num, rentlist.bnum, 
booklist.subject 
from rentlist, booklist 
where rentlist.bnum=booklist.num;

-- rentlist의 mnum에 해당하는회원의 이름과 전화번호를 
-- rentlist의 num, rentdate, mnum과 함께 출력
select memberlist.name, memberlist.phone, rentlist.num, 
rentlist.rentdate, rentlist.mnum
from rentlist, memberlist 
where rentlist.mnum=memberlist.num;

-- 테이블명에 별칭을 부여한 후 컬럼 앞에 소속테이블을 저장
-- 테이블명으로 소속을 기술할 대는 한쪽만 있는 필드에 생략이 가능하지만 
-- 아래와 같이 별칭 부여시에는 모든 필드 앞에 반드시 별칭을 기술해야함
select a.ename, b.dname, b.loc, a.deptno
from emp a, dept b
where a.deptno=b.deptno and a.name='SCOTT';
select a.num, a.rentdate, a.mnum, b.name, b.phone
from rentlist a, memberlist b
where a.mnum=b.num;

 

3. non-equi join
-동일 컬럼이 없어서 다른 조건을 사용하여 조인
-조인 조건에 특정 범위 내에 있는지를 조사하기 위해 조건절에 조인 조건을 '=' 연산자 이외의 비교

-- 연산자를 이용
select a.ename, a.sal, b.grade from emp a, salgrade b
where a.sal>=b.losal and a.sal<=b.hisal;

-- 비교 명령을 이용
select a.ename, a.sal, b.grade from emp a, salgrade b
where a.sal between b.losal and b.hisal;

 

4. 세 개의 테이블을 하나로 join(equi, nonequi 조인의 조합)

select a.ename, a.sal, c.grade, b.dname
from emp a, dept b, salgrade c
where a.deptno=b.deptno and a.sal between c.losal and c.hisal;

-- 연습 문제

-- rentlist 테이블의 rentdate, bnum, mnum을 조회하되
-- booklist와 memberlist 테이블을 조인해서 
-- 책제목과 대여가격, 회원이름과 사은포인트를 출력
-- 출력순서: 대여일자, 도서번호, 도서제목, 회원번호, 회원이름, 포인트
-- 테이블의 별칭은 a, b, c
select a.rentdate as "대여일자", a.bnum as "도서번호", 
       b.subject as "도서 제목", a.mnum as "회원번호", 
       c.name as "회원이름", c.bpoint as "사은포인트"
from rentlist a, booklist b, memberlist c
where a.bnum=b.num and a.mnum=c.num;

-- 조인된 테이블에 계산식으로 필드를 생성할수도 있음
select a.rentdate as "대여일자", a.bnum as "도서번호", 
       b.subject as "도서 제목", a.mnum as "회원번호",
       c.name as "회원이름", c.bpoint as "사은포인트",
	   b.rentprice-a.discount as "할인대여료"
from rentlist a, booklist b, memberlist c
where a.bnum=b.num and a.mnum=c.num;

insert into rentlist values(sysdate, rent_seq.nextVal, 2, 4, 200);

select to_char(a.rentdate,'YYYY-MM-DD') as "대여일자", 
       a.bnum as "도서번호", b.subject as "도서 제목", 
	   a.mnum as "회원번호", c.name as "회원이름", 
       c.bpoint as "사은포인트", b.rentprice-a.discount as "할인대여료"
from rentlist a, booklist b, memberlist c
where a.bnum=b.num and a.mnum=c.num
order by a.num desc;

delete from rentlist where num in(11,13);

 

5. outer join
-조인 조건에 만족하지 못해서 해당 결과를 출력시에 누락이 되는 문제점이 발생할 때 해당 레코드를 출력하는 조인

select a.num, a.subject, b.rentdate
from booklist a, rentlist b
where a.num(+)=b.bnum;
-- booklist에는 도서코드가 있지만 한번도 대여한 적이 없어서 
-- rentlist에는 존재하지 않아서 null로 표시되는 경우
-- 보통 null로 표시될 필드에 (+) 표시를 넣음

 

6. ANSI join

1) ansi cross join

select*from emp, dept --일반크로스 조인 표현
select*from emp cross join dept 
--ansi cross join -> 일반 크로스 조인과 같은 효과

 

2) ansi inner join

--일반 equi 조인 표현 방식
select ename, dname from emp a, dept b where a.deptno=b.deptno

-- ansi 이너 조인의 표현 방식
select ename, dname
from emp inner join dept 
on emp.deptno=dept.deptno;

-- ansi 이너 조인의 다른 표현 방식
-- 두 테이블의 조인 기준이 되는 필드명이 똑같을 때만 사용가능
select ename, dname
from emp inner join dept
using (deptno);

 

3) ansi outer join

-기존 아우터 조인의 표현방식
select*from emp, dept where emp.deptno=dept.deptno(+);
select*from emp, dept where emp.deptno(+)=dept.deptno;

-- ansi outer join 표현방식
select*from emp left outer join dept on emp.deptno=dept.deptno;
select*from emp right outer join dept on emp.deptno=dept.deptno;
-- 기준이 되는 필드명 중 A테이블의 필드에는 있으나
-- B테이블 필드에는 해당값이 없는 경우에 대한 표현 여부 결정

 

---

VIEW

-물리적인 테이블에 근거한 논리적인 가상 테이블
-주로 조인 명령에 의해 만들어지는 가상테이블이 view 생성의 대상이 되며, 만들어진 테이블을 뷰로 저장하는 것이 아니라 조인 명령을 저장하고 있다가 테이블을 조회하듯 select로 명령의 결과 테이블을 조회함

 

뷰 생성 방법

create or replace view 뷰이름 as select 조회명령

-- 결과-> select의 결과를 테이블로 내어 놓는 
--        가상테이블 제작 명령이 생기는 셈
-- 뷰이름으로 조회명령을 저장하고 있다가 
-- 뷰이름으로 조회할 때마다 조회 명령이 실행되어 결과를 내놓음

create or replace view rentjointable as
-- 명령의 첫마디가 creat or replace 라는건, 
-- 같은 뷰이름으로 또다른 뷰를 만들면 기존 명령은 사라지고
-- 새로운 명령으로 기존 이름의 뷰가 대체됨
select a.rentdate as "대여 일자", b.subject as "도서 제목", 
       c.name as "회원 성명", c.bpoint as "사은포인트",
       b.rentprice-a.discount as "할인대여료"
from rentlist a, booklist b, memberlist c
where a.bnum=b.num and a.mnum=c.num;

 

 

-- emp 테이블과 dept 테이블에서 
-- 번호, 이름, 직책, 입사말, 부서번호, 부서이름, 지역을 출력
create or replace view result_inner_join as
select a.empno, a.ename, a.job, a.deptno, b.dname, b.loc
from emp a, dept b
where a.deptno=b.deptno;

 

---

SUBQUERY

-하나의 select 문장의 절 안에 포함된 또 하나의 select 쿼리문
-주로 from 뒤에 대상 테이블이 서브쿼리로 작성하거나 where절에서 비교값의 대상을 테이블 또는 값으로 얻어낼 때 사용함

 

 

SOCTT이 근무하는 곳의 부서명과 지역 출력

1. 기존 사용

-- 단일행 서브쿼리: 서브쿼리의 결과가 하나인 것을 다른 쿼리의 결과로 사용

-- 두개의 select문 사용
select deptno from emp where ename='SCOTT'; -- 결과 20
select dname, loc from dept where deptno=20;

-- join을 사용했을 때
select b.dname, b.loc from emp a, dept b 
where a.deptno=b.deptno and a.ename='SCOTT';

2. subquery 사용

select dname, loc from dept 
where deptno=(select deptno from emp where ename='SCOTT');

 

subquery 사용 연습하기

-- SCOTT과 동일직 업(job)을 가진 사원의 모든 정보 출력
	-- 최종 출력에 어떤 필드가 나오느냐
	-- 대상테이블은 어떤 테이블이냐
	-- 조건은 어떤 조건을 갖고 있나
select*from emp 
where job=(select job from emp where ename='SCOTT');

-- SCOTT과 급여가 동일하거나 더 많이 받는 사원이름과 급여 출력
	-- 최종 출력에 어떤 필드가 나오느냐
	-- 대상테이블은 어떤 테이블이냐
	-- 조건은 어떤 조건을 갖고 있나
select ename, sal from emp 
where sal>=(select sal from emp where ename='SCOTT');

 

 

 

subquery & group function

-- 전체 사원 평균 급여보다 더 많은 급여를 받는 사람의 이름, 급여, job
select ename, sal, job from emp 
where sal>(select avg(sal) from emp);

 

 

subquery & in, some, ani, all 등

-- 급여를 2900이상 받는 사원이 소속된 부서와 
-- 소속된 부서에서 근무하는 사원들의 이름, 부서번호, job
-- 서브쿼리에 들어갈 내용: 2900이상 급여를 받는 사원이 소속된 부서번호
select ename, deptno, job from emp 
where deptno in(select distinct deptno from emp where sal>=2900 );

-- 30번 부서 소속 사원들 중에서 급여를 가장 많이 받는 사원보다 
-- 급여가 더 많은 사원의 이름과 job, 급여
	-- 출력: 이름, job, 급여
	-- 대상테이블: emp
	-- 조건: 30번 부서 소속 사원들 중에서 
    --       급여를 가장 많이 받는 사원의 급여보다 많음
	-- 서브쿼리1: select max(sal) from emp where deptno=30;
select ename, job, sal from emp 
where sal>(select max(sal) from emp where deptno=30);
	-- 서브쿼리2: all(select sal from emp where deptno=30)
select ename, job, sal from emp 
where sal>all(select sal from emp where deptno=30);	

-- 30번 부서 소속 사원들 중에서 급여를 가장 적게 받는 사원보다 
-- 높은 급여를 받는 사원의 이름과 job, 급여
-- 방법 1
select ename, job, sal from emp 
where sal>(select min(sal) from emp where deptno=30);
-- 방법 2
select ename, job, sal from emp 
where sal>any(select sal from emp where deptno=30);

-- 영업 사원(job='SALESMAN')들의 최소 급여보다 많이 받는 사원들의
-- 이름과 급여, 직급을 출력하되 영원사원은 출력하지 않음
-- 방법 1
select ename, sal, job from emp 
where sal>(select min(sal) from emp where job='SALESMAN') 
and job<>'SALESMAN';
-- 방법 2
select ename, sal, job from emp 
where sal>any(select sal from emp where job='SALESMAN') 
and job<>'SALESMAN';