[SQL] SubQuery : 서브 쿼리 문법 정리 및 예제

# fullstack/database/p240611/select_subquery.sql

 

서브 쿼리는 사용 위치에 따라 중첩 서브 쿼리, 스칼라 서브 쿼리, 인라인 뷰로 구분됨

[서브 쿼리] subquery
* 서브 쿼리 : 쿼리문 내 쿼리문
select 절, from 절, where 절에 들어올 수 있음
들어오는 위치에 따라 구분이 됨

* 스칼라 서브쿼리 (scalar subquery) 
: select 절, 단일행 / 단일컬럼 반환

* 인라인 뷰 (inline view) 	   
: from 절, 반드시 하나의 테이블 반환 

* 중첩 서브쿼리 (nested subquery)   
: where 절, 단일행 or 다중행 반환	  

// 가상의 테이블를 뷰라고 말함, 쿼리가 실행되면서 만들어지는 뷰

 

중첩 서브 쿼리

d005 부서에 근무하고 있는 사원들의 사번과 이름 조회

1) 사번과 이름 조회

# 1) 사번과 이름 조회
select emp_no, first_name
from employees;

이미지에서는 d001 인데 d005 로 함

 

2) d005 부서에 근무하는 사원의 사번, 부서번호

# 2) d001 부서에 근무하는 사원의 사번, 부서번호
select emp_no, dept_no
from dept_emp;

이미지에서는 d001 인데 d005 로 함

 

전체 직원을 다 가져 왔으니 d005 부서만 가져오자

select emp_no, dept_no
from dept_emp
where dept_no like 'd005';

이미지에서는 d001 인데 d005 로 함

 

3) 서브쿼리 작성

여러 값을 비교하기 위해서 in 연산자를 사용하고, 여러 값을 넣기 위해 ()  사용 !

select emp_no, first_name
from employees
where emp_no in (select emp_no from dept_emp where dept_no like 'd005');

이미지에서는 d001 인데 d005 로 함

cf) 위 서브쿼리를 조인으로 변경

웬만하면 기본키에 설정되어 있는 테이블명을 명시하는 것이 좋음
dept_emp 테이블에는 key 가 2개 즉, 복합키 !

속도 처리를 위해 employees 테이블에 있는 emp_no 지정해주기 ~

select em.emp_no, first_name
from employees em , dept_emp dp
where em.emp_no = dp.emp_no  # 조인 조건
	and dept_no like 'd005'; # 조건

main 쿼리와 서브 쿼리 

메인 쿼리와 서브 쿼리 ! 이렇게 구분합시다 !

인라인 뷰

• from 절에 위치한 서브 쿼리
• 테이블의 일부 데이터만 불러와 복합한 쿼리 단순화
• 쿼리 가독성 높이
• 결과는 반드시 하나의 테이블 리턴
• 인라인 뷰는 SQL 문이 실행될 때, 임시적으로 생성되는 동적인 뷰 (Dynamic View)
  ↔ 정적인 뷰(Static View)
• 단점 액섹스 속도 저하 가능성
  실행이 되는 쿼리문안에 서브쿼리가 또 실행이 되기 때문에 join 을 하거나 값 비교 등등 이유

매니저의 사번, 부서 번호, 부서명 조회

1) 매니저 사번, 부서번호 조회

# 1) 매니저 사번, 부서번호 조회
select emp_no, dept_no
from dept_manager;

2) 부서번호, 부서명 조회

# 2) 부서번호, 부서명 조회
select dept_no, dept_name
from departments;

 

3) 인라인 뷰

# 3) 인라인 뷰
select emp_no, dm.dept_no
from dept_manager dm, (select dept_no, dept_name from departments) as d;

 

# 3) 인라인 뷰
select emp_no, dm.dept_no
from dept_manager dm, (select dept_no, dept_name from departments) as d
where dm.dept_no = d.dept_no;

 

일부 데이터만 가져올 수도 있음 !

# 3) 인라인 뷰
select emp_no, dm.dept_no
from dept_manager dm, (select dept_no, dept_name
                        from departments 
                        where dept_no in ('d001', 'd002')) as d
where dm.dept_no = d.dept_no;

 

d001 부서에 근무하고 있는 사원들의 사번과 이름 조회 

메인 쿼리 내에 들어올 서브 쿼리는 () 안에 넣어주기 !

select emp_no, first_name
from employees em, ()

 

# d001 부서에 근무하고 있는 사원들의 사번과 이름 조회 
select em.emp_no, first_name
from employees em, (select emp_no, dept_no from dept_emp where dept_no like 'd001') as dp
where em.emp_no = dp.emp_no; # 조인 조건

스칼라 서브쿼리

• 권장 x
• 반드시 단일 행, 단일 컬럼 반환
• 단일 행, 단일 컬럼이 오기 위해서는 경우의 수가 엄청나게 많기 때문에 생각하고 써야 함

