5시간코딩
[교차합집합] departments와 employees 테이블의 department_id를 가지고 교집합, 차집합, 합집합을 출력하세요. 본문
[교차합집합] departments와 employees 테이블의 department_id를 가지고 교집합, 차집합, 합집합을 출력하세요.
5시간코딩 2019. 4. 5. 00:231. oracle
1) 교집합
select department_id from departments
intersect
select department_id from employees;
| ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 27 | 429 | 6 (84)| 00:00:01 | | 1 | INTERSECTION | | | | | | | 2 | SORT UNIQUE NOSORT| | 27 | 108 | 2 (50)| 00:00:01 | | 3 | INDEX FULL SCAN | DEPT_ID_PK | 27 | 108 | 1 (0)| 00:00:01 | | 4 | SORT UNIQUE | | 107 | 321 | 4 (25)| 00:00:01 | #####정렬작업 | 5 | TABLE ACCESS FULL| EMPLOYEES | 107 | 321 | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------------- |
select department_id
from departments o
where exists(select 'x' from employees where department_id = o.department_id);
| --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 70 | 1 (0)| 00:00:01 | | 1 | NESTED LOOPS SEMI| | 10 | 70 | 1 (0)| 00:00:01 | | 2 | INDEX FULL SCAN | DEPT_ID_PK | 27 | 108 | 1 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN| EMP_DEPARTMENT_IX | 41 | 123 | 0 (0)| 00:00:01 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("DEPARTMENT_ID"="O"."DEPARTMENT_ID") |
2) 차집합
select department_id from employees
minus
select department_id from departments;
select department_id
from departments o
where not exists(select 'x' from employees where department_id = o.department_id);
3) 합집합
select department_id from employees
union
select department_id from departments;
| ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 134 | 429 | 6 (50)| 00:00:01 | | 1 | SORT UNIQUE | | 134 | 429 | 6 (50)| 00:00:01 | ##### 정렬작업 | 2 | UNION-ALL | | | | | | | 3 | TABLE ACCESS FULL| EMPLOYEES | 107 | 321 | 3 (0)| 00:00:01 | | 4 | INDEX FULL SCAN | DEPT_ID_PK | 27 | 108 | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------- |
select department_id
from employees
union all
select department_id
from departments o
where not exists(select 'x' from employees where department_id = o.department_id);
| --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 134 | 429 | 4 (25)| 00:00:01 | | 1 | UNION-ALL | | | | | | | 2 | TABLE ACCESS FULL| EMPLOYEES | 107 | 321 | 3 (0)| 00:00:01 | | 3 | INDEX FULL SCAN | DEPT_ID_PK | 27 | 108 | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------- |
2. r
a=subset(emp,,select=DEPARTMENT_ID,drop=TRUE)
b=subset(dept,,select=DEPARTMENT_ID,drop=TRUE)
intersect(a,b)
setdiff(a,b)
setdiff(b,a)
union(a,b)
setequal(a,b)
identical(a,b)
3. python
import pandas as pd
emp=pd.read_csv("c:/data/employees.csv")
dept=pd.read_csv("c:/data/departments.csv")
a=emp.loc[:,'DEPARTMENT_ID']
b=dept.loc[:,'DEPARTMENT_ID']
a=set(a)
b=set(b)
a&b
a-b
b-a
a|b