데이터베이스 서브 쿼리
IT 위키
더 많은 작업
- Sub Query
- 하나의 SQL 문 안에 괄호로 감싸 포함된 또 다른 SELECT 문
서브쿼리는 메인쿼리(바깥 쿼리) 안에서 값이나 행 집합을 만들어 메인쿼리에 넘겨 준다. 놓이는 위치, 돌려주는 데이터의 형태, 메인쿼리 칼럼을 참조하는지 여부에 따라 다음과 같이 나눈다.
| 구분 | 종류 | 설명 |
|---|---|---|
| 위치별 | Nested Subquery | WHERE절에 위치하고, 가장 먼저 개발됨 |
| Inline View | FROM절에 위치하고, SQL 내 절차성 효과 | |
| Scalar Subquery | SELECT절에 위치하고, 가장 최근에 개발됨 | |
| 반환 데이터
형태별 |
단일행 서브쿼리 |
|
| 다중행 서브쿼리 |
| |
| 다중칼럼 서브쿼리 |
| |
| 동작방식별 | 비연관 서브쿼리 |
|
| 연관 서브쿼리 |
|
B_EMP
| 사원번호 | 이름 | 부서번호 | 관리자번호 | 급여 |
|---|---|---|---|---|
| 1001 | 김대표 | 10 | NULL | 9000 |
| 1002 | 이부장 | 20 | 1001 | 6000 |
| 1003 | 박과장 | 20 | 1002 | 4500 |
| 1004 | 최대리 | 30 | 1001 | 4000 |
| 1005 | 정사원 | 30 | 1004 | 3000 |
| 1006 | 한신입 | NULL | 1002 | 2500 |
B_DEPT
| 부서번호 | 부서명 | 지역 |
|---|---|---|
| 10 | 총무 | 서울 |
| 20 | 개발 | 부산 |
| 30 | 영업 | 대전 |
| 40 | 연구 | 서울 |
| 위치 | 이름 | 예 |
|---|---|---|
| SELECT 절 | 스칼라 서브쿼리 | SELECT 이름, (SELECT 부서명 FROM B_DEPT D WHERE D.부서번호 = E.부서번호) FROM B_EMP E
|
| FROM 절 | 인라인 뷰 | SELECT * FROM (SELECT 부서번호, AVG(급여) AS 평균급여 FROM B_EMP GROUP BY 부서번호) V
|
| WHERE 절 | 중첩 서브쿼리 | WHERE 급여 > (SELECT AVG(급여) FROM B_EMP)
|
| HAVING 절 | 중첩 서브쿼리 | HAVING COUNT(*) > (SELECT COUNT(*) FROM B_EMP WHERE 부서번호 = 10)
|
| INSERT VALUES 절 | 스칼라 서브쿼리 | VALUES (1007, '오인턴', 40, (SELECT MIN(급여) FROM B_EMP))
|
| UPDATE SET 절 | 스칼라 서브쿼리 | SET 급여 = (SELECT ROUND(AVG(급여)) FROM B_EMP X WHERE X.부서번호 = E.부서번호)
|
| ORDER BY 절 | 스칼라 서브쿼리 | ORDER BY (SELECT 부서명 FROM B_DEPT D WHERE D.부서번호 = E.부서번호)
|
- Oracle에서 GROUP BY 절에는 서브쿼리를 쓸 수 없다(ORA-22818: subquery expressions not allowed here).
- 위 HAVING 예는 사원이 2명인 20번, 30번 부서를 돌려준다. ORDER BY 예는 부서명 순(개발, 영업, 총무)으로 정렬하고 부서명이 NULL인 한신입을 맨 뒤에 둔다.
결과가 1행 이하여야 하며 =, <, > 같은 단일행 비교 연산자와 함께 쓴다.
SELECT 이름, 급여 FROM B_EMP
WHERE 급여 > (SELECT AVG(급여) FROM B_EMP); -- 평균 4833.3보다 큰 김대표, 이부장
SELECT 이름 FROM B_EMP
WHERE 급여 = (SELECT 급여 FROM B_EMP WHERE 부서번호 = 20);
-- ORA-01427: single-row subquery returns more than one row
- 20번 부서 사원이 2명이라 서브쿼리가 2행을 돌려주므로 실행 중에 오류가 난다. 다중행 연산자 IN을 쓰면 해결된다.
- 서브쿼리 결과가 0행이면 비교 값이 NULL이 되어 메인쿼리 결과도 0행이다.
여러 행을 돌려주는 서브쿼리는 IN, ANY(SOME), ALL, EXISTS와 함께 쓴다. 아래는 30번 부서 급여 목록 (4000, 3000)과 비교한 결과이다.
| 조건 | 의미 | 결과 |
|---|---|---|
급여 IN (서브쿼리) |
목록 값 중 하나와 같다(= ANY와 같음) | 최대리, 정사원 |
급여 > ANY (서브쿼리) |
최솟값(3000)보다 크다 | 김대표, 이부장, 박과장, 최대리 |
급여 < ANY (서브쿼리) |
최댓값(4000)보다 작다 | 정사원, 한신입 |
급여 > ALL (서브쿼리) |
최댓값(4000)보다 크다 | 김대표, 이부장, 박과장 |
급여 < ALL (서브쿼리) |
최솟값(3000)보다 작다 | 한신입 |
- 서브쿼리 결과가 0행이면
> ALL은 모든 행에 대해 참(6행 전부),> ANY와 IN은 거짓(0행)이다. - 서브쿼리 결과에 NULL이 섞이면 NOT IN은 0행이 된다. B_EMP의 부서번호에 NULL(한신입)이 있으므로
SELECT 부서번호 FROM B_DEPT WHERE 부서번호 NOT IN (SELECT 부서번호 FROM B_EMP)는 사원이 없는 40번 부서도 돌려주지 못하고 0행이다(데이터베이스 Null). 이럴 때는 NOT EXISTS를 쓴다.
여러 칼럼을 한 번에 비교한다. 부서별 최고 급여를 받는 사원을 찾는 예이다.
SELECT 이름, 부서번호, 급여 FROM B_EMP
WHERE (부서번호, 급여) IN (SELECT 부서번호, MAX(급여) FROM B_EMP GROUP BY 부서번호);
-- 김대표 10 9000, 이부장 20 6000, 최대리 30 4000
- 부서번호가 NULL인 그룹(한신입)은 NULL 비교가 참이 되지 않아 빠진다.
- SQL Server는
(a, b) IN (서브쿼리)형태의 다중칼럼 비교를 지원하지 않아 EXISTS 등으로 바꿔 쓴다.
서브쿼리가 메인쿼리 칼럼을 참조하면 메인쿼리의 각 행마다 서브쿼리가 다시 평가된다. EXISTS는 서브쿼리 결과가 1행이라도 있으면 참이며, SELECT 목록의 값은 의미가 없어 보통 SELECT 1로 쓴다.
-- 소속 부서 평균보다 급여가 많은 사원
SELECT E.이름, E.부서번호, E.급여 FROM B_EMP E
WHERE E.급여 > (SELECT AVG(급여) FROM B_EMP X WHERE X.부서번호 = E.부서번호);
-- 이부장(20번 평균 5250), 최대리(30번 평균 3500)
-- 사원이 있는 부서 / 없는 부서
SELECT D.부서번호, D.부서명 FROM B_DEPT D
WHERE EXISTS (SELECT 1 FROM B_EMP E WHERE E.부서번호 = D.부서번호); -- 10, 20, 30
SELECT D.부서번호, D.부서명 FROM B_DEPT D
WHERE NOT EXISTS (SELECT 1 FROM B_EMP E WHERE E.부서번호 = D.부서번호); -- 40 연구
한 행, 한 칼럼만 돌려주어야 한다. 결과가 없으면 NULL이 된다(한신입의 부서명은 NULL).
SELECT E.이름, (SELECT D.부서명 FROM B_DEPT D WHERE D.부서번호 = E.부서번호) AS 부서명
FROM B_EMP E; -- 6행, 한신입은 NULL
SELECT E.이름, (SELECT 이름 FROM B_EMP X WHERE X.부서번호 = E.부서번호) AS 동료
FROM B_EMP E;
-- ORA-01427: single-row subquery returns more than one row
SELECT E.이름, (SELECT 부서명, 지역 FROM B_DEPT D WHERE D.부서번호 = E.부서번호)
FROM B_EMP E;
-- ORA-00913: too many values
- 스칼라 서브쿼리는 외부 조인처럼 동작한다. 짝이 없는 행도 메인쿼리에서 빠지지 않는다.
- 서브쿼리는 반드시 괄호로 감싼다.
- 단일행 비교 연산자에는 단일행 서브쿼리만 쓸 수 있다. 결과가 2행 이상이면 ORA-01427 오류이다.
- WHERE 절 등에 쓰는 중첩 서브쿼리 안에는 ORDER BY를 쓸 수 없다.
WHERE 부서번호 IN (SELECT 부서번호 FROM B_DEPT ORDER BY 부서번호)는 ORA-00907(missing right parenthesis) 오류이다. 정렬은 메인쿼리의 ORDER BY에서 한 번만 한다. - FROM 절의 인라인 뷰에는 ORDER BY를 쓸 수 있다.
SELECT * FROM (SELECT 이름, 급여 FROM B_EMP ORDER BY 급여 DESC) WHERE ROWNUM <= 2는 김대표, 이부장을 돌려준다(Top N 쿼리). - 메인쿼리는 서브쿼리의 칼럼을 쓸 수 없다.
SELECT 이름, 부서명 FROM B_EMP WHERE 부서번호 IN (SELECT 부서번호 FROM B_DEPT)는 ORA-00904("부서명": invalid identifier) 오류이다. 서브쿼리 칼럼이 필요하면 조인이나 인라인 뷰를 쓴다. 반대로 서브쿼리는 메인쿼리 칼럼을 쓸 수 있다(연관 서브쿼리).
뷰는 SELECT 문에 이름을 붙여 데이터베이스에 저장한 가상 테이블이다(데이터베이스 뷰). 인라인 뷰는 이름을 붙여 저장하지 않고 FROM 절에서 한 번만 쓰는 뷰라고 볼 수 있다.
-- 인라인 뷰: 부서별 평균 급여 중 4000 이상
SELECT V.부서번호, V.평균급여
FROM (SELECT 부서번호, AVG(급여) AS 평균급여 FROM B_EMP GROUP BY 부서번호) V
WHERE V.평균급여 >= 4000; -- 10번 9000, 20번 5250
| 뷰의 장점 | 설명 |
|---|---|
| 독립성 | 테이블 구조가 바뀌어도 뷰 정의만 고치면 뷰를 쓰는 응용 프로그램은 바꾸지 않아도 된다. |
| 편리성 | 복잡한 조인·집계 질의를 뷰로 만들어 두면 단순한 SELECT로 쓸 수 있다. |
| 보안성 | 숨기고 싶은 칼럼이나 행을 빼고 뷰를 만들어 사용자에게 뷰 권한만 줄 수 있다. |
- 위치별 이름(스칼라 서브쿼리, 인라인 뷰, 중첩 서브쿼리)과 각 위치에서 쓸 수 있는지 여부. GROUP BY 절에는 쓸 수 없다.
- 단일행 비교 연산자에 여러 행이 오면 ORA-01427 오류. 스칼라 서브쿼리도 2행 이상이면 같은 오류
> ANY는 최솟값보다 크다,> ALL은 최댓값보다 크다. 서브쿼리가 0행일 때 ALL은 참이다.- NOT IN 서브쿼리 결과에 NULL이 있으면 0행, NOT EXISTS는 NULL의 영향을 받지 않는다.
- 중첩 서브쿼리 안의 ORDER BY는 오류, 인라인 뷰의 ORDER BY는 가능. 메인쿼리에서 서브쿼리 칼럼은 쓸 수 없다.