SQL : 07 서브쿼리

_플레이데이터 빅데이터캠프 공부 내용 _5/30, 5/31

MySQL : 07 서브쿼리(Subquery)

1.  개념

(1) 서브쿼리(Sub Query)
- 쿼리안에서 select 쿼리를 사용하는 것.

-  SQL문에서 SELECT 문으로 조회한 결과를 이용하기 위해서.
- 메인 쿼리 : 실제 조회하고자 하는 쿼리. 밖에 있는

- 서브쿼리 : 메인 쿼리에서 사용할 데이터를 조회하기 위한 쿼리

 

(2) 서브쿼리가 사용되는 구
 - select절,  where절, having절 -> 값 대신 쓰는 것. ex) where salary = (select~~~ ) 조회된 값 자체가 사용되는 것.
 - from절 -> 테이블 대신 : 인라인 뷰 ex) from (selet~~~) t; 

 
(3) 서브쿼리의 종류
어느 구절에 사용되었는지에 따른 구분
    - 스칼라 서브쿼리 - select 절에 사용. 반드시 서브쿼리 결과가 1행 1열(값 하나-스칼라) 0행이 조회되면 null을 반환
    - 인라인 뷰 - from 절에 사용되어 테이블의 역할을 한다.

              - select 조회한 결과는 하나의 테이블 표로 나온다. 그 테이블을 from 불러오는 것.

    
(4) 서브쿼리 조회결과 행수에 따른 구분
    - 단일행 서브쿼리 - 서브쿼리의 조회결과 행이 한행인 것. (조회한 데이터 행이 0 또는 1개)
    - 다중행 서브쿼리 - 서브쿼리의 조회결과 행이 여러행인 것.(조회한 데이터 행이 다수. 0개 이상)

    * 이에 따라 연산자가 달라짐.
    
(5) 동작 방식에 따른 구분
    - 비상관(비연관) 서브쿼리 - 서브쿼리에 메인쿼리의 컬럼이 사용되지 않는다.
                메인쿼리에 사용할 값을 서브쿼리가 제공하는 역할을 한다.
    - 상관(연관) 서브쿼리 - 서브쿼리에서 메인쿼리의 컬럼을 사용한다. 
                메인쿼리가 먼저 수행되어 읽혀진 데이터를 서브쿼리에서 조건이 맞는지 확인하고자 할때 주로 사용한다.

(6) 처리 방식

- 서브쿼리는 반드시 ( ) 로 묶어줘야 한다.

 

2. 단일행 서브쿼리

 

EX. (단일행 스칼라 서브쿼리)_ 메인 커리의 where절 내에 서브쿼리 select절이 들어갔다. 

-- 1. sub query 가 먼저 실행된다.
-- 2. sub query 의 조회 결과를 이용해 main query가 실행된다.

-- main query
select emp_id, emp_name, job_id, salary
from emp
where job_id = (select job_id from emp where emp_id= 120); -- (select문) : subquery

 

EX. (단일행 스칼라 서브쿼리) _ 서브쿼리의 조회하는 컬럼나 다르고 select 조건이 동일할 때

     -- > pair 방식 서브쿼리.  두개 이상 컬럼 비교해도 된다.

-- 직원_id(emp.emp_id)가 115번인 직원과 / 같은 업무(emp.job_id)를 하고 and 같은 부서(emp.dept_id)에 속한 직원들을 조회하시오.
select * from emp 
where (job_id, dept_id) = (select job_id, dept_id from emp where emp_id=115) ; 
-- 마치 튜플 대입 처럼. 단, 양 쪽 다 괄호 쳐줘야 함.

-- MYSQL에서는 아래처럼 값을 직접 PAIR 방식으로 대입해서 행을 선택하는 것도된다.

   그러나, 안되는 DBMS도 있게 이때는 그냥 AND 연산으로 해주자. 서브쿼리 내 이 방식은 모든 DBMS가 되지만

select * from emp
where (job_id, dept_id) = ('PU_MAN', 30);

 

EX. (단일행 인라인 뷰 서브쿼리) _  from 절에 사용되어 테이블의 역할 하는 인라인 뷰

- select 조희의 결과는 표 테이블! 이다. 이 조회한 표를 from 테이블로 쓰겠다. => 인라인 뷰

- 이때 별칭 꼭 적어줘야 함.

-- '부서직원들의 평균'이 전체 직원의 평균(emp.salary) 이상인 부서의 이름(dept.dept_name), 평균 급여(emp.salary) 조회.
-- 평균급여는 소숫점 2자리까지 나오고 통화표시($)와 단위 구분자 출력

