[SQL][TIL] 집합 연산자

drop table t1 purge;
drop table t2 purge;

create table t1 (col1 number);
insert into t1 values(1);
insert into t1 values(1);
insert into t1 values(1);
insert into t1 values(2);
insert into t1 values(2);
insert into t1 values(3);
insert into t1 values(3);
insert into t1 values(3);

create table t2 (col1 number);
insert into t2 values(6);
insert into t2 values(6);
insert into t2 values(3);
insert into t2 values(3);
insert into t2 values(4);
insert into t2 values(4);
insert into t2 values(4);

--union

select *
from t1
union
select *
from t2;

--union all

select *
from t1
union all
select *
from t2;

--intersect
,

select *
from t1
intersect
select *
from t2;

--minus

select *
from t1
minus
select *
from t2;

select *
from t2
minus
select *
from t1;

--사원이 근무하는 부서를 검색하시오

select deptno
from dept
intersect
select deptno
from emp;

--사원이 근무하지 않는 부서

select deptno
from dept
minus
select deptno
from emp;

--사원들의 매니저 역할을 하는 사람들 찾아줘

select empno
from emp
intersect
select mgr
from emp;

--사원들의 매니저 역할을 하지 않는 사람들 찾아줘

select empno
from emp
minus
select mgr
from emp;

--컬럼이 존재하지 않는 테이블은 'null 컬럼명' 을 적으면 된다
--정렬은 첫번째 select절의 컬럼명을 따른다
--order by는 맨 아래에 적는다

select employee_id, null start_date, null end_date, job_id, department_id
from employees
where employee_id = 176
union all
select employee_id, start_date, end_date, job_id, department_id
from job_history
where employee_id = 176
order by start_date;

 

 

 

minus 이용해서 사라진 값 찾기

drop table t1 purge;
create table t1
as
select level as no
from dual
connect by level <=100;

--원래 100개

select count(*)
from t1;

--테이블에서 랜덤으로 7개의 숫자를 삭제하세요

delete from t1
where no in (select round(dbms_random.value(1, 100))
             from dual
             connect by level <= 7);

              
--93개로, 7개 삭제됨         

select *
from t1;

--사라진 7개의 값을 찾으시오

select level
from dual
connect by level <=100
minus
select *
from t1;

--A 데이터 집합이랑, B 데이터 집합이 차이가 있나? 가 실제로 현업에서 궁금할 때가 있음 => MINUS 이용