06 SUBQUERY

ryunyryuny 2023. 10. 13. 18:19

■ subquery(서브쿼리)
- SQL문안에 SELECT문을 서브쿼리라고 한다.
- SELECT문의 서브쿼리는 비교연산자 오른쪽에 괄호()로 묶어야 한다.

110번 사원의 급여보다 더 많이 급여를 받는 사람?
SELECT *
FROM hr.employees
WHERE salary > 110번 사원의 급여;

1)	SELECT salary
	FROM hr.employees
	WHERE employee_id = 110;

2)	SELECT *
	FROM hr.employees
	WHERE salary > 8200 ;

1) + 2) = 서브쿼리
SELECT *
FROM hr.employees
WHERE salary > (SELECT salary
                FROM hr.employees
                WHERE employee_id = 110);

■ 중첩서브쿼리(nested subquery)
1. subquery(inner query) 먼저 수행
2. 1번에서 수행한 값을 가지고 main query(outer query) 수행

┌ main query(outer query)
---------------
SELECT *
FROM hr.employees
WHERE salary > (SELECT salary
                FROM hr.employees
                WHERE employee_id = 110);   -- 단일행 
                -------------------------
                └ subquery(inner query)
                
                
SELECT *
FROM hr.employees
WHERE salary > (SELECT salary
                FROM hr.employees
                WHERE last_name = 'King');  -- 여러행
└ 오류 : 단일 비교연산자를 이용할 때는 단일값만 비교해야한다.

■ 단일행서브쿼리
- 서브쿼리의 결과가 단일값이 나오는 서브쿼리
- 단일행 비교연산자 : =, >, >=, <, <=, <>, !=, ^=

SELECT *
FROM hr.employees
WHERE salary > (SELECT max(salary)
                FROM hr.employees
                WHERE last_name = 'King');     

SELECT *
FROM hr.employees
WHERE job_id = (SELECT job_id
                FROM hr.employees
                WHERE employee_id = 110)
AND salary > (SELECT salary
              FROM hr.employeeS
              WHERE employee_id = 110);

[문제] 최고 급여를 받는 사원들의 정보를 출력.

SELECT *
FROM hr.employees
WHERE salary = (SELECT max(salary)
                FROM hr.employees);

[문제] 최소 급여를 받는 사원들의 정보를 출력.
SELECT *
FROM hr.employees
WHERE salary = (SELECT min(salary)
                FROM hr.employees);

- HAVING 절의 비교연산자 오른쪽에 괄호() 묶어서 서브쿼리 사용한다.

SELECT department_id, sum(salary)
FROM hr.employees
GROUP BY department_id
HAVING sum(salary) > (SELECT max(salary)
		      FROM hr.employees
		      WHERE department_id = 50);

SELECT department_id, max(sum(salary))
FROM hr.employees
GROUP BY department_id;
└> 오류 : 그룹함수를 두번 중첩할 경우 개별컬럼을 사용할 수 없다. ★
	↓
오류 해결방법 : 서브쿼리 사용
SELECT department_id, sum(salary)
FROM hr.employees
GROUP BY department_id
HAVING sum(salary) = (SELECT max(sum(salary))
                        FROM hr.employees
                        GROUP BY department_id);

■ 여러행서브쿼리
- 서브쿼리 결과가 여러개의 값이 나오는 서브쿼리
- 여러행 비교연산자 : IN, ANY, ALL

SELECT *
FROM hr.employees
WHERE salary IN (SELECT min(salary)
FROM hr.employees
GROUP BY department_id); -- 오류

★ ANY
- ANY : OR 범주를 가지고 있다
- (단일비교연산자) ANY

> ANY = 최소값보다 크다

SELECT *
FROM hr.employees
WHERE salary > ANY (SELECT salary
                    FROM hr.employees
                    WHERE job_id = 'IT_PROG');

salary : 4200,4800,4800,6000,9000

↓
SELECT *
FROM hr.employees
WHERE salary > 4200
OR salary > 4800
OR salary > 4800
OR salary > 6000
OR salary > 9000;

↓
SELECT *
FROM hr.employees
WHERE salary > (SELECT min(salary)
                FROM hr.employees
                WHERE job_id = 'IT_PROG');

< ANY = 최대값보다 작다

SELECT *
FROM hr.employees
WHERE salary < ANY (SELECT salary
                    FROM hr.employees
                    WHERE job_id = 'IT_PROG'); 

salary : 4200,4800,4800,6000,9000

↓
SELECT *
FROM hr.employees
WHERE salary < 4200
OR salary < 4800
OR salary < 4800
OR salary < 6000
OR salary < 9000;