select dept_name, concat('$', format(평균급여, 2)) "평균급여" -- 메인 쿼리에서 원하는 format을 여기 맞춰줌.
from(
select d.dept_name, avg(e.salary) "평균급여"
from dept d join emp e on d.dept_id = e.dept_id
group by d.dept_id, d.dept_name
having avg(e.salary) >= (select avg(salary) from emp)
order by 2) t ; -- 별칭 적어줘야 함.
-- 일단 서브커리에서 원하는 정렬된 상태로 만들어서!


-- 정렬 할 때 만약 문자열로 형식을 바꿨을 때 생각과 다르게 순서는 바뀐다. 
-- 이를 위해 우린 from 절 인라인 뷰를 이용해 정렬까지 끝맞힌 뒤 
-- 이 테이블을 불러와 원하는 문자열 형식으로 select 조회하겠다.

 

 EX. (단일행 스칼라 서브쿼리) _  서브 안에 서브 쿼리 넣어서도 처리 가능.

-- TODO: 급여(emp.salary)가장 많이 받는 직원이 속한 부서의 이름(dept.dept_name), 위치(dept.loc)를 조회.

select *
from dept
where dept_id in (select dept_id from emp where salary = (select max(salary) from emp));

 

 

 EX. (단일행 스칼라 서브쿼리) _  서브 쿼리 내 JOIN

-- 전체 직원들 중 'IT' 부서(dept.dept_name)의 직원중 가장 많은 급여를 받는 직원보다 더 많이 받는  
-- 직원의 ID(emp.emp_id), 이름(emp.emp_name), 입사일(emp.hire_date), 부서 ID(emp.dept_id), 급여(emp.salary) 조회
-- 입사일은 "yyyy년 mm월 dd일" 형식으로 출력
select emp_id, emp_name, date_format(hire_date,'%Y년 %m월 %d일') "입사일", dept_id, salary
from emp
where salary > (select max(e.salary) from emp e join dept d on e.dept_id = d.dept_id where d.dept_name = 'IT' )
order by salary;

 

** 주의 EX.  NULL 모르는 값은 비교연산을 할 수 없다. 무조건 False가 된다. 항상 처리 시 NULL 값을 주의해야 한다.

-- 이 연산에서 job_id 값인 null인 행이 조회가 안됨.
-- null 모르는 값은 이 값을 같냐 다르냐 이러한 연산의 결과를 알 수 없다. 
-- 그래서 null 비교 안산은 무조건 False가 반환되어, 해당 행은 조회되지 않는 것.
-- 항상 null이 포함될 수 있는 컬럼은 조심해야 한다.

select * from emp
where job_id != 'ST_CLERK';

-- 전체 직원들 중 담당 업무 ID(emp.job_id) 가 'ST_CLERK'인 직원들의 평균 급여보다 적은 급여를 받는 직원들의 모든 정보를 조회. 
-- 단 업무 ID가 'ST_CLERK'이 아닌 직원들만 조회. 

-- 방법1 NULL 데이터 값도 포함하기

select *
from emp
where salary < (select avg(salary) from emp where job_id = 'ST_CLERK') 
      and (job_id != 'ST_CLERK' or job_id is null) 
-- null도 포함되게 -- and연산자가 or보다 우선이기에 꼭 맞게 괄호 쳐주기. 조심.
order by salary desc;


-- 방법2 NULL 데이터 값도 포함하기
select *
from emp
where salary < (select avg(salary) from emp where job_id = 'ST_CLERK') 
and ifnull(job_id, 'None') != 'ST_CLERK' 
order by salary desc;

 

3.  다중행 서브쿼리

 : 서브쿼리의 조회된 결과 값이 하나가 아닌 여러 데이터 값이(여러 행이) 나오는 것

    (컬럼이 두개 아님. 행(데이터)가 두개인 것)

where절 에서의 연산자

- in : 값들 중에 하나만.
- 비교연산자 any (subquery) : 조회된 값들 중 하나만 참이면 참. 

        EX) where   컬럼   >,<,=,=!     any(서브쿼리)
- 비교연산자 all (subquery) : 조회된 값들 모두와 참이면 참.

        EX) where   컬럼   >,<,=,=!     all(서브쿼리)

 

 EX. (다중행. IN) 

