SQL 6. SUB 서브 쿼리
관련링크
본문
6-1. 서브 쿼리 구문
쿼리문에 또하나의 쿼리문을 넣는다.
어떻게 넣냐고??
select * from table where a > ( select name frome table where name='개똥이' );
서브 쿼리는 다른 SELECT문의 절에 삽입된 SELECT 문으로서 서브 쿼리를 사용하면
간단한 명령문으로 강력한 기능을 제공하는 명령문을 작성할 수 있다.
서브 쿼리는 테이블자체의 데이터에 종속된 조건을 사용해 테이블에서 행을 선택할 때 유용한다.
다음과 같은 여러 SQL절에 서브 쿼리를 포함시킬 수 있다.
․ 서브쿼리(내부질의)는 기본 질의 실행 전에 한 번 실행된다.
․ 서브쿼리의 결과는 메인쿼리(외부질의)에 사용된다.
․ WHERE 절
․ HAVING 절
․ FROM 절 (인라인뷰라 부른다)
구문 설명 :
operator > , = , IN 등 비교 조건을 포함합니다.
참고 : 비교 조건은 단일 행 연산자(>,=,>=,<,<>,<=)와 여러 행 연산자(IN,ANY,ALL)로 분류된다.
서브 쿼리는 중첩SELECT문, 하위SELECT문 또는 내부SELECT문 이라고도 한다.
일반적으로 서브 쿼리가 먼저 실행되고 그 출력결과를 사용하여 메인쿼리(외부 질의)에 대한 질의 조건을 완성한다.
예제) 서브 쿼리 사용
오라클 디비 예제 :
SELECT last_name
FROM employees
WHERE salary > (SELECT salary FROM employees WHERE last_name = 'Abel');
MSSQL 디비 예제 :
SELECT lname, job_id, job_lvl
FROM employee
WHERE job_lvl = ( SELECT job_lvl FROM employee WHERE lname='Afonso' );
☞설명)
예제에서 내부 질의는 사원 Abel의 급여를 확인하고 외부 질의는 내부 질의의 결과를 사용하여 해당급여보다 급여가 많은
사원을 모두 표시한다.
서브 쿼리 사용 지침
․ 서브 쿼리를 괄호로 묶는다.
․ 읽기 쉽도록 비교 조건의 오른쪽에 서브 쿼리를 넣는다.
․ Oracle8i 릴리스 이전에는 서브 쿼리에 ORDER BY절을 포함할 수 없다.
ORDER BY 절은 SELECT 문에서 한 번만 사용할 수 있으며, 지정할 경우 기본 SELECT 문의 마지막 절이어야 했다.
Oracle8 릴리스부터 Top-N 분석을 수행하는데 필요하므로 ORDER BY 절을 서브 쿼리에 사용할 수 있게 되었다.
MSSQL에서는 지원하지 않는 기능이다.
․ 단일 행 서브 쿼리에는 단일 행 연산자를 사용 하고 다중 행 서브 쿼리에는 다중 행연산자를 사용한다.
․ 서브 쿼리에 사용되는 비교 조건은 단일 행 연산자 및 다중 행 연산자로 분류된다.
․ 단일 행 서브 쿼리 : 내부 SELECT문에서 한 행만 반환하는 질의
․ 다중 행 서브 쿼리 : 내부 SELECT문에서 여러 행을 반환하는 질의
① 서브 쿼리 유형
= 같음
> 보다 큼
>= 크거나 같음
< 보다 작음
<= 작거나 같음
<> 같지 않음
MSSQL 디비 예제 :
SELECT lname, job_id, job_lvl FROM employee
․ 다중 행 서브 쿼리
기본 질의 반환
서브 쿼리 ----------> ST_CLERK
SA_MAN
참고 : 다중 열 서브 쿼리 : 내부 SELECT문에서 여러 열을 반환하는 질의
② 단일 행 서브 쿼리
․ 한 행만 반환합니다.
․ 단일 행 비교 연산자를 사용합니다.
단일 행 서브 쿼리는 내부 SELECT문에서 하나의 행을 반환하는 서브 쿼리로서 단일 행 연산자를 사용한다.
예제)
오라클 디비 예제 :
SELECT last_name, job_id
FROM employees
WHERE job_id = ( SELECT job_id FROM employees WHERE employee_id = 141 );
☞설명)
사원 141과 동일한 업무ID를 가진 사원을 표시합니다.
MSSQL 디비 예제 :
SELECT lname, job_id FROM employee
WHERE job_id =
( SELECT job_id FROM employee
WHERE emp_id='PXH22250M')
단일 행 서브 쿼리 실행
오라클 디비 예제 :
SELECT last_name, job_id, salary
FROM employees WHERE
job_id = (SELECT job_id FROM employees WHERE employee_id = 141 )
AND
salary > ( SELECT salary FROM employees WHERE employee_id = 143 );
단일 행 서브 쿼리 실행
SELECT문을 질의 블록으로 간주할 수 있다.
예제는 사원141과 업무ID가 동일하면서 사원143보다 급여가 많은 사원을 표시한다.
예제는 세 개의 질의(외부 질의 한 개와 내부 질의 두 개)블록으로 구성된다.
내부 질의 블록이 먼저 실행되어 각각의 질의 결과(ST_CLERK,2600)를 생성한다.
그런 다음 외부 질의 블록이 처리되면서 내부 질의에서 반환된 값을 사용하여 검색 조건을 완성한다.
두 개의 내부 질의는 모두 하나의 값(ST_CLERK 및 2600)을 반환하므로 이 SQL문을 단일 행 서브 쿼리라고 한다.
참고 : 내부 질의 및 외부 질의는 서로 다른 테이블의 데이터를 가져올 수 있습니다.
WHERE
job_id = ( SELECT job_id FROM employee WHERE emp_id='TPO55093M')
AND
job_lvl = ( SELECT job_lvl FROM employee WHERE emp_id='PMA42628M')
아래와 위는 같다.
select fname, job_id, job_lvl, emp_id from employee where job_id = 13 and job_lvl = 35
MSSQL 디비 예제 :
SELECT lname , job_id, job_lvl FROM employee WHERE job_lvl= ( SELECT min(job_lvl) FROM employee )
예제) 서브 쿼리에서 그룹 함수 사용
SELECT last_name, job_id, salary FROM employees WHERE salary = ( SELECT MIN(salary) FROM employees );
서브 쿼리에서 그룹 함수 사용
단일 행을 반환하는 그룹 함수를 서브 쿼리에서 사용하여 기본 질의에서 데이터를 표시 할 수 있다.
서브 쿼리는 괄호로 묶어야 하며 비교 조건 다음에 놓는다.
예제는 최소 급여를 받는 모든 사원의 이름, 업무ID및 급여를 표시하며 MIN그룹 함수는 단일 값(2500)을 외부 질의에 반
환한다.
HAVING 절에 서브 쿼리 사용
․ SQL server는 서브 쿼리를 먼저 실행한다.
․ SQL server는 메인 쿼리의 HAVING 절에 결과를 반환한다.
오라클 디비 예제 :
SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN( salary) > ( SELECT MIN (salary) FROM employees WHERE department_id = 50 );
MSSQL 디비 예제 :
SELECT pub_id, MIN(job_lvl)
FROM employee
GROUP BY pub_id
HAVING MIN(job_lvl) >( SELECT MIN(job_lvl) FROM employee WHERE pub_id=0877 );
예제) 평균 급여가 가장 적은 업무를 찾습니다.
오라클 디비 예제 :
SELECT job_id, AVG(salary)
FROM employees
GROUP BY job_id
HAVING AVG(salary) = ( SELECT MIN(AVG(salary)) FROM employees GROUP BY job_id );
MSSQL 디비 예제 :
select pub_id , min(job_lvl)
from employee
group by pub_id having min(job_lvl) > ( select top 1 AVG(job_lvl) from employee GROUP BY job_id ORDER BY AVG(job_lvl) ASC )
오라클 디비 예제 :
SELECT employee_id, last_name
FROM employees
WHERE salary = ( SELECT MIN (salary) FROM employees GROUP BY department id );
예제) 이 명령문은 무엇이 잘못되었을까요?
ERROR at liln 4 :
ORA-01427 : single-row subquery resturns more than one row
☞설명) 서브 쿼리 오류
다중 행 서브 쿼리에 단일 행 연산자를 사용 했다.
서브 쿼리의 일반적인 오류는 단일 행 서브 쿼리에 대해 여러 행이 반환되는 것이다.
예제의 SQL문에서 서브 쿼리는 GROUP BY절을 포함하는데 이는 서브 쿼리가 그룹당 하나씩 여러 행을 반환함을 의미한다.
이 예제에서 서브 쿼리의 결과는 4400,6000,2500,4200,7000,17000,8300이다.
외부 질의는 서브 쿼리의 결과(4400,6000,2500,4200,7000,17000,8300)를 받아 WHERE절에서 사용한다.
WHERE절은 하나의 값만 처리하는 단일 행 비교 연산자 등호(=) 연산자를 포함하는데 = 연산자가 서브 쿼리로 부터 여러
값을 받아들일 수 없으므로 오류가 발생한다.
이 문제를 수정하려면 = 연산자를 IN으로 바꾸어야 한다.
MSSQL 디비 예제 :
SELECT emp_id , lname
FROM employee
WHERE job_lvl
IN (select min(job_lvl) from employee group by pub_id)
SELECT emp_id , lname
FROM employee
WHERE job_lvl IN (100,35,32,198,246,172,200,80)
예제) 이 명령문은 행을 반환할까요?
오라클 디비 예제 :
SELECT last_name, job_id
FROM employees
WHERE job_id =
(SELECT job_id
FROM employees
WHERE last_name = 'Haas');
no rows selecsted
☞설명)
서브 쿼리가 값을 반환하지 않는다.
서브 쿼리에서 발생할 수 있는 일반적인 문제는 내부 질의에서 행을 반환하지 않는 경우다.
예제의 SQL문에서 서브 쿼리는 이름이 Haas인 사원을 찾는 WHERE절을 포함한다.
이 명령문은 유효하지만 실행했을 때 선택되는 행이 없다.
이름이 Haas라는 사원이 없으므로 서브 쿼리에서 행을 반환하지 않는다.
외부 질의는 서브 쿼리의 결과(널)를 받아 WHERE절에서 사용한다.
외부 줄의는 업무 ID가 널인 사원을 찾지 못한다.
널 값을 갖는 업무가 존재하더라고 널 값 두 개를 비교하면 널이 반환되므로 행이 반환되지 않는다.
따라서 WHERE조건이 True가 되지 않는다.
MSSQL 디비 예제 :
select lname, job_id
from employee
where job_id = (select job_id
from employee
where lname='Rhee')
③ 다중 행 서브 쿼리
․ 여러 행을 반환합니다.
․ 여러 행 비교 연산자를 사용합니다.
연산자 의미
IN 목록에 있는 임의의 멤버와 동일합니다.
ANY 값을 서브 쿼리에 의해 반환된 각 값과 비교합니다.
ALL 값을 서브 쿼리에 의해 반환된 모든 값과 비교합니다.
여러 행을 반환하는 서브 쿼리를 여러 행 서브 쿼리라고 하는데 여러 행 서브 쿼리에서는 단일 행 연산자 대신 여러 값을
처리하는 여러 행 연산자를 사용한다.
오라클 디비 예제 :
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary IN
(SELECT MIN(salary)
FROM employees
GROUP BY department_id);
결과)
SELECT last_name, salary, department_id
FROM employees
WHERE salary IN (2500, 4200, 4400, 6000, 7000, 8300. 8600, 17000);
예제) 다중 행 서브 쿼리에 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');
☞설명)
서브쿼리의 결과가 9000,6000,4200
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ANY ( 9000,6000,4200 )
AND job_id <> 'IT_PROG');
결과 적으로 쿼리는
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary <= 9000
AND job_id <> 'IT_PROG');;
ANY 연산자(및 동의어인 SOME연산자)는 값을 서브 쿼리가 반환하는 각각의값과 비교한다.
예제는 IT프로그래머가 아니면 급여가 임의의 IT프로그래머보다 낮은 사원을 표시한다.
프로그래머의 최고 급여는 $9,000이다.
“< ANY”는 최대값보다 작음을 나타내고, “> ANY”는 최소값보다 큼을 나타내며, “= ANY”는 IN과 동일하다.
MSSQL 디비 예제 :
--// "< ANY"는 최소 값보다 작은 값에 해당되는 것
SELECT pub_id, lname, job_id, job_lvl
FROM employee
MSSQL 디비 예제 :
--// "< ANY"는 최대 값보다 작은 값에 해당되는 것
SELECT pub_id, lname, job_id, job_lvl
FROM employee
WHERE job_lvl < ANY
( SELECT job_lvl
FROM employee
WHERE job_id=14)
AND job_id <> 14
=>
SELECT pub_id, lname, job_id, job_lvl
FROM employee
WHERE job_lvl < 100
AND job_id <> 14
--// ">
ANY" 는 최소값보다 큰 값에 해당되는 것
SELECT pub_id, lname, job_id, job_lvl
FROM employee
WHERE job_lvl > ANY
( SELECT job_lvl
FROM employee
WHERE job_id=14)
AND job_id <> 14
=>
SELECT pub_id, lname, job_id, job_lvl
FROM employee
WHERE job_lvl > 35
AND job_id <> 14
예제) 다중 행 서브 쿼리에 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';
실행 결과는
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ALL ( 9000,6000,4200 )
AND job_id <> 'IT_PROG';
결과 적으로 쿼리는
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary <= 4200
AND job_id <> 'IT_PROG');
ALL 연산자는 값을 서브 쿼리에서 반환하는 모든 값과 비교한다.
예제는 업무 ID가 IT_PROG인 모든 사원보다 급여가 적으면서 업무가 IT_PROG가 아닌 사원을 표시한다.
“> ALL”은 최대값보다 큼을 나타내고, “< ALL”은 최소값 보다 작음을 나타낸다.
NOT 연산자는 IN, ANY 및 ALL 연산자와 함께 사용할 수 있다.
WHERE job_lvl < ALL
( SELECT job_lvl
FROM employee
WHERE job_id=14)
AND job_id <> 14
=>
SELECT pub_id, lname, job_id, job_lvl
FROM employee
WHERE job_lvl < 35
AND job_id <> 14
--// ">
ANY " 는 최대값보다 큰 값에 해당되는 것
SELECT pub_id, lname, job_id, job_lvl
FROM employee
WHERE job_lvl > ALL
( SELECT job_lvl
FROM employee
WHERE job_id=14)
AND job_id <> 14
=>
SELECT pub_id, lname, job_id, job_lvl
FROM employee
WHERE job_lvl > 100
AND job_id <> 14
MSSQL 디비 예제 :
SELECT t1.price, t1.title_id
FROM titles t1
예제) 서브 쿼리에서의 널 값
오라클 디비 예제 :
SELECT emp.last_name
FROM employees emp
WHERE emp.employee_id NOT IN
(SELECT salary
FROM employees mgr);
no rows selected
☞설명) 서브 쿼리의 결과 집합과 널 반환
예제의 SQL문은 부하 직원이 없는 모든 사원을 표시한다.
논리적으로는 이 SQL문이 행을 12개 반환해야 하지만 아무 행도 반환되지 않는다.
내부 질의에서 반환한 값 중 하나가 널 값이므로 전체 질의가 행을 반환하지 않는 것이다.
널 값을 비교하는 모든 조건에 대한 결과는 널이다.
따라서 서브 쿼리의 결과 집합에 널 값이 포함될 가능성이 있는 경우 에는 NOT IN연산자를 사용해서는 안된다.
NOT IN 연산자는 <> ALL 과 동일하다.
IN연산자를 사용할 때는 서브 쿼리 결과 집합에 널 값이 있어도 된다.
IN연산자는 = ANY와 동일하다.
예를 들어, 부하 직원이 있는 모든 사원을 표시하려 한다.
SELECT emp.last_name
FROM employees emp
WHERE emp.employee_id IN
(SELECT mgr.manager_id
FROM employees mgr);
또는, 서브 쿼리에 다음과 같은 WHERE절을 포함시켜 부하 직원이 없는 모든 사원을 표시 할 수 있습니다.
SELECT emp.last_name FROM employees emp
WHERE emp.employee_id NOT IN
(SELECT mgr.manager_id
FROM employees mgr
WHERE manager_id IS NOT NULL);
WHERE t1.price IN
( SELECT price FROM titles t2)
=>
SELECT t1.price, t1.title_id
FROM titles t1
WHERE t1.title_id NOT IN
( SELECT title_id FROM titles t2
WHERE price IS NOT NULL)
-- in을 하게되면 null을 뺀 나머지에 해당되는 데이터 출력
-- not in을 하게되면 null로 인해 출력결과를 가지지 못한다.
SELECT t1.price, t1.title_id
FROM titles t1
WHERE t1.price IN
( SELECT price FROM titles t2)
SELECT t1.price, t1.title_id
FROM titles t1
WHERE t1.price =ANY
( SELECT price FROM titles t2)
SELECT t1.price, t1.title_id
FROM titles t1
WHERE t1.price IS NOT NULL
--NULL인 출력결과를가지려면
SELECT * FROM titles
WHERE price IS NULL