DAY-25. Oracle, 서브쿼리

🔐 2022-04-05

데이터베이스

❗ 수업을 듣고, 개인이 공부한 내용을 적은 것 이기에 오류가 많을 수도 있음

2022-04-05

🔔 set Operator (집합 연산자)

• 두 개 이상의 테이블을 조인 없이 연관된 데이터를 조회하는 방식
• 각 테이블에서 반환된 결과값을 하나의 테이블로 결합하는 방식
• 여거 개의 sql문을 사용해서 하나의 테이블로 결과를 반환해야 하는 경우

조건

1. select 절에서 조회하려는 ‘컬럼의 수가 동일’
2. select 절의 종일 위치에 존재하는 컬럼의 ‘데이터 타입이 상호 호환 가능’

UNION(합집합)

중복된 데이터를 제거하고 첫번째 컬럼을 기준으로 오름차순하여 데이터를 보여 줌

• 각 테이블에서 조회하는 컬럼수가 다르면 에러 발생
• 조회하려는 컬럼이 서로 상호호환 불가한(같은 타입이 아니면) 데이터이면 union 사용 x

💡 UNION은 컬럼의 수와 데이터가 같아야 사용이 가능하다

SELECT no_a from A // 1,5,3,4,2
SELECT no_b from B; // 7,6,3,8,5,9

SELECT no_a from A
UNION
SELECT no_b from B;

// 1,2,3,4,5,6,7,8,9 (중복된 데이터는 제거 되고 출력)

UNION ALL (합집합)

종복된 데이터제거를 안하고 , 정렬 없이 데이터를 그대로 보여준다

→ 조회된 결과값의 첫번째 테이블 아래로 두번째 테이블 결과값을 그대로 이어붙이는 식.

SELECT no_a from A // 1,5,3,4,2
SELECT no_b from B; // 7,6,3,8,5,9

SELECT no_a from A
UNION ALL
SELECT no_b from B;  // 5 3 4 2 7 6 3 8 5 9

INTERSECT (교집합)

두 테이블의 쿼리 결과 중 공통된 결과 값만 보여 줌.

SELECT no_a from A // 1,5,3,4,2
SELECT no_b from B; // 7,6,3,8,5,9

SELECT no_a from A
INTERSECT
SELECT no_b from B;     // 3 5

MINUS (차집합)

두 테이블의 쿼리 결과 중 공통된 요소를 뺀 한 테이블의 결과값만 보여줌

SELECT no_a from A // 1,5,3,4,2
SELECT no_b from B; // 7,6,3,8,5,9

SELECT no_a from A
MINUS
SELECT no_b from B;  // 1,2,4 (중복 제거 후 A부분을 보여줌)

SELECT no_b from B
MINUS
SELECT no_a from A;  // 6,7,8,9(중복 제거 후 B부분을 보여줌)

🔔 서브쿼리 (SubQuery)

하나의 sql 문안에 포함된 다른 sql문 ()안에 sql 문 삽입

→ 메인 쿼리가 서브 쿼리를 포함하는 종속적인 관계

→ 소괄호로 묶어줌 / 연산자의 오른쪽에 위치 / order by 절은 subquery내에서 사용 불가

select
    emp_id, emp_name
from employee
    where emp_id = (select manager_id from employee where emp_name ='전지연');

// select manager_id from employee where emp_name ='전지연'의 값이 214가 도출되어
// where emp_id = 214가 된다.

단일행 서브쿼리

서브쿼리 조회 결과 값이 1개(행)일 경우

🔎
// 전 직원의 급여 평균보다 급여를 많이 받는 사원의 사번, 사원명, 직급코드, 월급 조회


select
    emp_id , emp_name , job_code , salary
from employee
    where salary > 3047662;

// 위 두개의 쿼리문을 하나로

select
    emp_id , emp_name , job_code , salary
from employee
    where salary > (select  floor(avg(salary)) from employee);

🔎
// 윤은해 직원과 급여가 같은 사원들을 검색 -> 사번, 사원명, 급여 출력

select
    emp_id, emp_name, salary
from employee
    where salary = (select salary from employee where emp_name = '윤은해')
            and emp_name != '윤은해';

다주행 서브쿼리

서브쿼리 조회 결과가 여러개(여러 행) 일 때

→ 다중행 서브쿼리 앞에는 = , ! = 연산자 사용 불가

→ in, not in, any, all, exists 등을 이용

🔎
//차태연, 전지연 사원의 급여등급(sal_level)과 같은 등급을 가진 사원의 직급명, 사원명, 급여등급 출력

