110번 사원의 급여보다 더 많은 급여를 받는 사원 정보 추출

해당 정보를 추출하기 위해서는 다음과 같은 과정을 거쳐야 한다.

1. 110번 사원의 급여 추출
2. 추출한 값을 기준으로 비교

-- 1)
SELECT salary -- 8200
FROM hr.employees
WHERE employee_id = 110;
-- 2)
SELECT *
FROM hr.employees
WHERE salary> 8200;

이런 과정을 한번에 처리할 수 없기 때문에 우리는 서브쿼리를 이용하여 해결한다.

실행 과정이 서브쿼리 실행 후 메인쿼리를 실행하며 이를 중첩서브쿼리(nested subqeury)라고 한다.

단일행 서브쿼리

서브쿼리의 결과가 단일 값인 서브쿼리를 의미한다.
단일행 비교 연산자를 사용하는 서브쿼리로 단일행 비교연산자의 종류는 다음과 같다.

• =, >, >=, <, <=, <>, !=, ^=

select * -- main query, outer query
from hr.employees
where salary > ( -- sub query, inner query
        select salary
        from hr.employees
        where employee_id = 110
);

복수행 서브쿼리

서브쿼리의 결과가 복수의 값인 서브쿼리를 의미한다.
복수행 비교 연산자를 사용하는 서브쿼리로 단일행 비교연산자의 종류는 다음과 같다.

• IN, ANY, ALL

IN

• = or 연산을 진행한다.

select *
from hr.employees
where salary in (
        SELECT min(salary)
        from hr.employees
        group by department_id);

NOT IN의 주의할 점

• != and 연산을 진행한다.
• NOT IN에서 서브쿼리에 null값이 존재하면 정상적인 결과를 반환하지 않는다.

-- 잘못된 결과 반환
select *
from hr.employees
where employee_id not in (select manager_id from hr.employees);

위 코드를 풀어보면...

select *
from hr.employees
where employee_id != null and ...

여기서 != 연산자와 null이 사용되었는데 null은 비교할 수 없는 자료형이기 때문에
employee_id != null연산 자체가 null을 반환한다.(is not null과 다름)

이후 null과 and 연산자가 수행되면 진리표에 의해 null을 반환하기 때문에

select *
from hr.employees
where null

과 같은 코드가 수행되어 정상적인 결과를 도출해 내지 못한다.

따라서 서브쿼리에서 null을 다음과 같이 제거하는 과정을 수행해야 한다.

select *
from hr.employees
where employee_id not in (
		select manager_id 
        from hr.employees 
        where manager_id is not null);

NULL, AND 진리표

null은 알 수 없는 값, 즉 나중에 들어올 수 있는 데이터

T AND T = True
T AND False = False
T AND NULL = NULL
FALSE AND NULL = False

NULL, OR 진리표

T or T = True
T or False = True
T or NULL = TRUE
FALSE or NULL = NULL

ANY

OR의 범주를 가지고 있으며 단일행 비교 연산자와 같이 사용된다.

• > ANY : 최솟값 보다 크다 의미 ( 어떤 것 보다 크다. )
• < ANY : 최댓값 보다 작다 의미 ( 어떤 것 보다 작다. )
• = ANY : IN과 같은 의미

select *
from hr.employees
where salary > ANY (
        SELECT salary
        from hr.employees
        where job_id = 'IT_PROG');
-- 서브쿼리의 결과보다 큰 어떠한, 모든 데이터 반환 ( > or > or > or ...)

select *
from hr.employees
where salary > (
        SELECT min(salary)
        from hr.employees
        where job_id = 'IT_PROG');
-- 두 쿼리문의 결과 값과 같다.

ALL

AND의 범주를 가지고 있으며 단일행 비교 연산자와 같이 사용된다.

• > ALL : 최댓값 보다 크다. ( 무엇보다 크다 )
• < ALL : 최솟값 보다 작다. ( 무엇보다 작다 )
• = ALL : 일치하는 결과값 없음 (모든 값과 일치한다)

select *
from hr.employees
where salary > ALL (
        SELECT salary
        from hr.employees
        where job_id = 'IT_PROG');
-- 서브쿼리의 모든 결과보다 큰 데이터 반환 ( > and > and > and ... )

select *
from hr.employees
where salary > (
        SELECT min(salary)
        from hr.employees
        where job_id = 'IT_PROG');
-- 두 쿼리문의 결과 값과 같다. 

HAVING과 SUBQUERY

HAVING 절에도 SUB쿼리를 사용할 수 있따.

SELECT      department_id, sum(salary)
FROM        hr.employees
GROUP BY    department_id
HAVING      SUM(salary) > (
                SELECT  min(salary)
                FROM    hr.employees
                WHERE   department_id = 40);
                
-- 연봉의 평균이 가장 작은 부서의 id와 연봉의 평균
SELECT  department_id, round(avg(salary),1)
FROM    hr.employees
GROUP BY department_id
HAVING  AVG(salary) = (
SELECT  min(avg(salary))
FROM    hr.employees
GROUP BY department_id);