↓
SELECT *
FROM hr.employees
WHERE salary < (SELECT max(salary)
                FROM hr.employees
                WHERE job_id = 'IT_PROG');

= ANY : IN 연산자와 동일

SELECT *
FROM hr.employees
WHERE salary = ANY (SELECT salary
                FROM hr.employees
                WHERE job_id = 'IT_PROG'); 

salary : 4200,4800,4800,6000,9000

↓
SELECT *
FROM hr.employees
WHERE salary = 4200
OR salary = 4800
OR salary = 4800
OR salary = 6000
OR salary = 9000;

↓
SELECT *
FROM hr.employees
WHERE salary IN (SELECT salary
                FROM hr.employees
                WHERE job_id = 'IT_PROG');

★ ALL
- ALL : AND 범주를 가지고 있다
- (단일비교연산자) ALL

> ALL : 최대값보다 크다

SELECT *
FROM hr.employees
WHERE salary > ALL (SELECT salary
                    FROM hr.employees
                    WHERE job_id = 'IT_PROG');  

salary : 4200,4800,4800,6000,9000

↓
SELECT *
FROM hr.employees
WHERE salary > 4200
AND salary > 4800
AND salary > 4800
AND salary > 6000
AND salary > 9000;

↓
SELECT *
FROM hr.employees
WHERE salary > (SELECT max(salary)
                FROM hr.employees
                WHERE job_id = 'IT_PROG');

< ALL : 최소값보다 작다

SELECT *
FROM hr.employees
WHERE salary < ALL (SELECT salary
                    FROM hr.employees
                    WHERE job_id = 'IT_PROG');  

salary : 4200,4800,4800,6000,9000

↓
SELECT *
FROM hr.employees
WHERE salary < 4200
AND salary < 4800
AND salary < 4800
AND salary < 6000
AND salary < 9000;

↓
SELECT *
FROM hr.employees
WHERE salary < (SELECT max(salary)
                FROM hr.employees
                WHERE job_id = 'IT_PROG');

= ALL : 논리적으로 의미가 없다.

SELECT *
FROM hr.employees
WHERE salary = ALL (SELECT salary
                    FROM hr.employees
                    WHERE job_id = 'IT_PROG');  

salary : 4200,4800,4800,6000,9000

↓
SELECT *
FROM hr.employees
WHERE salary = 4200
AND salary = 4800
AND salary = 4800
AND salary = 6000
AND salary = 9000;

↓
SELECT *
FROM hr.employees
WHERE salary = (SELECT min(salary)
                FROM hr.employees
                WHERE job_id = 'IT_PROG');

OR 진리표 ★
TRUE OR TRUE        TRUE
TRUE OR FALSE       TRUE
TRUE OR NULL(T,F)   TRUE
FALSE OR NULL(T,F)  NULL

AND 진리표 ★
TRUE  AND TRUE      TRUE
TRUE  AND FALSE     FALSE
TRUE  AND NULL(T,F) NULL
FALSE AND NULL(T,F) FALSE

[문제] 관리자(직속상관)가 아닌 사원들의 정보를 출력.

SELECT *
FROM hr.employees
WHERE employee_id NOT IN (SELECT manager_id
                            FROM hr.employees);
└ 조회안됨

>> NOT IN 연산자를 사용시에 서브쿼리에 NULL 값이 있으면 조회가 안된는 이유?
	TRUE AND NULL(T,F) = NULL
    
manager_id : null,100,102,...

SELECT *
FROM hr.employees
WHERE employee_id <> NULL
AND employee_id <> 100
AND employee_id <> 102
...

↓ 해결

SELECT *
FROM hr.employees
WHERE employee_id NOT IN (SELECT manager_id
                            FROM hr.employees
                            WHERE manager_id IS NOT NULL);

SELECT *
FROM hr.employees
WHERE employee_id <> ALL (SELECT manager_id
                            FROM hr.employees
                            WHERE manager_id IS NOT NULL);

자신의 부서 평균 급여보다 더 많이 받는 사원 ?

SELECT *
FROM hr.employees
WHERE salary > 자신의 부서 평균;

SELECT *
FROM hr.employees
WHERE salary > (SELECT avg(salary)
FROM hr.employees
WHERE department_id = 자신의 부서 코드);

■ 상호관련 서브쿼리(correlated subquery), 상관 서브쿼리
1. main query(outer query) 먼저 수행
2. 첫번째 행을 후보행으로 잡고 후보행값을 서브쿼리에 전달
3. 후보행값을 사용해서 서브쿼리 수행한다.
4. 서브쿼리 결과값을 사용해서 후보행과 비교해서 참이면 그 행을 결과집합(메모리)에 저정, 거짓이면그냥 넘어 간다.
5. 다음행을 후보행으로 잡고 서브쿼리에 전달하고, 2,3,4번 수행, 메인쿼리 행의 수만큼 5번을 반복 수행한다.

