[SQL] 서브쿼리의 모든 것 - Sub Query, Inline View, Scalar, Multi Column

쿼리 안에 또 다른 쿼리 - Sub Query

SELECT col1, (SELECT ...)     -- 스칼라 서브쿼리(Scalar Sub Query): 하나의 컬럼처럼 사용 (표현 용도)

 FROM (SELECT ...)             -- 인라인 뷰(Inline View): 하나의 테이블처럼 사용 (테이블 대체 용도)

WHERE col = (SELECT ...)    -- 일반 서브쿼리: 하나의 변수(상수)처럼 사용 (서브쿼리의 결과에 따라 달라지는 조건절)

1) Inline view (인라인 뷰)

먼저, FROM 절에 사용하는 서브쿼리부터 살펴볼까요?

위의 설명처럼 인라인 뷰는 SELECT 절의 결과를 FROM 절에서 하나의 테이블처럼 사용하고 싶을 때 사용합니다.

기존 단일 쿼리로는 '테이블에서 각 부서별 최대 연봉' 까지 알 수 있었다면,

서브쿼리를 통해서 누가 최대 연봉자인지 확인할 수 있게 되었습니다.

 EMP 테이블에서 각 부서별 최대 연봉자 확인  

=> 인라인 뷰를 먼저 보면, '각 부서별 최대 연봉' 을 구한 결과를 메인 쿼리에서 테이블처럼 사용한 것입니다.

     해석해보면, EMP 테이블에서 각 부서별 최대 연봉(인라인 뷰)과 

                    같은 연봉 데이터(e.SAL = i.max_sal)를 갖는 직원 조회. 라고 할 수 있겠죠?

      인라인 뷰도 테이블처럼 사용하기 때문에 EMP 테이블과 Join 을 하려면 where 절에 조건이 필요하답니다.

      그래서 e.DEPTNO = i.DEPTNO 을 써준 것이죠.

      여기서 주의할 점은. 

      인라인뷰에 있는 max(sal) 컬럼에 Alias 를 명시해주지 않으면, i.max(sal) 로 사용하게 되겠죠.

      이렇게 사용하면 오라클은 WHERE 절에서 max 함수로 인식하게 되므로, max_sal 과 같은 Alias 를 사용한 것입니다.

       

 추가적으로. 서브쿼리에 ORDER BY 절은 올 수 없답니다. 사실 서브쿼리는 출력 용도가 아닌 테이블처럼 사용 용도이므로 굳이 정렬할 필요가 없는 것이죠.

 

2) Sub Query (일반 서브쿼리)

일반 서브쿼리는 SELECT 절의 결과를 WHERE 절에서 하나의 변수(상수)처럼 사용하고 싶을 때 사용합니다.

조건절은 서브쿼리의 결과에 따라 달라지겠죠.

일반 서브쿼리는 WHERE 절에 사용하는만큼 

조건에 필요한 단일 행 서브쿼리와, 다중 행 서브쿼리와 함께 사용됩니다.

단일 행 서브쿼리와 다중행 서브쿼리에 따라 연산자를 잘 선택하는 것도 중요하답니다!

1. Single Row Sub Query (단일 행 서브쿼리)

단일 행 서브쿼리는 수학을 배웠다면 누구나 다 알만한 연산자입니다.

 ' = ' : 같다

 ' <> ' : 같지 않다

 ' > ' : 크다

  ' >= ' : 크거나 같다

 ' < ' : 작다

 ' <= ' : 작거나 같다

정말 쉽죠? ㅋ_ㅋ 

차근차근 적용해볼까요?

EMP 테이블에서 allen 의 연봉보다 높은 연봉을 받는 사람의 정보 출력

Step 1) 먼저 allen 의 연봉을 확인합니다.

Step 2) 위에서 확인한 연봉을 기준으로 쿼리 작성합니다.

=> Allen 의 연봉이 오른다면 이 식을 계속 사용할 수 없습니다. 변경될 때마다 계속 수정이 필요하죠.

      이 때, where 절에 상수를 사용하지 않고, 상수 대신 무언가의 결과를 재사용하고 싶을 때 서브쿼리를 사용합니다!

