7. [Oracle] Subquery를 사용하여 Query 해결해보자.

-Subquery를 사용하여 문제 해결

Abel보다 급여가 많은 사람을 찾는 query를 작성한다고 가정해 보겠습니다.

 

이 문제를 해결하려면 두 개 의 query가 필요합니다. 하나는 Abel이 받는 급여액을 찾는 query이고 또 하나는 이 액수보다 많은 급여를 받는 사람을 찾는 query입니다. 한 query를 다른query 내부에 배치하는 방식으로 두 query를 결합하여 이 문제를 해결할 수 있습니다.

 

inner query(또는 subquery)는 outer query(또는 main query)에서 사용되는 값을 반환합니다. subquery를 사용하는 것은 두 query를 순차적으로 수행하여 첫번째 query 결과를 두번째 query의 검색 값으로 사용하는 것과 동일한 기능입니다.

 

-Subquery 구문

SELECT select_list

FROM table

WHERE expr operator (SELECT select_list FROM table);

 

• subquery(inner query)는 main query(outer query) 전에 실행됩니다.

• Subquery 결과는 main query에서 사용됩니다.

 

subquery는 다른SELECT 문의 절에 포함되는 SELECT 문입니다. Subquery를 사용하면 단순하면서도 강력한 명령문을 구축할 수 있습니다. subquery는 테이블 자체의 데이터에 종속되는 조건을 사용하여 테이블에서 행을 선택해야 하는 경우에 매우 유용합니다.

 

-Subquery 사용

SELECT last_name, salary

FROM employees

WHERE salary > (SELECT salary FROM employees WHERE last_name = 'Abel');

 

서 inner query는 사원Abel의 급여를 판별합니다. Outer query는 inner query의 결과를 가져와서 Abel 사원보다 많은 급여를 받는 모든 사원을 표시합니다.

 

-Subquery 사용 지침

• subquery는 괄호로 묶습니다.

• 가독성을 위해 비교 조건의 오른쪽에 subquery를 배치합니다. 그러나 subquery는 비교 연산자의 양쪽 어디에나 사 용할 수 있습니다 .

• 단일 행 subquery에는 단일 행 연산자를 사용하고 여러 행 subquery에는 여러 행 연산자를 사용합니다.

 

-Subquery 유형

• 단일 행 subquery: 내부 SELECT 문에서 하나의 행만 반환하는 query

• 여러 행 subquery: 내부 SELECT 문에서 두 개 이상의 행을 반환하는 query

 

-단일 행 Subquery

단일 행 subquery는 내부SELECT 문에서 한 행만 반환하는 query입니다. 이러한 유형의 subquery는 단일 행 연산자를 사용합니다. 슬라이드는 단일 행 연산자 리스트를 보여줍니다.

 

SELECT last_name, job_id, salary

FROM employees

WHERE job_id = (SELECT job_id FROM employees WHERE last_name = 'Taylor')

AND salary > (SELECT salary FROM employees WHERE last_name = 'Taylor');

 

SELECT 문은 query 블록으로 간주할 수 있습니다. 슬라이드의 예제는 "Taylor"와 직무가 같고 급여는 더 많은 사원을 표시합니다.

 

예제는 세 개의query 블록(outer query와 두 개의inner query)으로 구성됩니다. inner query 블록이 먼저 실행되어 각각 SA_REP 및 8600이라는 query 결과를 생성합니다. 그런 다음 outer query 블록이 처리되고 inner query에서 반환된 값을 사용하여 검색 조건을 완성합니다.

 

두 inner query는 모두 단일 값 (각각 SA_REP 및 8600)을 반환하므로 이러한 SQL 문을 단일 행 subquery라고 합니다.

 

-Subquery에서 그룹 함수 사용

SELECT last_name, job_id, salary

FROM employees

WHERE salary = (SELECT MIN(salary) FROM employees);

 

subquery에서 그룹 함수를 사용하여 단일 행을 반환하는 방식으로 main query에서 데이터를 표시할 수 있습니다. subquery는 괄호로 묶이고 비교 조건 뒤에 배치됩니다. 슬라이드의 예제는 급여가 최저 급여와 같은 모든 사원의 성, 직무 ID 및 급여를 표시합니다. MIN 그룹 함수는 outer query로 단일 값 (2500)을 반환합니다.

 

-Subquery가 있는HAVING 절