없는 컬럼을 넣어주자 !

 

 

# 매니저 사번, 부서번호, 부서명 조회
select dept_no, emp_no, (select dept_name from departments where dept_no = dept_manager.dept_no) as dept_name
from dept_manager;

 

하나의 단일 행, 컬럼만 가능 

여러 값이 오면 error !

 

[문제 1] 각 사원의 사번, 이름, 연봉 합 조회

1-1) 스칼라 서브쿼리

사원의 사번, 이름 조회

select emp_no '사번', first_name '이름'
from employees;

 

사원별 연봉합

select emp_no, sum(salary)
from salaries
group by emp_no;

 

스칼라 서브쿼리는 select 절에 오며 () 안에 작성해줘야 함 !

select emp_no '사번', first_name '이름', () '연봉합'
from employees;

 

그럼 사원별 연봉합을 넣어주자

근데 저대로 넣으면 error ! Subquery returns more than 1 row

# 그룹 x
select emp_no '사번', first_name '이름', 
	(select sum(salary) from salaries where emp_no = employees.emp_no) '연봉합'
from employees;

# 그룹 o
select emp_no '사번', first_name '이름', 
	(select sum(salary) from salaries where emp_no = employees.emp_no group by emp_no ) '연봉합'
from employees;

1-2) 인라인 뷰

인라인뷰는 from 절에 옴 !

select em.emp_no '사번', first_name '이름'
from employees em, ()

 

from 절에 추가 !

다대다 조인

sum(salary) 가 가공된 데이터라 as 로  'sum_salary' 해줘야 select 절에서 가져올 수가 있다

select em.emp_no '사번', first_name '이름', sum_salary '연봉합'
from employees em, 
	# 다대다 조인
    (select emp_no, sum(salary) 'sum_salary' from salaries group by emp_no) as s 
where em.emp_no = s.emp_no;

근데 여기서 format 함수를 사용할 수가 있다 !

select em.emp_no '사번', first_name '이름', sum_salary '연봉합'
from employees em, (select emp_no, format(sum(salary), '#,###') 'sum_salary'  # 다대다 조인
						from salaries 
						group by emp_no) as s 
where em.emp_no = s.emp_no;

 

 

[문제 2] d001 부서에 근무하고 있는 사원들의 사번, 이름, 부서번호 조회

2-1) 스칼라 서브쿼리, 중첩 서브 쿼리

사원들의 사번, 이름 - main 쿼리 작성

select emp_no, first_name 
from employees;

 

d001 부서에 근무하고 있는 사원들의 사번

select emp_no, dept_no
from dept_emp
where dept_no like 'd001';

 

d001 부서에 근무하고 있는 사원들의 사번, 이름 - 중첩 서브 쿼리

select emp_no, first_name
from employees
where emp_no in (select emp_no from dept_emp where dept_no like 'd001');

 

d001 부서에 근무하고 있는 사원들의 사번, 이름, 부서번호 조회 - 스칼라 서브쿼리

select emp_no '사번', first_name '이름', 
	(select dept_no 
		from dept_emp de 
        where e.emp_no = de.emp_no and to_date = '9999-01-01') '부서번호'
from employees e
where emp_no in (select emp_no from dept_emp where dept_no like 'd001');

 

근데 너무 복잡하고, 보기 어려움 , , , 

인라인뷰로 바꾸기 ~ ㄱㅏ독성이 좋음 !

select e.emp_no, first_name, dept_no
from employees e, (select emp_no, dept_no from dept_emp where dept_no like 'd001') d
where e.emp_no = d.emp_no; # join 조건

select e.emp_no, first_name, dept_no
from employees e, dept_emp d
where e.emp_no = d.emp_no # join 조건
	and dept_no like 'd001'
    and to_date = '9999-01-01';

 

[문제 3] 1960년 이후 태어난 사원들의 사원 번호, 부서 번호 조회 

1) 인라인 뷰

조인 조건을 해도 무조건 묶여지지 않다 (이거 공부 . . .)

select de.emp_no, de.dept_no
from dept_emp de, 
	(select emp_no from employees where birth_date >= '1960-01-01') as em
where de.emp_no = em.emp_no; # 조인 조건

 

년도만 뽑아서 비교할 수 있는데 (가공) 모든 데이터를 비교하다보니 시간이 걸려 비추

# year 함수를 써서 가공됨 -> 모든 데이터에 적용하느라 시간이 걸림, 비추
select de.emp_no, de.dept_no
from dept_emp de, 
	(select emp_no from employees where year(birth_date) >= 1960) as em
where de.emp_no = em.emp_no; # 조인 조건

2) 중첩 서브쿼리

select emp_no, dept_no
from dept_emp
where emp_no in (select emp_no from employees where birth_date >= '1960-01-01');