서브쿼리

하나의 쿼리에서 SELECT문 안에 또 다른 SELECT문이 있는 경우, 안에 포함된 SELECT문을 서브쿼리라고 한다.

 

서브쿼리의 유형

메인 쿼리의 어느부분에 위치하는가에 따라 구분하기도 하고 메인쿼리와의 연관성에 따라 구분하기도 합니다.

 

스칼라 서브쿼리

SELECT 절에 있는 서브쿼리

 

파생 테이블

FROM 절에 있는 서브쿼리

 

WHERE 절의 서브쿼리

 

메인쿼리와 서브쿼리의 연관성에 따라 다음 두가지로 분류

1. 연관성 있는 서브쿼리 : 메인쿼리와 서브쿼리를 조인하는 경우

2. 연관성 없는 서브쿼리 : 메인쿼리와 서브쿼리를 조인하지 않는 경우

 

스칼라 서브쿼리

단일 칼럼이 와야하며, 여러 개의 칼럼을 기술 할 수 없다.

또한, 단일 건(로우)를 반환해야 하고, 여러 로우를 반환하면 오류가 발생한다.

그래서 조인의 형태로 서브쿼리를 사용하게 된다.

서브 쿼리문을 통해 조인하고, 정보를 얻어온 모습

 

이렇게 서브쿼리문에 두개 이상의 컬럼을 가져오려고 하면, 오류가 발생하게 된다.

 

또한, 이렇게 조인 없이 가져오려고 해도 오류가 발생한다.

또 스칼라 서브 쿼리를 사용하는 경우를 확인해보자.

 

departments 테이블의 구성은 이렇다. 

이럴때, 다음과 같은 쿼리문을 작성하면 IT가 누락된다.

왜냐하면 IT부서에는 관리자가 존재하지 않기때문이다.

만약 IT부서까지 조회하고 싶다면 어떻게 해야할까?

이렇게 작성하면, departments의 해당 컬럼을 모두 가져오되, 서브커리에 해당하는 로우가 옆에 반환되므로 모두 조회된다.

 

파생 테이블

메인쿼리의 FROM 절에 사용되어 하나의 테이블 역할을 하는 서브쿼리

 

위는 부서번호와 해당 담당자의 번호, 이름을 조회한 쿼리이다.

이런 조인 쿼리가 있다고 했을때, 부서명까지 알고싶다고 하자.

그러려면 departments 테이블과 조인해야 한다.

 

위와 같이 from절에서 조인한 테이블을 가져와서, from절의 테이블의 컬럼을 가져오고, 이외의 컬럼은 department에서 가져왔다. 

이때, 조인한 테이블은 b라는 이름을 줘서 다시 사용할 수 있게 했다.

 

혹은 파생 테이블은 어떤 집계값을 한번에 구할 수 없을 때 사용한다.

 

위 코드는 2015년 이후 영화들의 전체 매출에 대한 순위권 영화의 매출 차지비중을 구하는 쿼리문이다.

년도별 1,2,3순위 순위 제목, 매출액은 쉽게 구할 수 있다. 그러나 매출액 비중을 구하려면 전체 매출액을 구하고, 각 영화의 매출액을 이것으로 나누어야 한다. 따라서 두 단계를 구해야 한다.

 

LATERAL

: FROM이나 INNER JOIN 뒤에 붙어서 이전에 선언한 테이블을 참조할 수 있게 해줌

 

WHERE 서브쿼리(조건 서브쿼리)

where절의 일부에 서브쿼리를 사용하는 것

조건 서브쿼리가 반환하는 값은 단일 값이어야 한다. 즉 로우와 칼럼이 1개여야 한다.

select ranks, movie_name, sale_amt
from box_office
where year(release_date) =2019
and sale_amt >=(select max(sale_amt) from box_office where year(release_date)=2018);

2019년 에 개봉한 영화 중 2018년도 개봉 영화 중 최고매출액보다 큰 것의 랭크와 영화이름, 매출액을 반환하는 쿼리

 

그렇다면 만약에 2018년도의 매출액 1위에서 3위까지의 영화들 보다 큰 값을 구하고 싶다면 어떻게 할까?

 

이렇게 쿼리를 작성하면, 아래와 같은 오류가 난다.

그러면 이 중에서 아무거나 잡아서 비교하고 싶을 때는 어떻게 해야할까

ANY(SOME)를 사용하면 여러개의 로우 중 하나와 비교한다.

그리고 이 외에도 ALL이 있다.

ALL은 비교하려는 칼럼의 값이 조건 서브쿼리가 반환하는 모든 값에 대해 조건을 만족해야 한다.

 

IN

IN 연산자는 비교할 칼럼 값에 비교대상 값 중 하나라도 포함되어 있다면 모두 조회하는 연산자이다.

예를들어 column IN('a','b','c')라고 할 경우, column값이 a 또는 b 또는 c인 것ㄴ을 조회한다.

 

2018년과 2019년 에서 동일한 영화가 개봉한 경우를 찾는 경우를 들 수 있다.

select ranks, movie_name,director
from box_office
where year(release_date)=2019
and (movie_name,director) IN (select movie_name, director
from box_office
where year(release_date)=2018);

위와 같이 두 가지 칼럼을 동시에 비교하는 경우 소괄호로 묶어서 비교할 수 있다.

 

EXISTS

메인 쿼리 테이블의 값 중 서브쿼리의 결과 집합에 존재하는 건이 있는지를 확인하는 역할을 한다.

조건을 따로 확인하지 않고 서브쿼리 내에서 조인형식으로 조건을 부여한다.

 

EXISTS를 사용한 서브쿼리는 select문에서 따로 출력을 하지 않아도 된다.

위의 사진과 같이 select문의 결과값에 1을 입력해 준 모습이다.