• Oracle 서버는 subquery를 먼저 실행합니다.

• Oracle 서버는 main query의 HAVING 절로 결과를 반환합니다.

 

SELECT department_id, MIN(salary)

FROM employees

GROUP BY department_id HAVING MIN(salary) > (SELECT MIN(salary) FROM employees WHERE department_id = 50);

 

WHERE 절뿐만 아니라 HAVING 절에서도 subquery를 사용할 수 있습니다. Oracle 서버가 subquery를 실행하고 그 결과는 main query의 HAVING 절로 반환됩니다. SQL 문은 부서 50보다 최저 급여가 많은 모든 부서를 표시합니다.

 

-여러 행 Subquery

• 두 개 이상의 행을 반환합니다.

• 여러 행 비교 연산자를 사용합니다.

 

두 개 이상의 행을 반환하는 subquery를 여러 행subquery라고 합니다. 여러 행 subquery에는 단일 행 연산자 대신 여러 행 연산자를 사용합니다. 여러 행 연산자는 하나 이상의 값을 예상합니다.

 

-여러 행 Subquery에서 ANY 연산자 사용

SELECT employee_id, last_name, job_id, salary

FROM employees

WHERE salary < ANY (SELECT salary

FROM employees

WHERE job_id = 'IT_PROG')

AND job_id <> 'IT_PROG';

 

ANY 연산자 및 그 동의어인 SOME 연산자는 값을 subquery에서 반환되는 각 값과 비교합니다. 슬라이드의 예제는 IT 프로그래머가 아닌 사원 중 급여가 IT 프로그래머보다 적은 사원을 표시합니다. 프로그래머가 받는 최고 급여는 $9,000입니다.

• <ANY는 최대값보다 작음을 의미합니다.

• >ANY는 최소값보다 큼을 의미합니다.

• =ANY는 IN과 같습니다.

 

-여러 행 Subquery에서 ALL 연산자 사용

SELECT employee_id, last_name, job_id, salary

FROM employees

WHERE salary < ALL (SELECT salary

FROM employees

WHERE job_id = 'IT_PROG')

AND job_id <> 'IT_PROG';

ALL 연산자는 값을 subquery에서 반환되는 모든 값과 비교합니다. 슬라이드의 예제는 직무 ID가 IT_PROG인 모든 사원보다 급여가 적고 직무가 IT_PROG가 아닌 사원을 표시합니다. >ALL은 최대값보다 큼을 의미하고 <ALL은 최소값보다 작음을 의미합니다. NOT 연산자는 IN, ANY 및 ALL 연산자와 함께 사용할 수 있습니다.

 

-EXISTS 연산자 사용

SELECT * FROM departments

WHERE NOT EXISTS

(SELECT * FROM employees WHERE employees.department_id=departments.department_id);

 

EXISTS 연산자는 테이블에 특정 행이 있는지 여부에 따라 query 결과가 달라지는 query에 사용됩니다. subquery에서 최소한 한 개의 행을 반환하면 TRUE로 평가됩니다.

예제는 사원이 없는 부서를 표시합니다. DEPARTMENTS 테이블의 각 행에 대해 EMPLOYEES 테이블에 부서 ID가 동일한 행이 있는지를 확인하는 조건 검사가 수행됩니다. 그러한 행이 없으면 해당 행은 조건을 충족하는 것이므로 선택됩니다. EMPLOYEES 테이블에 그러한 행이 있을 경우 해당 행은 선택되지 않습니다.

 

-Subquery의 null 값

SELECT emp.last_name

FROM employees emp

WHERE emp.employee_id NOT IN (SELECT mgr.manager_id FROM employees mgr);

 

SQL 문은 부하 직원이 없는 모든 사원을 표시하려고 합니다. 논리적으로 이 SQL 문은 12개의 행을 반환해야 합니다. 그러나 이 SQL 문은 행을 반환하지 않습니다. inner query에서 반환되는 값 중 하나가 null 값이기 때문에 전체 query가 행을 반환하지 않습니다. 왜냐하면 null 값을 비교하는 모든 조건은 결과가 null이기 때문입니다. 따라서 subquery의 결과 집합의 일부가 null 값이 될 것으로 예상되는 경우 NOT IN 연산자를 사용하지 마십시오. NOT IN 연산자는 <> ALL과 같습니다.