Step 3) 위의 두 쿼리를 합쳐줍니다. 

          * 1600 대신 ALLEN 의 연봉을 조회하는 쿼리문을 넣어줍니다.

 => WHERE 절에 상수를 사용하지 않았기때문에, 이 식은 더 이상 수정할 필요가 없게되겠죠?

               

2. Multi Row Sub Query (다중 행 서브쿼리)

다중 행 서브쿼리는 살짝? 좀 많이 헷갈립니다ㅠㅠ

IN : 같은 값을 찾음

>ANY : 최소값을 반환

<ANY : 최대값을 반환

<ALL : 최소값을 반환

>ALL: 최대값을 반환

 '=' 은 in 으로 대체

 '>' , '<' 은 any, all로 대체 가능

 EMP 테이블에서 A로 시작하는 직원과 같은 부서의 직원 출력

먼저, 이름이 A로 시작하는 직원이 있는 부서를 확인해봅니다.

두 개의 부서가 확인되네요. 비교의 대상이 두 개 이상은 대소비교가 불가합니다.

그럴 경우, in 연산자를 사용합니다. 같은 값을 찾는 연산자죠!

쉽게 풀어보면 아래와 같이 사용할 수 있겠죠? deptno 이 20 혹은 30인 행을 조회

 select *

   from emp

  where deptno in (20, 30);

이제, 다중 행 서브쿼리를 작성해보면, 아래와 같습니다.

서브쿼리의 결과가 여러 행일 경우 '=', '<', '>' 와 같은 대소비교가 불가합니다.

10 = 20, 30 (?),

10 > 20, 30(?),

10 < 20, 30(?) 

이럴 때 다중 행 서브쿼리의 in 연산자를 사용해주면 됩니다!

그러면 원하던 결과인 EMP 테이블에서 A로 시작하는 직원과 같은 부서(20, 30)의 직원이 출력되겠죠?

 EMP 테이블에서 A로 시작하는 직원의 연봉보다 높은 직원 출력         

먼저, A로 시작하는 직원의 연봉을 확인합니다.

이번에도 결과가 두 개죠? 저 두 연봉보다 높은 직원을 조회하려면.. 일단 단일행 비교 연산자를 사용할 수 없겠네요.

select *

 from emp

where sal > min(1100, 1600); 

이렇게 쓰면 Error 입니다!

1100 과 1600 중 최소를 선택하기 위해 min 을 where 절에 사용하면 에러가 발생합니다.

왜냐! 그룹 함수는 where 절에 사용할 수 없기 때문이죠.

이럴 때! 다중 행 서브쿼리의 연산자 any와 all 표현식으로 대체해야합니다.

> any(a, b) : a,b 중 최소보다 큰

> all(a, b) : a,b 중 최대보다 큰

< any(a, b) : a,b 중 최대보다 작은

< all(a, b) : a,b 중 최소보다 작은

1100, 1600 을 대입해보면,

 > any 는 크다와 결합하면 최소를 리턴, 1100 또는 1600 이상이면 1100 리턴

 > all 은 크다와 결합하면 최대를 리턴, 1100 또는 1600 이상이면 1600 리턴

 < any 는 작대와 결합하면 최대를 리턴, 1100 또는 1600 이상이면 1600 리턴

 < all 은 작다와 결합하면 최소를 리턴, 1100 또는 1600 이상이면 1100 리턴

너무 헷갈리죠.. 헷갈리면 where 절이 아닌! 서브쿼리에 min, max 를 사용해도 문제 없습니다.

성능상으로 차이가 없기때문에 해석하기 편하고, 이해하기 편하게 작성하는게 좋습니다.

다시 한번, where 절에는 그룹함수 min, max 사용 불가!

select *

 from emp

where sal > any(1100, 1600); 

서브쿼리에 min, max 를 사용할 때와, any, all 을 사용할 때의 결과는 당연히 같습니다.

                                          <서브쿼리에 min 을 사용한 결과>

                                           <any 를 사용한 결과>

               

3) Multi Column Sub Query (다중 컬럼 서브쿼리)

