[Oracle] 5일차 ) JOIN, 서브쿼리, ROWNUM

JOIN, 서브쿼리, ROWNUM

오늘 배운것

정리하는 내용

- OUTER JOIN의 (+)는 확장해야 할 곳에 붙인다. 즉 null 이 있는 테이블

- USING에 쓰인 컬럼은 테이블을 지정할 수 없다. ( select employees.first_name, employees.department_id)

-mysql 의 limit == oracle의 rownum

- GROUP BY 를 썻을 때 WHERE절에 ROWNUM을 쓰면 안됀다. 서브쿼리를 이용하자

SELECT * FROM departments

WHERE rownum <5

SELECT * 

FROM (select department_id, count(*) from employees group by department_id )

WHERE rownum <5

- 마지막에 FETCH FIRST 5 ROWS ONLY;를 사용하면 topN 쿼리를 사용할 수 있다. (12g이후)

ㅇ 서브쿼리의 위치에 따른 명칭

    - SELECT문에 있는 서브쿼리 : 스칼라 서브쿼리

    - FROM절에 있는 서브쿼리 : 인라인 뷰

    - WHERE절에 있는 서브쿼리 : 서브쿼리

ㅇ 서브쿼리의 반환 값에 따른 서브쿼리 종류

    - 단일 행 서브쿼리(Single-Row Subquery) : 서브쿼리의 결과가 1행

    - 다중 행 서브쿼리(Multiple-Row Subquery) : 서브쿼리의 결과가 여러 행

    - 다중 컬럼 서브쿼리(Multi-Column Subquery) : 서브쿼리의 결과가 여러 컬럼

출처: https://ttend.tistory.com/623 [toward the end · · ·]

IN을 이용하게 되면 서브쿼리 전체를 먼저 스캔하고 tableA의 모든 레코드에 대해

IN에서 추출된 데이터와 비교하게 되므로 서브쿼리의 추출되는 레코드가 많다면 성능이 저하됩니다.

EXISTS와 조인을 이용하는 방법은 비교해서 참 또는 거짓만 리턴하므로 서브쿼리의

추출되는 데이터가 많다면 훨씬 좋은 성능을 보이게 됩니다.

- e1의 empId가 managerID로 등록되어있는지 확인하는 쿼리

select e1.employee_id, e1.first_name

from employees e1

where not exists (

                    select 1 

                    from employees e2 

                    where e2.manager_id = e1.employee_id

                );

---

-오늘의 문제 1)

--1-1 location_id, street_address, city, state_province, country_name

select location_id, street_address, city, state_province, country_name

from locations

join countries using (country_id)

;

--1-2

select e.first_name, e.job_id, e.department_id 

from employees e

join departments d on e.department_id = d.department_id

join locations l on l.location_id = d.location_id

where lower(l.city) ='toronto'

;

--1-3

select e1.employee_id, e1.first_name, e1.manager_id, e2.first_name manager_name

from employees e1

left outer join employees e2 on e1.manager_id = e2.employee_id

-오늘의 문제 2)

-- 1. 평균급여를 구하고, lastname에 u가 들어사는 사원과 같은 부서에 근무하는 모든 사원의 사원번호 성 이름

Select * 

from employees e1

where 1=1

and e1.salary > (Select avg(e2.salary) from employees e2)

and e1.department_id in (Select distinct department_id from employees where last_name like '%u%')

;

--2. king 에게 보고하는 모든 사원

Select *

from employees e2

where e2.last_name = 'King'

;

Select e1.last_name, e1.salary

from employees e1

where manager_id in (Select employee_id

                                    from employees e2

                                    where e2.last_name = 'King');

--3. 부서 명칭이 executive인 모근 사원

select department_id, first_name, job_id       

from employees

where 1=1

and department_id in  (select department_id from departments where department_name = 'Executive');

select department_id, first_name, job_id

from employees

join departments using (department_id)

where 1=1

and department_name = 'Executive'

;

--4. 사원 수가 가장 많은 부서의 부서번호

select department_id, department_name

from departments

where department_id = (

                                            select department_id 

                                            from (

                                                        select department_id, count(*) cnt 

                                                        from employees 

                                                        group by department_id 

                                                        order by cnt desc

                                                        ) 

                                            where rownum=1

                                        )

;