-- 'Alexander' 란 이름(emp.emp_name)을 가진 관리자(emp.mgr_id)의 
-- 부하 직원들의 ID(emp_id), 이름(emp_name), 업무(job_id), 입사년도(hire_date-년도만출력), 급여(salary)를 조회
-- 알렉산더가 두명. 데이터 행 두개 -> 다중행

select emp_id, emp_name, job_id, year(hire_date) "입사년도", salary
from emp
where mgr_id in (select emp_id from emp where emp_name = 'Alexander');

-- where mgr_id = 103, 115 이렇게 피연산자가 데이터 두 개면 비교가 말이 안됨. -> in 연산자 다중행 쿼리로

 

 EX. (다중행. ALL) 

-- 직원 ID(emp.emp_id)가 101, 102, 103 인 직원들 보다! 급여(emp.salary)를 많이 받는! 직원의 모든 정보를 조회.
select * from emp
where salary > all(select salary from emp where emp_id in (101,102,103));
-- 서브쿼리로 조회한 값들이 있는데 이 값 모두보다!! 큰 것을 조회하라. 
-- > 그렇다면 보다 더 많이 받는 상황이 구현됨.

-- 이렇게 풀 수는 있지만. 위처럼 다중행 처리로.
select * from emp
where salary > (select max(salary) from emp where emp_id in (101,102,103));

 

 EX. (다중행. ANY) 

-- 직원 ID(emp.emp_id)가 101, 102, 103 인 직원들 중 급여가 가장 적은!! 직원보다 급여를 많이!! 받는 직원의 모든 정보를 조회.
select * from emp
where salary > any (select salary from emp where emp_id in (101,102,103))
order by salary;
-- 나오는 셋 값 중 하나하고만 맞으면 해당 값 출력. 
-- > 각 값보다 많은 데이터 행들은 모두 조회.
-- > 그렇다면 가장 적은 값에 대해 그보다 더 많이 받는 데이터 행들은 모두 조회되는 상황이 구현됨.

-- EX. GROUP BY 부서별 집계함수avg 데이터 행들이 서브쿼리 결과. 
-- 부서별 급여의 평균중 가장 적은 부서의 평균 급여보다 보다 많이 받는 직원들의 이름, 급여, 업무를 서브쿼리를 이용해 조회
select emp_name, salary, j.*
from emp e left join job j on e.job_id = j.job_id
where salary > any (select avg(salary) from emp group by dept_id);

3.  비상관 쿼리와 상관 쿼리

3.1 비상관 쿼리

- 서브쿼리가 메인 쿼리와 상관없이 일하는 것. 독립적으로 일하는 실행.

  서브 쿼리 처리 완료된 결과값으로 메인쿼리가 처리하는 것. 
  처리 방식 : 1. 서브 쿼리 ---- 완료 ----> 2. 메인쿼리
- 지금까지 한 거.

3.2 상관(연관) 쿼리

- 메인쿼리문의 조회 결과값을 서브쿼리의 조건에서 사용하는 쿼리.
- 메인쿼리를 실행하고 그 결과를 바탕으로 서브쿼리의 조건절을 비교한다.
- 메인 쿼리의 where을 실행하면서 subquery가 같이 실행된다. 

  이때 메인쿼리 where 절에서 비교하는 각 행 데이터들을 가지고 subquery가 실행된다.
- 메인쿼리가 먼저 일하고 그 내 메인이 일하는 도중에 서브쿼리가 일한다. 함께 연관성있게 처리됨.

 

(1) 구문

select emp_name, salary, dept_id
from emp e
where salary = (select max(salary) from emp where dept_id = e.dept_id)

- e.dpet_id는 메인 쿼리의 값. dept_id는 서브쿼리 값이다. 

  지금 db에서 메인 쿼리 emp 들고와 e이라고 별칭 짓어준다. 그리고 서브 쿼리 emp도 들고 온다. 즉,  두 개 들고 왔다.

-- > 메인 쿼리의 테이블의 컬럼 e.dept_id 을 사용하고 있다면 상관쿼리가 된다.

 

[실행 처리 설명]

메인쿼리 먼저 실행

- > 메인쿼리 where절 처리 시작. salary 비교 부분에서부터 서브쿼리가 실행됨.

- > ( where 절은 매 행 마다 처리한다라는 것을 기억하자.) e.dept_id 컬럼의 첫 번째 행 데이터부터 비교 시작한다. 

- > e.dept_id의 첫 행 데이터을 들고 와 그 값과 서브쿼리 emp 테이블의 dept_id 컬럼의 값 중

