Oracle - SubQuery

데이터의 비교를 위해 사용한다.

비교 대상과 같은 자료형이여야 하기 때문에,

Main Query의 WHERE COLUMN과 SELECT COLUMN은 같아야 한다.

-- '가가'보다 월급이 많은 사람 출력
SELECT *
FROM TAB01
WHERE SAL > (SELECT SAL
            FROM TAB01
            WHERE NAME='가가');
            
-- 평균 월급보다 더 많이 받는 사람 출력
SELECT *
FROM TAB01
WHERE SAL > (SELECT AVG(SAL)
            FROM TAB01);
            
-- 서울에 일하지 않는 직원 출력. '가가'는 직장이 서울이다.
SELECT * 
FROM TAB01
WHERE LOC != (SELECT LOC
            FROM TAB01
            WHERE NAME='가가');
            
-- '가가'와 동일한 업무를 하는 직원 출력.
SELECT * 
FROM TAB01
WHERE LOC != (SELECT LOC
            FROM TAB01
            WHERE NAME='가가');
            
-- '가가'와 동일한 업무를 하는 동시에, 다른 지역에서 일하는 지역 출력
-- '가가'는 포함되지 않는다.
SELECT * 
FROM TAB01
WHERE WORK = (SELECT WORK
              FROM TAB01
              WHERE NAME = '가가')
AND LOC != (SELECT LOC
            FROM TAB01
            WHERE NAME = '가가');
            
--'가가'를 포함하고 싶으면 OR을 넣으면 된다.
SELECT * 
FROM TAB01
WHERE WORK = (SELECT WORK
              FROM TAB01
              WHERE NAME = '가가')
AND LOC != (SELECT LOC
            FROM TAB01
            WHERE NAME = '가가')
OR NAME = '가가';

 

부산이 근무지인 사람만 출력해, 부산으로 출력해보기

(뭔가 깔끔하지 않은 코드...)

SELECT ID, NAME, SAL, WORK, LOC,
REPLACE(T1.LOC, 4, 'BUSAN') AS LOCTEXT
FROM TAB01 T1
WHERE T1.LOC = ( SELECT LOC
              FROM TAB02
              WHERE LOCTEXT = 'BUSAN');
              
--또는              
SELECT ID, NAME, SAL, WORK, LOC,'BUSAN' AS LOCTEXT
FROM TAB01 T1
WHERE T1.LOC = ( SELECT LOC
              FROM TAB02
              WHERE LOCTEXT = 'BUSAN');

 

ANY, ALL 연산자

다음과 같은 데이터가 있는 테이블 TAB01이 있다(월급 딱 하나만 있음)

       SAL
----------
        50
       100
       150
       200
       250
       300

 

> ALL, < ANY는 최대값을 기준으로 실행한다. 즉, MAX 함수를 적용한 것과 같아진다.

나머지는 모두 최소값을 기준으로 실행한다.

SELECT *
FROM TAB01
-- 300보다 큰 월급을 전체에서 검색
WHERE SAL > ALL(100,200,300);

SELECT *
FROM TAB01
-- 300보다 작은 월급을 전체에서 검색
WHERE SAL < ANY(100,200,300);

SELECT *
FROM TAB01
-- 100보다 작은 월급을 전체에서 검색
WHERE SAL < ALL(100,200,300);

SELECT *
FROM TAB01
-- 100보다 큰 월급을 전체에서 검색
WHERE SAL > ANY(100,200,300);

 

이름, 국어·영어·수학 성적, 학과 데이터가 있는 테이블이 있다.

화학과 학생들의 총점이 각각 210, 230, 240이라면, 이들과 총점이 같은 타 학과 학생들이 누가 있는지 보고 싶을 때

다음과 같이 입력한다.

SELECT NAME, MAJOR, GRADE, KOR+ENG+MAT AS TOTAL
FROM TAB01
WHERE KOR+ENG+MAT IN (SELECT KOR+ENG+MAT AS TOTAL
                            FROM TAB01
                            WHERE MAJOR='화학과');

 

화학과 학생들의 학년과 총점이 같은 사람들 출력 시 다음과 같이 사용한다.

Ex) 화학과 학생들은 모두 1학년이고 , 3명 학생의 총점은 각각 210, 230, 240점이라면

다른 학과에서 1학년인 학생들 중 총점이 210 or 230 or 240점인 학생들을 출력하면 된다.

SELECT NAME, MAJOR, GRADE, KOR+ENG+MAT AS TOTAL
FROM TAB01
WHERE (GRADE, KOR+ENG+MAT) IN (SELECT GRADE, KOR+ENG+MAT AS TOTAL
                            FROM TAB01
                            WHERE MAJOR='화학과');

 

FROM절에 들어가는 SubQuery는 Inline View라고 부른다.

SELECT * 
--INLINE VIEW(SUBQUERY의 일종)
FROM (SELECT * 
FROM TAB01 T1
WHERE SAL >=30);

 

가독성을 향상시키기 위해, 별칭을 미리 지정하는 WITH를 통해 작성하는 경우도 있다.

WITH
T1 AS (SELECT * FROM 
(SELECT * FROM TAB01 WHERE SAL >=30)),
T2 AS (SELECT * FROM 
(SELECT * FROM TAB01 WHERE SAL <=30))
SELECT * FROM T1;

SELECT * FROM TAB01;