서브쿼리란?
하나의 SQL문안에 포함되어 있는 또 다른 SQL문으로, 알려지지 않은 기준을 이용한 검색에 사용한다.
- 괄호로 감싸서 사용한다.
- 서브쿼리는 단일 행 또는 다중 행 비교 연산자와 함께 사용 가능하다. 단일 행 비교 연산자는 서브쿼리의 결과가 반드시 1건 이하여야 하고, 다중 행 비교 연산자는 결과 건수와 상관 없다.
- TOP-N 분석, 인라인 뷰를 제외한 서브쿼리에서는 ORDER BY 를 사용하지 못한다.
- SELECT, FROM, WHERE, HAVING, ORDER BY, INSERT-VALUES, UPDATE-SET 절에 사용 가능하다.
- 서브쿼리에서 두 개의 테이블 위치를 바꿨는데도 같은 결과가 나온다면, 조인으로 바꿀 수 있다는 것이다. 그래서 일반적으로 서브쿼리를 집합적 개념을 사용할 수 있는 조인으로 바꾼다.
사용 위치에 따른 분류
SELECT (스칼라 서브쿼리)
FROM (인라인뷰)
WHERE (서브쿼리);
스칼라 서브 쿼리 - SELECT 절에 사용
- 단일 컬럼, 단일 행 반환 (1개)
- 결과 집합이 적을 때 사용 (많으면 성능 이슈 발생)
- SELECT 절에 사용시 JOIN 으로 동일한 결과를 얻을 수 있다.
- 칼럼을 쓸 수 있는 대부분의 곳에서 사용할 수 있다.
# select 절에서 사용 select empno, (select dname from dept where deptno = a.deptno) dname from emp a; # 함수의 인자로 사용 select empno, ename, substr((select dname from dept where deptno = e.deptno), 1, 3) as department_name from emp e; # where 절 조건으로 사용 select empno, ename from emp e where (select dname from dept where deptno = e.deptno) = (select dname from dept_2 where deptno = e.deptno); # order by 절에서 사용 select empno, ename, deptno from emp e order by (select dname from dept where deptno = e.deptno)
인라인 뷰 - FROM 절에 사용
- 인라인 뷰(Inline View)는 SQL에서 서브쿼리(Subquery)를 FROM 절에 사용하는 것을 의미한다.
- 테이블 명이 올 수 있는 곳에서 사용된다.
- VIEW 와 사용적 측면에서 동일하다.
-
VIEW 는 데이터베이스에 정의되어 사용되지만, 서브쿼리는 쿼리 실행시 정의되기 때문에
일반적인 뷰를 **정적 뷰(Static View)** 라고 하고 인라인 뷰를 **동적 뷰(Dynamic view)** 라고도 한다.select t.team_name, p.player_name, p.back_no from (select team_id, player_name, back_no from player where position = 'MF') p, team t where p.team_id = t.team_id order by player_name;VIEW 사용의 장점
단지 정의만을 가지고 있으며, 실행 시점에 질의를 재작성하여 수행한다.
-
실제 데이터를 저장하고 있는 뷰를 생성하는 기능을 지원하는 DBMS 도 있다.
-
독립성 : 테이블 구조가 변경되어도 뷰를 사용하는 응용 프로그램은 변경하지 않아도 된다.
-
편리성 : 복잡한 질의를 뷰로 생성하여 관련 질의를 단순하게 작성할 수 있다.
-
보안성 : 숨기고 싶은 정보를 빼고 생성하여 정보를 감출 수 있다.
-
- 조인 집합 간의 관계가 1:1이 아닌 1:M의 관계일 때 M쪽의 데이터를 인라인 뷰에서 먼저 group by 연산을 수행하여 메인쿼리의 집합과 1:1조인을 수행할 수 있다.
-
인라인 뷰의 집계 함수 출력 결과는 별칭으로 메인 쿼리에 전달한다.
select d.dname, e.avg_sal, e.max_sal from (select deptno, avg(sal) avg_sal, max(sal) max_sal from emp group by deptno) e, dept d where e.deptno = d.deptno AND d.sal > e.max_sal order by dname;
-
중첩 서브쿼리 - WHERE, HAVING 절에 사용
- 다중 컬럼 또는 다중 행 반환
- 하지만 = 비교 일 때는 단일 행 반환
반환되는 데이터 형태에 따른 분류
단일 행 서브쿼리
- 서브쿼리의 실행결과가 항상 1건 이하
- 단일 행 비교연산자에는 =, <, <=, >, >=, <> 이 있다.
- 다중 행 서브쿼리 비교 연산자를 사용할 수도 있다.
다중 행 서브쿼리
- 서브쿼리의 실행결과가 여러 건
- 다중 행 비교 연산자에는 IN, ALL, ANY, SOME, EXISTS 가 있다.
- ALL : ‘>’ 사용시 메인 쿼리에서는 서브 쿼리 결과의 최대값보다 큰 모든 행 반환
- ANY : ‘>’ 사용시 메인 쿼리에서는 서브 쿼리의 결과의 최소값 보다 큰 모든 행 반환
# salary 가 서브쿼리의 salary들 중 최소값보다 큰 모든 행 반환
select * from emp where salary > ANY(select salary from emp where deptno = 10)다중 컬럼 서브쿼리 (oracle) - SQL server 에서 지원 X
- 서브쿼리의 실행결과로 여러칼럼을 반환
- 비교하고자 하는 칼럼 개수와 칼럼의 위치가 동일해야 한다.
- 대소 비교가 불가능하다. (<, > 등)
select team_id, player_name, position, back_no, height
from player
where (team_id, height) in (select team_id, min(height) from player group by team_id)
order by team_id, player_name;동작 방식에 따른 분류
비연관 서브쿼리
- 서브쿼리가 메인쿼리 칼럼을 가지고 있지않는 형태의 서브쿼리이다.
- 메인쿼리에 값(서브쿼리가 실행된 결과)을 제공하기 위한 목적으로 주로 사용한다.
- 아래와 같이 메인쿼리를 참조하지 않고 결과값 만을 제공하면 비연관 서브쿼리
SELECT employee_id, first_name, salary FROM employees WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'Sales');
연관 서브쿼리 - WHERE 절에 사용
- 메인 쿼리의 컬럼을 서브쿼리에서 사용한다.
- 일반적으로 메인쿼리가 먼저 수행되어 읽혀진 데이터를 서브쿼리에서 조건이 맞는지 확인하고자 할때 주로 사용된다.
- 아래와 같이 메인쿼리의 e.department_id 를 참조하면 연관 서브쿼리
SELECT employee_id, first_name, salary FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);
슬기로운 개발 생활