[ORACLE]서브쿼리

단일행서브쿼리
서브쿼리  결과로 하나의 row만 반환하는 쿼리에 사용
>, < , =,<= ,=>
(예) salary > 3000 
salary = 3000와 salary IN(3000)는 같은 표현

IN 단일행,다중행 사용가능

문제-SCOTT보다 급여가 많은 사원 검색
 순서1. SCOTT의 급여를 알아낸다.-결과로 단일행이 출력

select salary --ename 넣어버리면 밑에 서브쿼리로 이용할때 value가 많다고 오류 뜸
from employee
where ename='SCOTT'; ---3000

순서2.위의 SCOTT의 급여 3000보다 급여가 많은 사원의 사원명과 급여 검색

select ename,salary from employee where salary>3000;

순서3.메인쿼리-서브쿼리

select ename,salary
from employee
where salary>(select salary--ename 넣지않도록 조심
		from employee
		where ename='SCOTT');
              --서브쿼리에서 실행한 결과(3000)가 메인쿼리에 전달되어 최종결과를 출력
              
 --위 결과에는 'SCOTT'도 함께 조회됨. 'SCOTT'은 제외하고 조회하려면			
select ename,dno
from employee
where dno=(select dno
	  from employee
    	   where ename='SCOTT')
and ename !='SCOTT'	;--조건추가

다중행 비교 연산자
IN,ANY,SOME,ALL,EXISTS
(예) salary IN(1000,2000,3000)

IN연산자
메인쿼리의 비교조건에서 서브쿼리의 출력결과와 '하나라도 일치하면'
메인쿼리의 where절이 true 조건을 만족하는 것마다 결과로 가져옴
단일 또는 다중 행 서브쿼리 둘다 사용가능함

문제-부서별 최소 급여를 받는 사원의 부서번호, 사원번호, 이름, 최소급여
1.부서별 최소급여 구하기

select dno,min(salary)
from employee
group by dno;--950,800,1300

2.메인쿼리-서브쿼리

select dno,eno,ename,salary
from employee
where salary in (950,800,1300)--조건 하나라도 만족하면 가져옴
order by dno;
				
select dno,eno,ename,salary
from employee
where salary in (select min(salary) --★★★dno 제외해야함 
			from employee
			group by dno)
order by dno;

번외-join 방법을이용

--[2-2]join 방법-1 이용
--[1]
select dno,min(salary)
from employee
group by dno;

--[2]
select *
from employee e1,(select dno,min(salary)
					from employee
					group by dno)e2
where e1.dno=e2.dno--조인조건				
order by e1.dno;			

--[3]
select e1.dno,eno,ename,salary 
from employee e1,(select dno,min(salary) as "minSalary"
					from employee
					group by dno)e2
where e1.dno=e2.dno--조인조건	
--and salary=min(salary)--검색조건=> 오류 min()함수 사용하여	
--[오류해결]별칭사용
and salary="minSalary"
order by e1.dno;	

--[2-3]join 방법-2 이용
select e1.dno,eno,ename,salary 
from employee e1 join (select dno,min(salary) as "minSalary"
					from employee
					group by dno)e2
on e1.dno=e2.dno
where salary="minSalary"
order by e1.dno;	

--[2-4]join 방법-3 natural join, 조인조건x,별칭x
select dno,eno,ename,salary 
from employee natural join (select dno,min(salary) as "minSalary"
					from employee
					group by dno)
where salary="minSalary"
order by dno;	

--[2-5]join 방법-4
select dno,eno,ename,salary 
from employee join (select dno,min(salary) as "minSalary"
					from employee
					group by dno)
using(dno)			
where salary="minSalary"
order by dno;

위의문제에서 min(salary)도 같이 출력하려면

select dno,eno,ename,salary,min(salary)
from employee
where salary in(select min(salary)from employee group by dno)--검색조건
group by dno,eno,ename, salary 
order by 1;

ANY연산자
where 컬럼명 = any(서브쿼리의 결과1, 결과2) =>결과들 중 아무거나와 같다면 참.
where 컬럼명 IN(서브쿼리의 결과1, 결과2) =>결과들 중 아무거나와 같다면 참.
IN과 비슷하니 IN을 쓰면됨!!!
A조건 or B조건
합집합 (각각 만족하는 조건의 결과를 다 합침)

 

where 컬럼명 < any(서브쿼리의 결과1, 결과2) =>결과들 중 "최대값" 보다 작으면 참.
where 컬럼명 > any(서브쿼리의 결과1, 결과2) =>결과들 중 "최소값" 보다 크면 참.

where salary != ANY(1300,800,950)
where salary <> ANY(1300,800,950)
where salary ^= ANY(1300,800,950)
where salary not in(1300,800,950)
=>서브쿼리 결과 중 어느것도 아니면 true , 1300,800,950 모두 아니어야함

where salary < any(1300,800,950) => 서브쿼리결과들 중 '최대값1300'보다 작은걸 salary에 찾아서 결과를 모두 반환
where salary > any(1300,800,950) => 서브쿼리결과들 중 '최소값800'보다 큰걸 salary에 찾아서 결과를 모두 반환

