10.집합연산

집합연산

• UNION:각 쿼리의 결과 합을 반환하는 합집합(중복제거)
• UNION ALL :각 쿼리의 모든 결과를 포함한 합집합(중복제거 안함)

select employee_id,first_name,salary,department_id
from employees
where department_id = 10
union
select employee_id,first_name,salary,department_id
from employees
where department_id = 20;

EMPLOYEE_ID	FIRST_NAME	SALARY	DEPARTMENT_ID
200	Jennifer	4400	10
201	Michael	13000	20
202	Pat	6000	20

 

• 집합 연산자로 두개의 결과값을 연결할 때는 열개수와 각열의 자료형이 순서대로 일치해야된다.
• ORA-01789: 질의 블록은 부정확한 수의 결과 열을 가지고 있습니다(오류:열개수 불일치)

select employee_id,first_name,department_id
from employees
where department_id=20;
union
select employee_id,first_name
from employees
where department_id=10;

 

• ORA-01790: 대응하는 식과 같은 데이터 유형이어야 합니다(열자료형 불일치)

select employee_id,first_name,salary,department_id
from employees
where department_id = 10
union
select employee_id,salary,department_id,first_name
from employees
where department_id =20;

 

• 열은 다르지만 열의 개수와 열자료형이 일치하면 결과는 나타난다

select * from employees;
select first_name,department_id,salary,email
from employees
where department_id = 10
union
select email,salary,department_id,first_name
from employees
where department_id = 20;

FIRST_NAME	DEPARTMENT_ID	SALARY	EMAIL
Jennifer	10	4400	JWHALEN
MHARTSTE	13000	20	Michael
PFAY	6000	20	Pat

 

출력결과가 같을때 중복 제거 (union)

select * from employees;
select first_name,department_id,salary,email
from employees
where department_id = 10
union
select first_name,department_id,salary,email
from employees
where department_id = 10;

 FIRST_NAME	DEPARTMENT_ID	SALARY	EMAIL
Jennifer	10	4400	JWHALEN

 

출력결과가 같을때 중복 (union all)

select first_name,department_id,salary,email
from employees
where department_id = 10
union all
select first_name,department_id,salary,email
from employees
where department_id = 10

FIRST_NAME	DEPARTMENT_ID	SALARY	EMAIL
Jennifer	10	4400	JWHALEN
Jennifer	10	4400	JWHALEN

 

• MINUS(차집합)

부서 아이디가 50인 사원을 빼고 사원의 이름,급여,사원아이디 부서아이디를 출력하세요.

select employee_id,first_name,salary,department_id
from employees
minus
select employee_id,first_name,salary,department_id
from employees
where department_id = 50;

100	Steven	24000	90
101	Neena	17000	90
102	Lex	17000	90
103	Alexander	9000	60
104	Bruce	6000	60
105	David	4800	60
106	Valli	4800	60
107	Diana	4200	60
108	Nancy	12008	100
109	Daniel	9000	100
110	John	8200	100
111	Ismael	7700	100
112	Jose Manuel	7800	100
113	Luis	6900	100
114	Den	11000	30
115	Alexander	3100	30

 

• INTERSECT(교집합)

부서ID가 50인 사원의 이름,급여,사원아이디,부서아이디를 출력하세요.

select employee_id,first_name,salary,department_id
from employees
intersect
select employee_id,first_name,salary,department_id
from employees
where department_id=50;

EMPLOYEE_ID	FIRST_NAME	SALARY	DEPARTMENT_ID
120	Matthew	8000	50
121	Adam	8200	50
122	Payam	7900	50
123	Shanta	6500	50
124	Kevin	5800	50
125	Julia	3200	50
126	Irene	2700	50
127	James	2400	50
128	Steven	2200	50
129	Laura	3300	50
130	Mozhe	2800	50
131	James	2500	50
132	TJ	2100	50
133	Jason	3300	50
134	Michael	2900	50