- > 일치하는 행들을 뽑아 그 행들 중 max(salary) 행을 뽑는다.

- > 그 행의 dept_id가 메인쿼리의 e.dept_id 값과 동일하다면 뽑아내고 다음행으로 넘어간다.

     같지 않다면 바로 다음행으로 넘어간다. 메인쿼리 e.dept_id 컬럼의 다음 행으로

- > 이렇게 메인 쿼리의 e.dept_id 컬럼의 모든 행에 대해 똑같이 처리한다.

 

한 행 처리할 때마다 서브쿼리가 실행하여 메인쿼리 한 행, 한 행씩 비교하는 것이다. 

그러니, 메인쿼리의 행 수 만큼 서브쿼리가 실행된다.

 

3.3  EXISTS, NOT EXISTS 연산자 _ 상관(연관)쿼리와 같이 사용됨

: 서브쿼리의 결과를 만족하는 값이 존재하는지 여부를 확인하는 조건. 

- 조건을 만족하는 행이 여러개라도 딱 한행만 있으면 더이상 검색하지 않는다.

  몇 개인지 보고 싶은 게 아니라 있는지 없는지만 보는 것.

 

(1) 보통 데이터테이블의 값이 내역/이력 테이블(Transaction TB)에 있는지 여부를 조회할 때 사용된다.

  - 어려워서 잘 안 쓰이긴 하는데 내역 조회하는 상황에서 자주 쓰임

         메인쿼리: 데이터테이블
         서브쿼리: 내역테이블

   => 메인쿼리에서 조회할 데이터 테이블의 행이

         서브쿼리의 내역 테이블에 있는지(또는 없는지) 확인 => sub query의 where절
    
(2) 내역테이블(Transaction TB)이란?

  : 일이 진행되는 과정에 대해 저장되어있는 테이블.

 

(3) 사용 예시 상황

- 주문 테이블(내역 테이블)에 고객테이블이 있는데 해당 고객이 주문테이블에 있는 지
고객(데이터-부모)  주문(내역-자식) -> 특정 고객이 주문을 했는지 여부?

    주문 테이블(내역 테이블)과 고객테이블은 부모-자식 관계.

    (주문테이블 내 주문한 고객 테이블의 정보가 있다.)

     내가 찾는 해당 고객이 주문테이블에 있는 지 여부를 확인하는 것이다.

 

- 장비(데이터) 대여(내역) -> 특정 장비가 대여 됐는지 여부?
- 공연장(데이터) 대관(내역) -> 특정 공연장이 대관된 적이 있는 여부

 

(4) 예시 

-- 직원이 한명이상 있는 부서의 부서ID(dept.dept_id)와 이름(dept.dept_name),위치(dept.loc)를 조회
-- 1) 상관 쿼리 : main query의 테이블을 sub query에서 사용.
-- 2) 데이터 : dept, 내역: emp

select dept_id, dept_name, loc
from dept d
where exists (select emp_id from emp where dept_id = d.dept_id);

-- 직원이 한명도 없는 부서의 부서ID(dept.dept_id)와 이름(dept.dept_name), 위치(dept.loc)를 조회
select dept_id, dept_name, loc
from dept d
where not exists (select emp_id from emp where dept_id = d.dept_id);
-- 부서테이블에서 dept_id 데이터로 비교를 하는데 emp 테이블에 이 dept_id의 해당 데이터가 없다면 False반환. 이 없는 데이터 dept_id 조회.

- 서브쿼리에서 emp_id가 조회, 반환되는 게 아니다.

   emp_id는 아무거나 써도 됨. 'a' 20 막 이렇게 써도 됨 의미 없는 값.

- main 쿼리 내 dept 부서테이블의 dept_id 현재 행 데이터 값과 서브쿼리 내 고객테이블 emp의 dept_id가

  같은 것이 있는지 여부 
=> 하나라도 있으면 True 반환하고 끝 ,하나도 없다면 False

 

EX. 

-- 부서(dept)에서 연봉(emp.salary)이 13000이상인 한명이라도 있는 부서의 부서ID(dept.dept_id)와 이름(dept.dept_name), 위치(dept.loc)를 조회
-- 조인 안 쓰고.

select * from dept d -- 메인쿼리에 데이터테이블. 부모.
where exists (select 1 from emp -- 서브쿼리에 내역테이블. 자식
                       where dept_id = d.dept_id -- 직원이 한 명 이상인 부서도 찾고
                             and salary >=13000 ); -- 주어진 조건에 맞는