select * from job;
select emp_name , sal_level from employee where emp_name in('차태연','전지연');
select emp_name, sal_level from employee where sal_level in ('S5','S4');

select
    job_name "직급명",
    emp_name "사원명",
    sal_level "급여등급"
from employee join job
     using(job_code)
where sal_level in (select sal_level from employee where emp_name in('차태연','전지연'));

any

서브 쿼리 결과 중에 하나라도 참이라고 한다면 참 (or)

값 > any(1,2,3) : 왼쪽에 있는 값이 오른쪽의 최소값보다 크면 된다.-> 값이 1 보다 크면 참
값 < any(1,2,3) : 왼쪽에 있는 값이 오른쪽의 최대값보다 작으면 된다 -> 값이 3보다 작으면 참
값 = any(1,2,3) : in과 같은 의미
값 != any(1,2,3) : not in 과 같은 의미

🔎
// 급여가 200만원 혹은 300만원보다 큰 사람의 사원명, 급여 출력

select
    emp_name,salary
from employee
    where salary > any(2000000,3000000); // salary가 2000000보다 크면 출력

🔎
// D1, D5의 부서코드를 가진 사원들의 급여보다 적게 받는 사원들의 사원명, 급여, 부서코드 출력
// D1, D5 부서 직원은  출력 X
// 정렬은 부서코드 기준 오름차순 정렬

// 1 번째 방법
select
    emp_name, salary, nvl(dept_code,'인턴') "부서코드"
from employee
where salary < any(select salary from employee where dept_code in('D1','D5'))
        and dept_code not in ('D1','D5') or dept_code is null
															// null 값도 출력해주는 코드 dept_code is null
order by 3;

// 2 번째 방법
select
    emp_name, salary, dept_code "부서코드"
from employee
where salary < any(select salary from employee where dept_code in('D1','D5'))
        and nvl(dept_code,'없음') not in ('D1','D5')
order by 3;

💡 null 값 비교는 is null 혹은 is not null로만 비교 가능

all

서브 쿼리 결과 중에 모든 것이 참이여야만 함

값 > all(1,2,3) : 왼쪽에 있는 값이 오른쪽에 있는 최대값 보다 커야한다
값 < all(1,2,3) : 왼쪽에 있는 값이 오른쪽에 있는 최소값 보다 작아야한다
값 = all(1,2,3) : 왼쪽에 있는 값이 오른쪽에 있는 값의 모두와 똑같아야한다 -> and 연산자

다중열 서브쿼리

서브쿼리 조회 결과 없이 여러개(열) 일 때

🔎
// 퇴사한 여직원 -> 같은 부서, 같은 직급에 해당하는 사원의 사원명, 직급코드, 부서코드, 입사일 조회

select dept_code, job_code
from employee
    where ent_yn ='Y'
    and substr(emp_no,8,1) = 2;

select
    emp_name, job_code, dept_code, hire_date
from employee
    where dept_code ='D8' and job_code = 'J6';

select
    emp_name, job_code, dept_code, hire_date
from employee
    where (dept_code ,job_code) in (select dept_code, job_code from employee where ent_yn ='Y' and substr(emp_no,8,1) = 2);

• dept_code와 job_code → 열이 두개다 → 다중열 서브쿼리 이용 → where 절 안에 () in ()으로 활용

🔎
기술지원부이면서 급여가 200만원인 지원의 사원명, 부서코드, 급여, 부서의 지역명 출력

select * from employee join department on(dept_code = dept_id) where dept_title ='기술지원부' and salary = 2000000;

// 방법 1
select
    emp_name,dept_code,salary,local_name
from employee join department
        on(dept_code = dept_id) join location
        on(location_id = local_code)
where dept_title ='기술지원부' and salary = 2000000;

// 방법 2 -> department를 연결 키로 해서 사용
select
    emp_name,dept_code,salary,local_name,local_code
from employee, location
    where (dept_code, local_code) in (select dept_id, location_id from department where dept_title = '기술지원부')
    and salary = 2000000;

// employee와 location은 직접 연결한 키가 없기에 department사용

• 서브쿼리를 이용한 테이블 생성

join에서도 group by 가능

//부서코드, 부서명, 부서별 평균급여, 부서별 인원수

select
	nvl(dept_code,'없음') "부서코드",
	nvl(dept_title,'없음') "부서명",
	floor(avg(salary)) "부서별 평균급여",
	count(*) "부서별 인원수"
from employee left outer join department
		on(dept_code = dept_id)
group by dept_code, dept_title  // dept_code로만 하면 오류 발생 dept_title(department에 있음)도 필요
order by 1;