SELECT *
FROM hr.employees o
WHERE salary > (SELECT avg(salary)
                FROM hr.employees 
                WHERE department_id = o.department_id);
                                        └ 미지수(변수),후보행값
                └ 서브쿼리가 행의 수만큼 반복이라 비효율적

# 상호관련 서브쿼리의 문제점
- 똑같은 후보행값이 입력되더라도 무조건 서브쿼리는 수행해야 한다.

# 꼭 사용해야하는 경우
값이 있는가 존재하는가 반복 체크가 필요한경우

SELECT department_id, avg(salary)
FROM hr.employees
GROUP BY department_id;

■ INLINE VIEW
- 가상테이블
- FROM 절 괄호 안에 SELECT 문(서브쿼리)를 INLINE VIEW 라고 한다.
- 별칭 사용하기!!

SELECT o.department_id, o.avg_sal, e.*
FROM (SELECT department_id, round(avg(salary)) avg_sal
        FROM hr.employees
        GROUP BY department_id) o, hr.employees e
WHERE o.department_id = e.department_id
AND e.salary > o.avg_sal;

■ EXISTS 연산자 ★
- 상호관련 서브쿼리에서 사용하는 연산자
- 후보행값이 서브쿼리에 존재하는지 여부를 찾는연산자
- 후보행값이 서브쿼리에 존재하면 TRUE 우리가 찾는 데이터 검색종료 후 후보행값을 별도의 메모리에 저장
- 후보행값이 서브쿼리에 존재하지 않으면 FALSE 우리가 찾는 데이터가 아니다.

SELECT *
FROM hr.employees
WHERE employee_id IN (SELECT manager_id
                        FROM hr.employees);
↓
SELECT *
FROM hr.employees e
WHERE EXISTS (SELECT 'X'
                FROM hr.employees
                WHERE manager_id = e.employee_id);

'X' 문법오류만 막을용도로 임의작성

■ NOT EXISTS 연산자 ★
- 후보행값이 서브쿼리에 존재하지 않으면 데이터를 찾는 연산자
- 후보행값이 서브쿼리에 존재하지 않으면 TRUE 우리가 찾는 데이터 별도의 메모리에 저장
- 후보행값이 서브쿼리에 존재하면 FALSE 우리가 찾는 데이터가 아니다.

SELECT *
FROM hr.employees
WHERE employee_id NOT IN (SELECT manager_id
                            FROM hr.employees
                            WHERE manager_id IS NOT NULL);
↓
SELECT *
FROM hr.employees e
WHERE NOT EXISTS (SELECT 'X'
                FROM hr.employees
                WHERE manager_id = e.employee_id);

[문제] 소속사원이 있는 부서 정보를 출력
1) IN
SELECT *
FROM hr.departments d
WHERE department_id IN (SELECT department_id
                        FROM hr.employees);

2) EXISTS
SELECT *
FROM hr.departments d
WHERE EXISTS (SELECT 'X'
                FROM hr.employees
                WHERE department_id = d.department_id);

[문제] 소속사원이 없는 부서 정보를 출력
1) NOT IN
SELECT *
FROM hr.departments d
WHERE department_id NOT IN (SELECT department_id
                            FROM hr.employees
                            WHERE department_id IS NOT NULL);

2) NOT EXISTS
SELECT *
FROM hr.departments d
WHERE NOT EXISTS (SELECT 'X'
                FROM hr.employees
                WHERE department_id = d.department_id);

[문] 각 부서별 가장 적은 월급을 받는 직원의 모든 데이터 출력.

SELECT o.department_id, o.급여, e.*
FROM (SELECT department_id, min(salary) 급여
      FROM hr.employees
      GROUP BY department_id) o, hr.employees e
WHERE o.department_id = e.department_id
AND e.salary <= o.급여;

# 자신이 속한 지역 평균 급여보다 많이 받는 사원 데이터를 출력.

SELECT o.location_id, o.지역평균, s.last_name, s.salary
FROM (SELECT d.location_id, round(avg(e.salary)) 지역평균
        FROM hr.employees e JOIN hr.departments d
        ON e.department_id = d.department_id
        GROUP BY d.location_id) o, hr.employees s, hr.departments dd
WHERE s.department_id = dd.department_id
AND dd.location_id = o.location_id
AND s.salary > o.지역평균;

