[SQL] 서브 쿼리 개념 | 단일행, 복수행 서브쿼리 | 인라인 뷰 (in-line-view)
서브쿼리
앞서 배운 조인은 하나 이상의 테이블에서 원하는 데이터를 조회할 때 사용하는 방법이고
서브쿼리는 하나의 SELECT 만으로 원하는 데이터를 조회할 수 없을 때 사용하는 방법으로
여러 개의 SELECT 문장을 하나로 합쳐서 하나의 실행가능한 SQL 문장으로 만들어 원하는 데이터를 조회할 수 있다.
예시로 이해하기)
Whalen 사원보다 많은 월급을 받는 사원을 조회하기 위해서는 하나의 SELECT 문으로는 불가능하다.왜냐하면
1.먼저 Whalen 사원의 월급이 얼마인지를 조회하는 SELECT 문이 필요하고,
2.조회한 월급보다 많은 월급을 받는 사원을 출력하기 위한 또 다른 SELECT 문이 필요하기 때문이다.
1) Whalen 사원의 월급이 얼마인지 구하는 SQL문
SELECT salary
FROM employees
WHERE last_name='Whalen';
2) Whalen 사원의 월급이 300 이라 했을 때 , 월급이 300 보다 많은 사원을 구하는 SQL 문
SELECT salary
FROM employees
WHERE salary>='Whalen';
이렇게 하나의 select 문이 아닌 여러개의 select문을 사용해야 원하는 결과를 조회할 수 있는 경우가 있는데
이러한 경우에 서브쿼리를 사용할 수 있다.
다음은 서브쿼리의 기본 문법이다.
SELECT select list
FROM 테이블
WHERE 컬럼명 연산자 (SELECT select_list
FROM 테이블);
바깥 쪽 SELECT 문을 메인쿼리라하고 하고 안쪽 SELECT문을 서브쿼리라고 부른다.
서브쿼리는 반드시 괄호를 사용해야 되며 실행 순서는 서브 쿼리가 먼저 실행되고, 실행된
결과가 메인 쿼리에 전달되어 실행된다.
위의 두 개의 SQL문을 하나의 서브쿼리 문으로 작성하면 다음과 같다.
SELECT last_name,salary
FROM employees
WHERE salary >= (SELECT salary
FROM employees
WHERE last_name='Whalen');
서브쿼리에서 이름이 Whalen인 사원의 월급을 조회하고 그 값을 메인쿼리로 전달하여 전달된 월급보다 크거나 같은
사원의 이름과 월급을 조회한다.
서브쿼리 종류
오라클에서 사용 가능한 서브쿼리는 서브쿼리가 실행되어 반환된 행의 개수에 따라서 단일행 서브쿼리와 복수행 서브쿼리로 구분된다.
단일행 서브쿼리 : 서브쿼리 실행ㄷ결과가 한 개의 행을 반환
-사용가능 연산자: =,>,>=,<=,!= 와 같은 비교 연산자
복수행 서브쿼리: 서브쿼리 실행 결과가 복수 개의 행을 반환한다.
-사용가능 연산자: IN, ANY,ALL,EXIST 연산자
단일행 서브쿼리
단일행 서브쿼리는 서브쿼리가 실행되어 반드시 한 개의 행을 반환하는 서브쿼리를 의마한다.
대표적으로 기본키를 이용하거나 MAX MIX SUM 같은 그룹함수를 사용하여 검색하는 경우로서 반드시 단일행 연산자를 사용하여 메인 쿼리와 연산되어야 된다.
예시) 사원들의 평균 월급보다 더 많은 월급을 받는 사원을 조회하는 SQL 문이다.
SELECT last_name,salary
FROM employees
WHERE salary >= (SELECT AVG(salary)
FROM employees);
복수행 서브쿼리
복수행 서브쿼리는 서브쿼리가 실행되어 반환되는 결과가 하나 이상의 행일 때 사용하는 서브쿼리이다.
반드시 복수형 연산자와 함께 사용해야 되며 사용 가능한 복수행 연산자는 다음과 같다.
IN , ANY, ALL, EXIST
IN 연산자
IN 연산자는 서브쿼리 반환값이 복수이고 메인 쿼리와 동등 연산자(=) 바익으로 비교할 때 사용하는 연산자이다.
예시) 이름이 Whalen 또는 Fay 인 사원과 같은 월급을 받는 모든 사원들의 정보를 출력하는 문장
SELECT last_name,salary
FROM employees
WHERE salary IN ( SELECT salary
FROM employees
WHERE last_name IN ('Whalen',Fay))
ALL 연산자
ALL 연산자는 복수행 서브쿼리에서 > < 같은 비교 연산자를 사용하고자 할 때 사용된다.
앞에서 배웠던 것처럼 > <같은 연산자는 단일행 연산자 이기 때문에 복수행 연산자에서 ALL키워드 없이 사용하면 에러가 발생된다. ALL 연산자는 서브쿼리에서 반환되는 행들 전체에 대한 조건이 모두 (all) 만족해야 된다는 것을 의미하고 ALL 연산자를 사용하는 경우는 다음과 같다.
> ALL (서브쿼리) : 모든 데이터보다 큰 데이터를 메인 쿼리에서 조회, 즉 최대값 보다 큰 데이터 조회
< ALL (서브쿼리) : 모든 데이터보다 작은 데이터를 메인쿼리에서 조회, 즉 최솟값 보다 작은 데이터를 조회
예시) 직업이 IT_PROG 인 사원의 최소 월급보다 적은 월급을 받는 사원들의 정보를 출력
SELECT last_name, department, salary
FROM employees
WHERE salary < ALL (SELECT salary
FROM employees
WHERE job_id='IT_PROG');
예시) 직업이 IT_PROG 인 사원의 최대 월급보다 많은 월급을 받는 사원들의 정보를 출력
SELECT last_name, department, salary
FROM employees
WHERE salary > ALL (SELECT salary
FROM employees
WHERE job_id='IT_PROG');
ANY 연산자
ANY 연산자는 앞서 배웠던 ALL 연산자와 사용법이 비슷하다.
차이점은 서브쿼리에서 반환되는 행들 전체에 대해 하나 이상만 만족하면 된다는 점이다.
> ANY (서브쿼리) : 최솟값보다 큰 데이터를 조회할 때 사용하는 서브쿼리
< ANY (서브쿼리) : 최댓값보다 작은 데이터를 조회할 때 사용하는 서브쿼리
예시) 직업이 IT_PROG 인 사원의 최소 월급보다 많은 월급을 받는 사원들의 정보를 출력
SELECT last_name, department, salary
FROM employees
WHERE salary > ANY (SELECT salary
FROM employees
WHERE job_id='IT_PROG');
예시) 직업이 IT_PROG 인 사원의 최대 월급보다 적은 월급을 받는 사원들의 정보를 출력
SELECT last_name, department, salary
FROM employees
WHERE salary < ANY (SELECT salary
FROM employees
WHERE job_id='IT_PROG');
EXIST 연산자
서브쿼리에서 실행된 결과가 하나라도 존재하는지 여부를 확인할 때 사용하는 복수행 연산자
만일 서브쿼리에서 검색된 결과가 하나라도 없으면 메인 쿼리에 전달된 값이 false 이기 때문에 조건과 일치하지 않아서 메인쿼리가 실행되지 않는다.
예시) 사원들 중에서 커미션을 받은 사원이 한 명이라도 있으면 모든 사원 정보를 출력하는 문장
SELECT last_name, department, salary
FROM employees
WHERE EXIST > (SELECT employee_id
FROM employees
WHERE commission_pct IS NOT NULL);
다중 컬럼 서브쿼리
다중컬럼 서브쿼리는 서브쿼리에서 여러 개의 컬럼값을 검색하여 메인쿼리의 조건절과 비교하는 서브쿼리이다. 메인쿼리의 조건절은 서브쿼리의 컬럼과 일대일 매칭이 되어야 한다.
다중 컬럼 서브쿼리에는 컬럼을 쌍으로 묶어서 동시에 비교하는 partwise 방식이 있고
컬럼별로 나누어 비교하고 나중에 AND 연산으로 처리하는 unpairwise 방식이 있다.
예시) pairwise 방식
예시) 직업이 IT_PROG 인 사원의 최대 월급보다 많은 월급을 받는 사원들의 정보를 출력
SELECT last_name, department, salary
FROM employees
WHERE (department_id , salary) IN (SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id );
인라인 뷰(in-line-view)
앞서 배웠던 서브쿼리는 메인쿼리의 where 절에 사용된 서브쿼리이다.
하지만 from 절에도 서브쿼리를 사용할 수 있으며 이렇게 from 절에 사용된 서브쿼리를 인라인 뷰라고 한다.
기본 문법)
SELECT select_list
FROM (서브쿼리) alias
WHERE 조건식;
일반적으로 from 절에는 테이블 명이 와야하지만 서브쿼리가 하나의 가상 테이블 형태로 사용될 수 있다.
인라인 뷰는 FROM절에서 참조하는 테이블의 크기가 클 경우에 필요한 행과 컬럼만으로 구성된 집합을 재정의 하여 쿼리문을 효율적으로 사용할 수 있는 장점이 있다.
