[Oracle] Sub Query

• sub query : select 구문 내부에 존재하는 select 구문

---

💡

Sub Query 작성 시 주의 사항

• Sub Query부분은 Where 절에 연산자 오른쪽에 위치해야 하며 반드시 괄호로 묶어야 한다.
• 특별한 경우 (Top-n 분석 등)를 제외하고는 Sub Query 절에 Order by 절이 올 수 없다.
• 단일 행 Sub Query 와 다중 행 Sub Query 에 따라 연산자를 잘 선택해야 합니다.
• 메인쿼리의 from 절에 있는 테이블의 칼럼명은 서브쿼리 내에서 사용 될 수 있으나 서브쿼리의 from 절에 있는 테이블의 칼럼명은 메인쿼리에서 사용할 수 없다.

---

💡 sub query 위치

1. where 조건문

2. having 조건문

3. 컬럼부분 : 스칼라 서브쿼리

4. from 구문 : inline view

---

• 단일행 sub query

              : 서브쿼리의 결과가 1개인 경우.

              : 사용 가능 연산자 : =,<,>,<>,<=,≥

예시 : emp테이블에서 SCOTT사원보다 급여가 많은 사람의 이름과 급여 출력하기.
select sal from emp where ename = 'SCOTT'
select ename, sal from emp where sal >3000

select ename, sal from emp 
where sal >(select sal from emp where ename = 'SCOTT')

---

문제 : 
 1. 김진욱 학생보다 높은 학년의 이름,학년,학과번호,학과명 출력하기.
select s.name,s.grade,s.deptno1,d.dname from student s, department d 
where s.deptno1 = d.deptno
and grade>(select grade from student where name = '김진욱')

문제 : 
 2. 교수테이블에서 김현정 교수보다 나중에 입사한 교수의 이름,입사일,학과명 출력하기.
-- 오라클 방식
select p.name 이름 ,p.hiredate 입사일,d.dname 학과명 
from professor p,department d
where p.deptno = d.deptno
and hiredate>(select hiredate from professor where name = '김현정')

-- ansi 방식
select p.name 이름 ,p.hiredate 입사일,d.dname 학과명 
from professor p join department d
on p.deptno = d.deptno
where hiredate>(select hiredate from professor where name = '김현정')

• 다중 행 sub query 

      : 서브 쿼리의 결과가 여러 개인 경우.

      : 사용 가능 연산자

      : in(같은 값 반환),>any(최소값 반환),<any(최대값 반환), <all(최소값 반환),>all(최대값 반환),

        exist(sub query 의 값이 있을 경우 반환함) =,<,>,<>,<=,>= 연산자 사용 불가

예시 : emp2,dept2 테이블을 이용하여 근무지역이 서울지사인 모든 사원의
 사번,이름,부서번호 조회하기.
-- join 구문
select e.empno,e.name,e.deptno
from emp2 e join dept2 d
on e.deptno = d.dcode
where d.area = '서울지사'

--서브쿼리 사용
select empno,name,deptno 
from emp2 
where deptno in (select dcode from dept2 where area='서울지사')

select empno,name,deptno 
from emp2 
where deptno = (select dcode from dept2 where area='서울지사') 
                           --- 오류 :  다중행 서브쿼리에서는 =,>,<,.... 연산자는 사용불가  

---

문제 :
 1. emp2테이블에서 과장직급(position)의 최소연봉자보다 연봉이 높은 사람의 
  이름,직급,연봉(pay)를 조회하기.
select name, position, pay 
from emp2 
where pay > (select min(pay) from emp2 where position = '과장')

문제 : 
 2. 학생 중 각 학년의 최대키를 가진 학생의 학년과 이름,키를 출력하기
select grade,name,height from student 
where height = (select max(height) from student where grade = 1) and grade =1

select grade,name,height from student 
where height = (select max(height) from student where grade = 2) and grade =2

select grade,name,height from student 
where height = (select max(height) from student where grade = 3) and grade =3

select grade,name,height from student 
where height = (select max(height) from student where grade = 4) and grade =4

• 다중 컬럼 sub query

           : 비교 대상 컬럼이 여러 개인 경우

예시 : 1 ~ 4학년 까지 한번 조회
 select grade,name,height from student
 where (grade,height) in 
 (select grade, max(height) from student group by grade)

예시 : 교수중 학과별로 입사일이 가장 빠른 교수의 교수번호(profno),이름(name),학과명 조회. 
학과명으로 정렬하여 출력하기
select p.profno,p.name,p.hiredate,d.dname
from professor p,department d
where p.deptno = d.deptno 
and (p.deptno,p.hiredate) in (select deptno,min(hiredate) from professor group by deptno)
order by 3