참고 : 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) ; |