-
[Oracle] join, view, subqueryDataBase/Oracle 2022. 9. 9. 15:39
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';
'DataBase > Oracle' 카테고리의 다른 글
[Oracle] 문자열함수, 수식처리, 날짜처리, 그룹함수 (0) 2022.09.09 [Oracle] DML (0) 2022.09.09 [Oracle] DDL (0) 2022.09.09 [Oracle] DB(database), SQL, 오라클자료형 (0) 2022.09.09 [Oracle] 설치 (0) 2022.09.09