다중 컬럼 서브 쿼리도 정말정말 중요하다고 합니다.

여러가지 쿼리들을 비교하면서 살펴보기 위해,

'인라인 뷰', '다중 컬럼 서브쿼리',  '상호연관 서브쿼리' 와 비교하면서 진행해봅시다!

 Q1. EMP 테이블에서 부서별 최대 연봉자의 이름 조회

 => 마음같아선 다음 식에 ename 을 넣고싶지만.. 문법상 오류로 Error 가 발생합니다.

      이를 문제를 해결하기 위해 서브 쿼리를 사용해봅시다!

select deptno, max(sal), ename??(X)

  from emp

 group by deptno;

 

해결방법 1) Inline View (인라인 뷰) * 대체적으로 인라인뷰가 우수하지만, 간혹! 다중 컬럼 서브쿼리가 좋을 때도 있습니다. 

 => 부서별 최대 연봉 결과를 테이블처럼 사용하였습니다. 

      그 후, 최대 연봉 결과와 같은 연봉을 가진 직원을 스캔해서 뽑아준 것이죠!

해결 방법 2) Multi Column Sub Query (다중 컬럼 서브 쿼리)

 => 서브 쿼리의 결과가 여러 행이므로 '=' (eqaul) 은 사용 불가합니다. 그래서 in 연산자로 대체!

      첫 번째 사용된 서브 쿼리는 생략이 가능한데, 항상 참의 조건이기 때문입니다.

      그리고 어차피 두 번째로 사용된 서브쿼리에서 deptno 기준으로 Grouping 해주기때문이죠.

 => 아래는 첫 번째 서브쿼리를 생략한 모습입니다. 하지만.. 가만보니 결과값은 어떨결에 똑같이 나왔지만,

      이 식은 SAL 컬럼만 비교하므로, 부서 별 비교도 필요합니다. 즉. DEPTNO 과 SAL 동시에 비교해야할 필요가 있죠

      이럴 때 동시에 컬럼을 비교하기 위한 비교식이 다중 컬럼 서브 쿼리라는 것입니다~!

 => 아래와같이 부서번호, 급여를 세트로 비교해주는 것입니다!

      이게 바로 다중 컬럼 서브 쿼리라는 것입니다~!

                          

Q2. STUDENT 테이블에서 각 학년별로 몸무게가 가장 많이 나가는 학생의 이름, 학년, 몸무게 출력

1) Inline View (인라인 뷰)

 => 서브 쿼리에서 학년별 가장 많이 나가는 몸무게를 구한 후, 테이블처럼 사용합니다.

      서브 쿼리와 JOIN을 해주고 학년(GRADE), 무게(WEIGHT) 를 비교해주는 모습입니다.

2) Multi Column Sub Query (다중 컬럼 서브 쿼리)

 => 서브 쿼리에서 학년별 가장 많이 나가는 몸무게를 구한 후, 학년(GRADE), 무게(WEIGHT) 를 세트로 비교해주는 것입니다!

      인라인 뷰 보다 뭔가 더 간결해보이지만.. 뭔가 헷갈리고 어렵죠?ㅠㅠ

 

 

 

Q3. STUDENT 테이블에서 각 학년별로 평균 몸무게보다 많이 나가는 학생의 이름, 학년, 몸무게 출력

1) Inline View (인라인 뷰)  * 인라인 뷰는 그룹별 대소비교가 가능

 => 서브 쿼리에서 학년별 평균 몸무게를 구한 후, 테이블처럼 사용합니다.

      서브 쿼리와 JOIN을 해주고 학년(GRADE), 무게(WEIGHT) 를 비교해주는 모습입니다.

      다만, 앞 예시에서는 equal 로 비교했다면 여기서는 대소비교를 하는데,

     인라인 뷰에서는 그룹별 equal 비교와 대소비교가 모두 가능하다는 것을 알 수 있습니다.

 