[문제] 2006년도에 입사한 사원들의 job_id와 동일한 사원들의 job_id별 급여의 총액 중에
    50000 이상인 값만 출력해주세요.

SELECT job_id, sum(salary)
FROM hr.employees
WHERE job_id IN (SELECT job_id
                    FROM hr.employees
                    WHERE hire_date >= to_date('2006-01-01','yyyy-mm-dd')
                    AND hire_date < to_date('2007-01-01','yyyy-mm-dd'))
GROUP BY job_id
HAVING sum(salary) >= 50000;

1. 2006년 입사자의 job_id -> 서브쿼리
2. 위와 동일한 job_id -> IN
3. sum(salary)
4. >= 50000

[문제] location_id 가 1700인 모든 사원들의 last_name, department_id, job_id를 
    출력해주세요.
1) 조인
SELECT e.last_name, e.department_id, e.job_id
FROM hr.employees e JOIN hr.departments d
ON e.department_id = d.department_id
WHERE d.location_id = 1700;
||
SELECT e.last_name, e.department_id, e.job_id
FROM hr.employees e, hr.departments d
WHERE e.department_id = d.department_id
AND d.location_id = 1700;
||
SELECT e.last_name, department_id, e.job_id
FROM hr.employees e JOIN hr.departments d
USING (department_id)
WHERE d.location_id = 1700;

2) 서브쿼리
SELECT last_name, department_id, job_id
FROM hr.employees
WHERE department_id IN (SELECT department_id
                        FROM hr.departments
                        WHERE location_id = 1700);

[문제] 60번 부서 사원들의 급여 보다 더 많은 급여를 받는 사원들의 정보를 출력해주세요.

SELECT *
FROM hr.employees
WHERE salary > (SELECT max(salary)
                FROM hr.employees
                WHERE department_id = 60);
||
SELECT *
FROM hr.employees
WHERE salary > ALL (SELECT salary
                FROM hr.employees
                WHERE department_id = 60);

[문제] Executive 부서이름의 소속된 모든 사원에 대한 employee_id, last_name, job_id를 출력해주세요.

1) 조인
SELECT employee_id, last_name, job_id
FROM hr.employees e JOIN hr.departments d
ON e.department_id = d.department_id
WHERE d.department_name = 'Executive';
||
SELECT employee_id, last_name, job_id
FROM hr.employees e JOIN hr.departments d
USING (department_id)
WHERE d.department_name = 'Executive';

2) 서브쿼리
SELECT employee_id, last_name, job_id
FROM hr.employees
WHERE department_id = (SELECT department_id
                        FROM hr.departments
                        WHERE department_name = 'Executive');

[문제] job_grades 테이블에서 사원들의 급여 등급에 포함된 등급정보를 출력해주세요.

SELECT *
FROM hr.job_grades J
WHERE EXISTS (SELECT 'X'
                FROM hr.employees
                WHERE salary BETWEEN lowest_sal AND highest_sal);
>> 서브쿼리 내 값이 hr.job_grades J 안에 존재한다면 TRUE

SELECT *
FROM hr.job_grades
WHERE grade_level IN (SELECT j.grade_level
                        FROM hr.employees e, hr.job_grades j
                        WHERE e.salary BETWEEN lowest_sal AND highest_sal);
||
SELECT *
FROM hr.job_grades
WHERE grade_level = ANY (SELECT grade_level
                        FROM hr.employees e, hr.job_grades j
                        WHERE e.salary BETWEEN lowest_sal AND highest_sal);

[문제] job_grades 테이블에서 사원들의 급여 등급에 포함되지 않은 등급정보를 출력해주세요.

SELECT *
FROM hr.job_grades J
WHERE NOT EXISTS (SELECT 'X'
                    FROM hr.employees
                    WHERE salary BETWEEN lowest_sal AND highest_sal);
||
SELECT *
FROM hr.job_grades
WHERE grade_level NOT IN (SELECT j.grade_level
                            FROM hr.employees e, hr.job_grades j
                            WHERE e.salary BETWEEN lowest_sal AND highest_sal);

'Oracle SQL' 카테고리의 다른 글

08 집합연산자, 그룹화, 계층검색 (0)	2023.10.17
07 PIVOT, UNPIVOT, SCALAR SUBQUERY (0)	2023.10.16
05 JOIN (0)	2023.10.12
04 Null, Case, Decode, 그룹함수, GROUP BY, HAVING (0)	2023.10.11
03 숫자함수, 날짜함수, 형변환함수 (0)	2023.10.10

'Oracle SQL'의 다른글

현재글06 SUBQUERY

Record.

06 SUBQUERY

'Oracle SQL' 카테고리의 다른 글

티스토리툴바