[문제]
직급이 SALESMAN이 아니면서
급여가 임의의 SALESMAN보다 낮은 사원의 정보(사원이름,직급,급여,출력)
(※임의의=각각으로 해석)

--[1]직급이 SALESMAN의 급여구하기
select distinct salary --1250이 두번나오기때문에 중복제거를 위해 distinct
from employee
where job='SALESMAN';--1600,1250,1500

--[2]
select ename,job,salary
from employee
where salary < any(select salary
				from employee
				where job='SALESMAN')
				--salary < ANY(1600,1250,1500)의 서브쿼리 결과 중 '최대값'보다 작으면 참
and job!='SALESMAN';

ALL연산자
A조건 and B조건
교집합(모든 조건을 동시에 만족)
where salary < ALL(1300,800,950) => 서브쿼리결과들 중 '최소값800'보다 작은걸 salary에 찾아서 결과를 모두 반환
where salary > ALL(1300,800,950) => 서브쿼리결과들 중 '최대값1300'보다 큰걸 salary에 찾아서 결과를 모두 반환


[문제] 직급이 SALESMAN이 아니면서
급여가 모든 SALESMAN보다 낮은 사원의 정보 (사원이름, 직급,급여)출력

--[1]직급이 SALESMAN의 급여구하기

select distinct salary 
from employee
where job='SALESMAN';--1600,1250,1500

--[2] 1600보다도 작고 1250보다도 작고 1500보다도 작은 동시에 만족하는 
select ename,job,salary
from employee
where salary < all(select salary
				from employee
				where job='SALESMAN')
				--salary < ALL(1600,1250,1500)의 서브쿼리 결과 중 '최소값1250'보다 작으면 참
and job!='SALESMAN';

EXISTS연산자

select 
from
where exists (서브쿼리);
--서브쿼리에서 구해진 데이터가 1개라도 존재하면 true -> 메인쿼리 실행
--				      1개라도 존재하지 않으면 false -> 메인쿼리 실행x

select 
from
where NOT exists (서브쿼리);
--서브쿼리에서 구해진 데이터가 1개라도 존재하지않으면 true -> 메인쿼리 실행
--				      1개라도 존재하면 false -> 메인쿼리 실행x

--[문제-1] 사원테이블에서 직업이 'president'가 있으면 모든 사원이름을 출력, 없으면 출력안함
--★ 문제의 뜻 : 조건을 만족하는 사원이 있으면 메인쿼리를 실행하여 결과를 출력

--[1] 사원테이블에서 직업이 'President'인 사원의 사원번호 조회
select eno
from employee
where job='PRESIDENT';--7839

--[2]
select ename
from employee
where exists (select eno
		 from employee
		 where job='PRESIDENT');
--결과 : 14명			  
			  
--[위 문제에 job이 'SALESMAN 이면서' 조건을 추가 ]	
--조건을 AND 연결: 두 조건이 모두 참이면 참	
select ename
from employee
where job='SALESMAN' AND exists (select eno
				 from employee
			   	 where job='PRESIDENT');	  
--결과 : 4명 and 14명 => 4명
			  
--[위 문제에 job이 'SALESMAN이거나' 조건을 추가 ]	
--조건을 AND 연결: 두 조건이 하나가 참이면 참	
select ename
from employee
where job='SALESMAN' OR exists (select eno
				 from employee
			  	 where job='PRESIDENT');	  
--결과 : 4명 or 14명=>14명								
								
--[NOT EXISTS]			  					 
--조건을 AND 연결: 두 조건이 모두 참이면 참	
select ename
from employee
where job='SALESMAN' AND NOT exists (select eno--데이터가 존재하기 때문에 메인쿼리실행X 결과 아무것도 없음
			 from employee
			    where job='PRESIDENT');	  
--결과 : 4명 and 0명 => 0명   					 
									
--조건을 OR 연결: 두 조건이 모두 참이면 참	
select ename
from employee
where job='SALESMAN' AND NOT exists (select eno--데이터가 존재하기 때문에 메인쿼리실행X 결과 아무것도 없음
			 from employee
			   where job='PRESIDENT');									
									
--결과 : 4명 or 0명 =>4명

[문제]사원테이블과 부서테이블에서 동시에 없는 부서번호, 부서이름 조회 (40,부서명이 출력)

--[방법-5]EXISTS 이용-오라클 실행순서를 알아야 정확히 풀 수 있음.
/****************************************************************************
 *  참조 : 오라클 실행 순서
 *  from -> where -> group by -> having -> select 컬럼명의 별칭 -> order by
 */

select dno, dname
from department d 
where not exists(select dno 
				from employee--별칭 사용 안해도 됨
				where d.dno=dno);--10 20 30
 --not exists 서브쿼리의 10,20,30을 제외한 40이 출력됨

--MINUS 이용
--10,20,30 
--10,20,30,40  => 조인 후 두 테이블에 동시에있는 부서번호(10,20,30)를 뻄 => 40만 남음
--[1]
select dno, dname
from department 

MINUS

select distinct dno,dname--distinct 생략가능
from department join employee
using(dno);