.png)
어제 배웠던, 서브쿼리의 진도를 이어 나가보자.
📝목차
- 다중 열 SubQuery
- 상호연관 SubQuery
- Inline View
- Ranking (Top-N)
1. 다중열 서브쿼리
: 반환 값이 여러 열인 서브 쿼리
다중열도, 다중행과 마찬가지로 반환 값이 여러 가지이다. 다만 한 컬럼 내에서 여러 값이 아닌, 여러 열을 반환한다.
[ 문법 ]
(값 1, 값2) = (select 값1, 값2 from 테이블 where 조건);
(dept_code, job_code) = (select dept_code, job_code from employee where ent_yn='Y)- 여러 열을 비교하기 때문에, 같다, in 비교가 가능하다.
- 단, 서브 쿼리의 출력값과 메인 쿼리의 입력 값이 일치해야 한다.
- 메인 쿼리에 담는 순서도 일치해야 한다.
퇴사한 사람과 동일한 부서와 동일한 직급인 직원의 목록을 출력하세요
위의 예제의 퇴사한 사람을 확인하는 쿼리는 다음과 같다.
select dept_code, job_code from employee
where ent_yn='Y'; -- 다중열로 출력하지만, 위처럼 작성하면 모든 퇴사자의 데이터가 나오게 된다.
이제, 다중열을 통해 검색조건에 따라 값을 담아보자.
select
emp_name, dept_code, job_code, hire_date
from employee
where
(dept_code, job_code) = (select dept_code, job_code from employee where ent_yn='Y')
and emp_name not in ('이태림');where 절을 통해 메인 쿼리에서 사용할 값을 담았다.
그리고 담은 값은 메인 쿼리 내에서 조건에 따라 select 으로 출력되게 된다.
= , in, any, all
: 다중 행-열 또는 단일행으로 받는 서브쿼리값을 메인쿼리로 담을 때는 조건이 존재한다.
- 전제 : 같은 컬럼 값은 컴퓨터가 구분할 수 없다.
=(같다)는 다중행, 다중행열의 경우 적용할 수 없다.- 단일행, 다중열은 한 컬럼의 한 행의 값이라 비교가 가능하다.
in : 다중행, 다중행열이 반환하는 여러 값 안에서 하나를 찾을 때 주로 사용한다.
2. 상호연관 서브쿼리
: 주로 select 절에 들어가는 서브쿼리
select () from () where () group by () having () order by ()서브 쿼리는 DQL을 이루는 모든 절 안에 들어갈 수 있다. 이 중 select 절에 들어가는 쿼리를 상호연관 쿼리라고 한다.
이런 상관 쿼리는 기존의 사용과는 다른 특이점이 있다.
select
emp_name 직원명,
(select dept_title from department where dept_code = dept_id) 부서명 -- 조건을 걸어서 가져옴.
from employee;위 예제는 서브쿼리가 상관 쿼리로 사용된 예시이다.
여기서 보면, 두 가지 특이점이 있다.
1) 외부 테이블에서 값을 끌어왔다.
2) dept_code employee 테이블의 컬럼인데, 서브쿼리에서 사용하였다.
이를 통해 상관 쿼리는 메인 쿼리의 데이터를 사용하고 있음을 알 수 있다.
따라서 실행 순서는 from을 제외하고, 서브쿼리가 먼저 실행된다.
위 예제에서 서브쿼리는 메인으로부터 받은 ‘dept_code’와 해당 테이블의 ‘dept_id’를 비교한다.
그리고 둘이 일치할 때만, department 테이블에서 ‘dept_title’을 반환해준다.
이런 의미에서 메인 쿼리와 연관지어 동작하는 쿼리는 의미에서 상(호연)관 쿼리라고 한다.
단, 상관 서브쿼리는 홀로 사용될 수 없고 기능은 편하지만, 성능이 안 좋다.
왜냐면 미리 뽑아놓고, 메인에서 출력 마다 한 번씩 확인해야 되기 때문이다.
3. inline view
: from 절에서 주로 사용되는 서브쿼리
from 절에서 우리는 내부에 저장된 테이블을 지정하고, 거기서 데이터를 가져온다.
하지만 이뿐만이 아니라, 테이블들의 컬럼을 모아 가상의 테이블을 만들어 줄 수 있다.
select * from employee;
select * from (select emp_name, salary from employee); 작정만 하면 join과 엮어서도 만들 수 있다.
4. Ranking (Top-N)
: 위에서부터 순서를 붙히는 함수
배울 때는 문법이라고 했지만, 크게는 순서과 다른 순위를 정해서 하나의 테이블로 반환하는 함수이다.
사용법으론 크게 3가지가 있다.
1) rank() over(order by 기준 컬럼명 desc)
: 공동순위 부여 가능, 공동순위 부여시 공백은 제거
select
emp_name,
salary,
rank() over(order by salary desc) 순위
from
employee;2) dense_rank() over(order by 기준 컬럼명 desc)
: 공동순위를 부여하지만, 그로 인해 비는 값을 지우지 않는다.
select
emp_name,
salary,
dense_rank() over(order by salary desc) 순위
from
employee;3) row_number() over(order by 기준 컬럼명 desc)
: 공동순위를 붙이지 않고, 행번호를 컬럼으로 빼는 용도로 사용된다.
select
emp_name,
salary,
row_number() over(order by salary desc) 순위
from
employee;추가적으로 inline view랑 row_number()는 같이 쓰인다.
select * from
(select emp_name, salary, row_number() over(order by salary desc) 순위 from employee)
where
순위 between 1 and 5; 1~5등을 뽑는 예제에서 1~5만을 간추리는 것은 쉬운 일이 아니다.
아예 그래서 inline view 로 가상테이블을 만든다. 단, 테이블에서 아예 랭킹을 정하고
메인쿼리로 가져와서, 랭킹의 담긴 값을 이용하면 된다.
