[Oracle] 6일차) 그룹 함수 (UNION ALL, MINUS, INTERSECT )

참고 : https://preamtree.tistory.com/45

여기 설명 잘되어잇다. 까먹지말자

INTERSECT : 교집합

MINUS : 차집합

UNION : 합집합 (첫번째 컬럼으로 자동정렬)

UNION ALL : 중복 허용되는 합집합

일단 개념은 위와 같고

쿼리를 두개 쓰면 된다

select country_id, country_name

from countries 

MINUS

select distinct c.country_id, c.country_name

from locations l , countries c , departments d

where d.location_id = l.location_id

and l.country_id = c.country_id;

---

다른방법으로 푸려햇는데

서브쿼리가 두개들어가서 그냥 포기~~

---

오늘의문제~~

--1

select distinct job_id, department_id from employees

where department_id = 10

union all

select distinct job_id, department_id from employees

where department_id = 50 

union all

select distinct job_id, department_id from employees

where department_id = 20

;

---

--2

select  country_id, country_name

from countries 

minus

select distinct c.country_id, c.country_name

from locations l , countries c , departments d

where d.location_id = l.location_id

and l.country_id = c.country_id;

- 아래와 같이 푸려다 NULL값땜시 망함

select distinct country_id, country_name, LOCATION_ID

from   countries

left outer join locations  using (country_id)

where (location_id IS NULL OR location_id not in ( select distinct location_id from departments))

ORDER BY COUNTRY_ID

;

select distinct location_id from departments order by location_id ;

 select distinct location_id from locations;

SELECT DISTINCT COUNTRY_ID FROM LOCATIONS 

WHERE location_id IN

(select distinct location_id from departments)

;

select distinct country_id, country_name

from locations

left join countries using (country_id)

where location_id in (

                    select location_id from locations minus select location_id from departments

                )

                ;

---

--3

select department_id, job_id, sum(salary)

from employees 

where 1=1

and department_id=10

group by (department_id, job_id)

union all

select department_id, ' ' job_id, sum(salary)

from employees 

where department_id=10

group by (department_id, job_id)

union all

select department_id, job_id, sum(salary)

from employees 

where department_id=20

group by (department_id, job_id)

union all

select department_id, ' ' as job_id, sum(salary)

from employees 

where department_id=20

group by (department_id)

union all

select department_id, job_id, sum(salary)

from employees 

where department_id=30

group by (department_id, job_id)

union all

select department_id, ' ' job_id, sum(salary)

from employees 

where department_id=30

group by (department_id )

union all

select null department_id, 'TOTAL' job_id, sum(salary)

from employees 

where department_id in (10, 20, 30)

;

CF1) 천재동기가 보내준 쥰니 짧은 쿼리

select   department_id, job_id, sum(salary)

from     employees 

where    department_id in (10, 20, 30)

group by job_id, department_id

union 

select   department_id, null, sum(salary)

from     employees

where    department_id in (10, 20, 30)

group by department_id

union

select   null, 'TOTAL', sum(salary)

from     employees

where    department_id in (10, 20, 30)

order by department_id;

CF2) 롤업쓰면 네줄

select department_id, nvl(job_id, ' '), sum(salary)

from employees

where department_id in (10, 20, 30)

group by rollup(department_id, job_id)

;