[Database] 서브쿼리 정리 (스칼라, 인라인뷰, where절 서브쿼리)

서브쿼리

• 하나의 SQL 문 안에 포함되어 있는 또 다른 SQL 문을 말한다.
• 반드시 괄호로 묶어야 한다.
  • ex) select 안의 select 문, insert, update, delete 안의 select 문
• 서브 쿼리 사용가능한 곳
  1. select 절
  2. from 절
  3. where 절
  4. having 절
  5. order by 절
  6. 기타 DML (insert, delete, update, merge)
     group by는 사용 불가

서브 쿼리 종류

1. 동작하는 방식에 따라 분류

• 비연관 서브쿼리
  • 서브쿼리가 메인쿼리 컬럼을 가지고 있지 않은 형태의 서브쿼리를 의미한다.
  • 메인쿼리에 섭쿼리가 실행된 결과 값을 제공하기 위한 목적으로 사용
• 연관 서브쿼리
  • 서브쿼리가 메인 쿼리 컬럼을 가지고 있는 형태의 서브쿼리
  • 일반적으로 메인쿼리가 먼저 수행된 후에 서브쿼리에서 조건이 맞는지 확인하고자 할 때 사용한다.

2. 위치에 따른 분류

위치에 따른 분류는 굉장히 중요하다.'

• 스칼라 서브쿼리
  • select 에 사용하는 서브쿼리를 의미한다.
  • 서브쿼리 결과를 마치 하나의 컬럼처럼 사용하기 위해 주로 사용한다.

출처: 홍쌤의 데이터 랩

• 인라인뷰
  • from 절에 사용하는 서브쿼리를 의미한다.
  • 서브쿼리 결과를 테이블 처럼 사용하기 위해 주로 사용한다.

• where 절 서브쿼리
  • 가장 많이 사용하는 서브쿼리로 가장 일반적인 서브쿼리다.
  • 비교 상수 자리에 값을 전달하기 위한 목적으로 주로 사용한다.
  • 리턴 데이터의 형태에 따라 단일행 서브쿼리, 다중행 서브쿼리, 다중 컬럼 서브쿼리, 상호 연관 서브쿼리로 구분한다.

where 절 서브쿼리 종류

1. 단일행 서브쿼리

• 서브쿼리 결과가 1개의 행이 리턴되는 형태

위와 같이 평균 급여를 확인해 보면 2073. ,,,이 나온다.

해당 쿼리를 where 절에 서브쿼리로 집어 넣어 평균 급여보다 높은 급여를 받는 직원을 찾아낼 수 있다.

 

 

2. 다중행 서브쿼리

• 서브쿼리 결과가 여러 행이 리턴되는 형태다
• =, >, < , 과 같은 단일 행과 비교하는 연산자는 사용 불가하다.
• 서브쿼리 연산자를 하나로 요약하거나 다중 행 서브쿼리 연산자를 사용해야 한다.
  • ex) IN, > ANY (최소값을 반환한다.) < ANY (최대값을 반환한다.),
    <  ALL (최소값을 반환한다.), > ALL (최대값을 반환한다.)

ALL 이 붙으면 2000보다 크고, 3000보다 큰 것 즉 제일 큰 값보다 큰 것만 반환한다는 의미다.

 

ANY는 or의 의미다. 즉 2000보다 크기만 하면 true 이므로 > ANY 는 최소값보다 큰 행들을 반환하라는 의미다.

 

위와 같이 집계 함수를 사용해서 비교할 수도 있고

 

> ANY를 사용해서 최소값을 비교할 수도 있다.

 

3. 다중컬럼 서브쿼리

• 서브쿼리 결과가 여러 컬럼이 리턴되는 형태
• 메인쿼리와의 비교 컬럼이 2개 이상
• 대소 비교 전달 불가하다. (두 값을 동시에 묶어 대소비교 할 수 없기 때문)

부서 별로 최대 급여자가 누군지 궁금해 다음과 같은 쿼리를 실행했지만

 

최대 급여를 받는 사람이 누군지는 못 알아냈다.

 

다음과 같이 서브쿼리로 where 절에 전달해준 후 

IN 연산자를 통해 DEPTNO 부서 번호와 SAL 봉급이 일치하는 사원의 행만 남기게 만들었다.

 

4. 상호연관 서브쿼리

• 메인쿼리와 서브쿼리의 비교를 수행하는 형태다.
• 비교할 집단이나 조건은 서브쿼리에 명시한다 .

 

 

다음과 같이 where 절에 서로 다른 테이블로 취급하여 

조건을 걸고 부서번호가 같은 사람들끼리 묶어 

부서별로 대소 비교를 수행할 수 있다.

 

만약 E1에서 부서번호가 30번인 사원이 있다면

서브쿼리에서 부서번호가 30번인 사원들의 평균을 구해 리턴할 것이다.

 

이후 메인쿼리 where 절에서 비교하면 된다.

 

인라인뷰

• 쿼리 안의 뷰 형태로 테이블처럼 조회할 데이터를 정의하기 위해 사용한다.
• 테이블명이 존재하지 않기 때문에 다른 테이블과 조인 시 반드시 별칭을 사용해야 한다.
• where 절 서브쿼리와 다르게 서브쿼리 결과를 메인 쿼리에서 사용 가능하다.
• 인라인뷰와 메인쿼리 테이블을 조인할 목적으로 주로 사용한다.

다음과 같이 최대 급여를 인라인뷰로 만들고

 

인라인 뷰와 메인쿼리 테이블을 부서번호로 조인시킨 후에

 

최대급여와 급여가 같은 사원을 출력하면 된다.

 

여기서 MAX(SAL)은 그룹함수다 즉 where절에서 사용이 불가능 하므로, 별칭을 만든 후 사용해야 한다.

 

스칼라 서브쿼리

• select 절에 사용하는 쿼리로, 마치 하나의 컬럼처럼 표현하기 위해 사용한다.
• 각 행마다 스칼라 서브쿼리 결과가 하나여야 한다.
• 조인의 대체 연산이다.
• 스칼라 서브쿼리를 사용한 조인 처리 시 outer join이 기본이다. 

두 번재 예제는 상위 매니저의 이름을 가져오는 예제다

 

해당 예제에서 상위 관리자가 존재하지 않는 사원이 있을 수도 있다.

 

그러한 사원의 경우 null이 출력되어 아우터 조인 같다고 얘기한다.

 

서브쿼리 사용 시 주의 사항

특별한 경우를 제외하고는 서브 쿼리절에 ORDER BY 절 사용이 불가능 하다.

 

단일 행 서브쿼리와 다중 행 서브쿼리에 따라 연산자를 잘 선택하도록 하자

 

 

참고 영상, 사진 출처: https://www.youtube.com/watch?v=hLvv0GN0rT8