===========
7장 Set 연산자
===========
# SET 연산자
-- 집합연산자
-- 두 집합 사이에 교집합, 합집합, 차집합 등
-- 집합 연산자를 넣어서 수행함.
# The number of columns and the data types of the columns being selected
must be identical in all the SELECT statements used in the query
선택될 컬럼의 개수와 데이터타입은 쿼리에 사용된 모든 SELECT구문(select [컬럼명] <- 여기까지)을 확실하게 해야한다.
select empno, ename, sal
from emp
union all
select employee_id, last_name||' '||first_name, salary
from employees;
--emp테이블의 전체 데이터와, employees테이블의 employee_id, salary는 나오고, last_name컬럼과 first_name을 스페이스바 하나를 두고 붙여서 한 컬럼으로 나타낸다.
--union된 전체테이블의 컬럼heading은 emp테이블과 같다.
select empno, ename as last_name, null as first_name, sal
from emp
union all
select employee_id, last_name, first_name, salary
from employees;
--union all 구문 뒤에 위치한 테이블의 컬럼 heading을 사용하기 위해서는 앞의 테이블에 컬럼 alias를 명시하면된다.
# 활용 예제 1
- 부하직원이 있는 사원
select empno from emp
intersect
select mgr from emp;
--두 select문의 교집합이 select 된다. - 부하직원이 없는 사원
select empno from emp
minus
select mgr from emp;
--두 select문의 교집합이 select 된다.# 활용 예제 2
- 근무하는 사원이 있는 부서
select deptno from dept
intersect
select deptno from emp;
-- 두 테이블로 부터 deptno가 select 된다.
-- 이 둘의 교집합을 추출한다.
-- 컬럼이 겹치지 않는 애들을 select구문에 넣으면?
- 근무하는 사원이 없는 부서
select deptno from dept
minus
select deptno from emp;
# 활용 예제 3
drop table t2 purge;
drop table t1 purge;
create table t1 as select empno, ename from emp;
create table t2 as select empno, sal from emp;
select * from t1;
select * from t2;
- 해법 1 : 조인
select t1.empno, t1.ename, t2.sal
from t1, t2
where t1.empno = t2.empno
order by 1;
--일반적인 join문
- 해법 2 : Set 연산자
select empno, ename, null as sal
from t1
union all
select empno, null, sal
from t2;
-- empno, ename, null
-- empno, null, sal
-- 이렇게 열을 맞추고 union all로 맞춰야한다.
-- t1, t2테이블이 두개가 위 아래로 붙어서 나온다.
-- 원하는 결과 아님.
select empno, ename, null as sal
from t1
union all
select empno, null, sal
from t2
order by 1, 2, 3;
-- empno, ename, null
-- empno, null, sal
-- 1,2,3열 순으로 오름차순 정렬
-- 위의 결과가 정렬됨
-- 원하는 결과 아님.
select empno, max(ename) as ename, max(sal) as sal
from (select empno, ename, null as sal
from t1
union all
select empno, null, sal
from t2)
group by empno
order by 1;
-- from절 안에 select 문의 결과는 empno가 같은 값이 두개가 연속으로 나오고 하나에는 ename만 있고, 다른 하나에는 sal만 있다.
-- 이 것을 max(ename)과 max(sal)로 하고 group by를 empno로 하면, 같은 empno, 즉 두개중에서 값이 큰 값만 뽑는다. 하나에는 값이 있고, 다른 하나에는 값이 없기 때문에 값이 있는 것만 추출된다. 그래서
EMPNO ENAME SAL
----- -------------------- ----------
7369 SMITH
7369 800
...
-- 위와 같은 결과가
EMPNO ENAME SAL
----- -------------------- ----------
7369 SMITH 800
...-- 위와 같은 결과로 합쳐진다. where절을 이용하여 join predicate을 사용하지 않고, set연산자인 union을 사용해서 join과 똑같은 결과를 얻었다.
- 해법 3 : 서브쿼리
select empno, ename, (select sal from t2 where empno = t1.empno) as sal
from t1;
-- t1테이블의 empno컬럼과 t2의 empno컬럼이 같은 것 중에서
-- sal만 추출하여 empno, ename, sal로 추출한다.
-- 주(main)절에서 where절을 사용하지 않고, subquery에서 where절을 사용해서 join을 구현
- 해법 4 : 사용자 정의 함수
create or replace function uf_sal
(p_empno number)
return number
is
v_ret number;
begin
select sal into v_ret
from t2
where empno = p_empno;
return v_ret;
end;
/
-- create or replace 로 function을 만들었다.
select empno, ename, uf_sal(empno) as sal
from t1;
-- uf_sal이라는 function에다가 t1의 empno를 넣으면, function내부적으로 테이블 t21의 sal을 리턴한다.# 7-21
create or replace view all_tax
as
select department_id, department_name, location_id
from departments
union all
select location_id, city, 0
from locations
union all
select employee_id, email, department_id
from employees;
-- view 객체를 만든다. -- 왜 이런 객체를 만들었지? 그냥 예를 위해서 만든것 같다.
↓↓
create or replace view all_tax
as
select department_id, department_name, location_id, '강남' as gubun
from departments
union all
select location_id, city, 0, '역삼'
from locations
union all
select employee_id, email, department_id, '삼성'
from employees;
select * from all_tax;
↓↓
create or replace view all_tax
as
select department_id, department_name, location_id, '강남' as gubun, 1 as gijun
from departments
union all
select location_id, city, 0, '역삼', 3 as gijun
from locations
union all
select employee_id, email, department_id, '삼성', 2 as gijun
from employees;
col gijun noprint
-- 지금 수행중인 테이블에서 gijun 컬럼을 출력하지 않는다.
select * from all_tax
order by gijun;
댓글 없음:
댓글 쓰기