2) Multi Column Sub Query (다중 컬럼 서브 쿼리) * 다중 컬럼 스브 쿼리는 그룹별 대소비교 불가능

 => 서브 쿼리에서 학년별 평균 몸무게를 구한 후, 학년(GRADE), 무게(WEIGHT) 를 세트로 비교해주는 것입니다!

      하지만 실행해보면 '연산자의 지정이 부적합합니다' 라는 Error 메시지가 뜨게 됩니다.

      즉, 멀티 컬럼 서브 쿼리로는 그룹별 대소비교가 불가능하다는 뜻이죠!

      이 해결 방안으로, 상호연관 서브쿼리라는 것이 있답니다.

 

 

3) 상호연관 서브쿼리  * 상호연관 서브쿼리는 그룹별 대소비교가 가능

=> 메인 쿼리와 서브 쿼리가 계속 정보를 주고 받는다는 의미로 상호연관 서브쿼리라고 합니다.

      다만.. 계속 모든 행마다 정보를 주고받고, 그룹 연산을 각 행마다 반복하므로 데이터의 양이 많아질수록 불리한 쿼리겠죠.

      반대로 소수의 데이터에는 상당히 빠른 속도, 좋은 성능을 보여준다고 합니다.

      초록색 줄이 쳐져있는 's1.grade = s2.grade' 이 부분에서 각 테이블의 모든 건수(행)마다 반복됩니다.

      메인 쿼리의 s1.grade 값을 모든 행마다 요청하면서  s2.grade 값과 비교하는 것이죠.

      그러면서 평균 몸무게보다 높은 정보가 발견되면 출력해주는 것이겠죠?

      이 예시에서는 각 학년에 대한 정보를 구하므로(grade 를 계속해서 비교) 학년에 대한 그룹별 연산이 필요 없습니다.

      그러므로 group by grade 문은 생략 가능합니다!

 

 

4) Scalar Sub Query (스칼라 서브쿼리)

 

스칼라 서브쿼리는 select 절에 사브쿼리를 사용하여 하나의 컬럼처럼 사용하기 위한 목적이 있습니다.

조인(Join)의 대체 표현식으로도 자주 사용됩니다. 

하지만, 성능도 좋은 편이 아니고, 주 표현식이 아니므로 참고만 해주시면 되겠습니다!

 

 Q1. EMP 테이블과 DEPT 테이블을 사용하여 각 직원의 이름, 부서번호, 부서명 출력

1) JOIN

 => EMP 테이블과 DEPT 테이블을 조인(JOIN) 한 결과입니다.

      JOIN 이 이해가 안 가신다면 아래 링크를 참고해주세요!

     [SQL] 조인의 모든 것 - Join, Cartesian Product, EQUI, Non-Equi, Outer, Sefl <- 이동

 

2) Scalar Sub Query  

 => 스칼라는 하나의 라는 의미를 가지고 있습니다. 그렇듯, 서브쿼리의 결과가 하나이길 기대하는 쿼리이죠.

      결과는 당연히 조인(JOIN)과 똑같지만, FROM 절에 DEPT 테이블을 사용하지 않고 필요한 데이터만 추출해서

      바로 SELET 절에 사용해 준 모습입니다.

 

  

 Q2. EMP 테이블과 DEPT 테이블을 사용하여 각 직원의 이름, 부서번호, 부서명 출력

1) Scalar Sub Query  

 => 아우터 조인(Outer Join) 에서 사용했던 예시인데, 

      아우터 조인은 null 값이 생략되는 정보도 포함해서 표시하기 위한 조인이라고 했었죠?

      그러나! 여기서는 아우터 조인을 사용하지 않았는데,

      왜? 어떻게? 매니저(선배 직원)가 없는 직원도 출력되는지 의문이 들 것입니다.

      아우터 조인을 사용하지 않았지만, 아우터 조인이 적용된 것 처럼 된 것은 바로바로~~

      메인 쿼리에 where 절이 없으므로 모든 행이 서브쿼리로 넘어갈 것입니다.

      그렇게 되면 서브쿼리는 null 값을 받게 되고 당연히 null 값을 null 로 출력해주게 되겠죠?

      이렇게 마치 아우터 조인을 적용한 것처럼 보이는 것이랍니다!

 

 

 

참고: KIC 캠퍼스 머신러닝기반의 빅데이터분석 양성과정