[Oracle] Query문을 작성해 보자! (6) - 서브쿼리란? ( 스칼라 서브쿼리, 인라인 뷰, 중첩 서브쿼리)

 

 

 

1. 서브쿼리란? 

 

하나의 쿼리에서 SELECT 문 안에 또 다른 SELECT 문이 있을 때, 안에 포함된 SELECT 문을 서브 쿼리라고 한다. 꼭 SELECT가 아니더라도 INSERT, UPDATE 쿼리문에서도 쓰일 수 있다. 메인쿼리 안에 서브 쿼리를 넣어 좀더 디테일한 쿼리 작업을 수행하는 것으로, SQL문 내부에서 사용하는 SELECT문 이라고 생각해도 될거같다.

 

 

[서브쿼리의 분류]

 

서브쿼리	사용위치	설명
스칼라 서브 쿼리	SELECT 절	단일 칼럼, 단일 행을 반환 (1개의 값)
인라인 뷰	FROM 절	View와 사용적인 측면에서 동일함 (임시 뷰, 임시 테이블)
중첩 서브 쿼리	WHERE 절, HAVING 절	다중 칼럼 또는 다중 행을 반환

 

 

[서브쿼리의 공통 특징]

 

1. 서브쿼리는 모두 SELECT의 형태로 되어있다.

 

2. 작성시 ( ) 안에서 서브쿼리가 작성된다.

 

3. 메인쿼리에 서브쿼리를 표시하기 위해서는 별칭을 정해줘야한다. (AS "별칭"처럼...)

 

4. 서브쿼리에 ORDER BY 절은 올 수 없다.

(인라인 뷰 같은 경우는 ROWNUM, TOP-N 등의 함수랑 같이 쓰일때 ORDER BY가 가능하다.)

 

 

솔직히 3가지 전부 그냥 메인쿼리 안에 SELECT 쿼리문을 넣는 형태이다. 사용방식과 용도가 조금 다를 뿐이다. 이제 서브 쿼리가 무엇인지 알았고 어디에 쓰이는지 알았으니 이제는 직접 실습을 통해 활용해 보자.

---

 

 1. 스칼라 서브 쿼리 

 

스칼라 서브 쿼리는 SELECT절에서 사용되는 서브 쿼리이다.  우리가 JOIN을 할 때 다른 테이블을 참조하여 데이터를 합치거나 VIEW를 만드는데, 스칼라 서브쿼리 또한 비슷한 성질이다. 조회하고자 하는 테이블에서 다른 테이블에 있는 컬럼의 값을 표시하고 싶을 때 사용한다.

 

[예시]

 

두개의 테이블을 가지고서 스칼라 서브 쿼리가 어떻게 표현되는지 한번 알아보자.

 

 

문제 )   아이스크림 테이블을 조회할 때 <가격 설문조사 테이블> 에 있는 COMMENT 컬럼을  "PRICE_SURVEY" 라는 이름으로  함께 조회해보자.

 

 

먼저 오류가 뜨도록 일부러 저렇게 쿼리문을 작성해 보았다. 스칼라 서브 쿼리를 작성할 때 조건을 걸어주지 않으면 값이 여러개가 된다. 하지만 스칼라 서브 쿼리의 특성은 반드시 컬럼1개에 1개의 값 = 1개의 행이 조회 되어야 하기 때문에 WHERE 조건절을 써서 값이 1개가 나올 수 있도록 해줘야한다. (스칼라 서브 쿼리의 고유 특징!)

 

 

 

 

다시  WHERE 조건을 이용해 각 행마다 값이 하나만 나오도록 조건을 걸고, 조회를 해보면??

 

 

 

 

드디더 내가 원하는 결과가 나왔다. 가격에 맞는 설문조사 COMMENT가 "PRICE_SURVEY" 컬럼으로 추가되어 아이스크림 테이블에 표시되어진 것을 볼 수 있다.

 

밑에 NULL값이 표시된것은 스칼라 서브 쿼리의 특징으로 반드시 하나의 값은 반환하는데, 해당하는 조건에 맞는 값이 없다면 저렇게 NULL값으로 반환한다.

 

---

 2. 인라인 뷰 

 

인라인 뷰란 FROM 절에 포함되어있는 서브쿼리를 의미하는데, 하나의 SQL을 실행될 때 생성되고 SQL이 종료되면 사라지는 가상의 테이블(물리적으로 존재하진 않지만 사용자에게 있는 것처럼 보여지는)이다.  SELECT 절의 결과를 FROM 절에서 하나의 테이블처럼 사용하고 싶을 때 사용한다.

 

- 인라인 뷰는 뷰(View)와 동일하며 다른 점은 뷰(View)는 오브젝트이기 때문에 재활용이 가능하지만 인라인 뷰는 해당 쿼리문에서만 사용하는 임시 뷰이다.

 

 - 인라인 뷰는 테이블과 같이 인덱스가 없기 때문에, 데이터가 많으면 쿼리문이 느려질 수 있어서 사용 시 주의해야 한다.

 

" 조인 작업을 제거하거나 개별 쿼리를 단일 쿼리로 통합해서 복잡한 쿼리를 단순화 할 수 있다. 또한 복잡한 조인 연산시 조인의 타겟 테이블의 데이터 개수를 줄일 수 있습니다. " 

 

구글에 검색시 가장 먼저 뜨는 설명이다. 사실 잘 모르겠다. 조인 작업을 제거한다는 저 말이 이해가 안간다.왜 제거 해야하는지? 조인을 하든 인라인 뷰를 쓰든 결국 하나는 해야 하는건데, 머가 더 좋은건지는 모르겠다. 쿼리를 단순화 할 수 있다는 것도 크게 와닿지 않는다. 한번 직접 인라인뷰를 사용하여 테이블을 조회해봐야겠다.

 

 

[ 실습 ]

 

문제 ) 아이스크림 테이블에서 연령층에 따른 아이스크림 가격의 평균을 구해보자. (avg_price 컬럼)

 

 

 AGEGROUP 컬럼을 기준으로 가격의 평균을 구해서 아이스크림 테이블에 표시해야한다.

 

 

 

처음에는 위에처럼 쿼리문을 작성해보았다. 완벽하게 작성한게 아니라 에러가 날 것을 각오하고 조회를 해봤더니.. 신기한 결과가 나왔다.

 

 

왜 각각의 아이스크림마다 3번씩 연산을 한걸까? 중년층인데도 가격은 어린이, 전령층, 중년층 가격이 다 표시되어있다.

 

내가 생각했을때  평균 가격을 구해놓은 avg_price 컬럼이 아이스크림 테이블에서 어떠한 컬럼과도 1:1 매칭이 안되어있기 때문인거같다(조건) 그래서 3가지 연련층에 해당하는 평균값들이 각 행에 한번씩 다 연산되어, 행 하나당 3개의 값이 표시되는 이상한 조회 결과가 나온것이다. 그러면 다시 1:1로 매칭을 시켜서 조회 해보자.

 

 

 

저렇게 매칭을 시키면 중년층 = 중년층 / 어린이 = 어린이 로 맞춰저서 각각 해당하는 연령층에 맞춰 하나씩만 나오게 된다.

 

 

내가 생각하는 원리가 맞는지 모르겠다. 좀 더 인라인 뷰를 많이 사용해 봐야 감이 잡힐거 같다. 근데 되도록이면 인라인 뷰보다 조인을 해서 사용해라는데, 그 이유가 인라인 뷰와 같은 서브 쿼리는 FULL TABLE SCAN을 해서 성능의 저하가 일어난다고 한다. 나중에 많은 데이터가 있는 작업을 할때 속도 저하가 생긴다면 서브 쿼리를 의심해 봐야겠다. 

 

 

---

 

3. 중첩 서브 쿼리 

 

WHERE절과 HAVING에서 사용되어지는 서브 쿼리를 말한다. 일반 서브 쿼리라고도 부르는데, SELECT절의 결과를 하나의 변수(상수)처럼 사용하고 싶을 때 사용한다. 변수처럼 사용 된다는 말은 A = B, C >= D 이렇게 비교 연삭식에서 쓰이는 A, B, C, D 이 친구들이 각각 변수이다. 즉, SELECT로 조회된 값이 하나의 문자나 숫자값으로 이용된다는 말 같다. 한번 예시를 통해 알아보자. 

 

 

 

1) 단일 행 서브 쿼리 

 

연산자를 통하여 값을 비교하는 가장 기본적인 조건절. 이 때 사용되는 서브 쿼리를 단일 행 서브 쿼리라고 한다. 비교하는 값이 하나일때는 ( =, <=, >= ) 이러한 비교 연산자를 이용하여 비교가 가능하다.

 

 

아이스크림 테이블에서 TASTE 컬럼이 "호두맛" 에 해당하는 NAME는 "호두마루"이다. 단 하나의 값이기 때문에 서브 쿼리의 규칙에 어긋나지 않고, WHERE 조건문에서 하나의 변수(값)으로 사용 가능해진다.

 

 

※ 주의!  위에서도 말했듯이 서브쿼리는 반드시 하나의 값이 반환 되어야 하기 때문에, 만약 TASTE의 값 중 겹치는게 있다면(예를들어 "팥맛"은 두개) more than one row 라는 에러가 뜬다.

 

사실 위에는 예시를 들기위해 굳이 쓰지 않아도 되는 서브 쿼리를 사용한 것이다. 위 그림처럼 서브 쿼리를 사용하지 않으면  훨씬 간단하게 내가 원하는 값, 2개 이상의 값도 얻을 수 있다.

 

그럼에도 서브 쿼리를 사용하는 이유는, 저렇게 단순하게 숫자를 입력하거나 문자를 입력하는 것이 아니라 다른 테이블에서 계산된 결과, 또는 참조되는 결과를 통해서 무언가 값을 도출하기 위함일 것이다.

 

---

 

 

2) 다중 행 서브쿼리 

 

위에서 단일 행 밖에 안된다고...

굳이 서브 쿼리를 써야하느냐고... 머라고 했는데.

 

서브 쿼리를 사용하면서 여러개의 행을 조회할 수도 있다. 여러 행의 값을 조건으로 사용하고 싶다면 단순 비교 연산자가 아닌, 다중 연산자로 바꿔주면 된다. 

 

 

[다중 연산자]

 

■ ANY 연산자(만족하는 값이 하나라도 있으면 실행)

IN , = ANY	' = ' 의 대체(동일)
> ANY	값들 중 최소값 보다 크면 결과를 리턴
>= ANY	값들 중 최소값 보다 크거나 같으면 결과를 리턴
< ANY	값들 중 최소값 보다 작으면 결과를 리턴
>= ANY	값들 중 최소값 보다 작거나 같으면 결과를 리턴

 

■ ALL 연산자(모든 값을 만족해야 실행)

> ALL	값들 중 최대값 보다 크면 결과를 리턴
>= ALL	값들 중 최대값 보다 크거나 같으면 결과를 리턴
< ALL	값들 중 최대값 보다 작으면 결과를 리턴
<= ALL	값들 중 최대값 보다 작거나 같으면 결과를 리턴
<> ALL	모든 값들과 다르면 결과를 리턴

 

 

 

[예시 1]

 

연령대가 중년층인 데이터를 조회해보니 4개의 행이 반환된다. 그러면 4개의 행은 어떻게 WHERE에서 조건으로 사용할 수 있을까? 

 

 

IN을 통해서 ' = ' 과 같은 효과를 낼 수 있다. 서브 쿼리에서 여러개의 행을 조건으로 사용하고 싶다면 IN, ANY, ALL을 활용해보자

 

 

 

[예시 2]

 

 

MAX 함수를 이용하여 중년층에 해당하는 가격 중 가장 큰 값인 2,000을 구했다. 이유는 IN말고도 ANY나 ALL을 어떻게 사용하는지 설명하기 위해서이다. 그러면 서브 쿼리와 같이 사용을 해보자. 

 

 

 

ALL을 쓰게 되면 서브쿼리에서  MAX값인 2000만이 조건이 되어, 단 하나의 값으로 연산을 한다. 

 

 

 

반대로 ANY는 서브쿼리 안에 있는 값들 중에서 하나라도 만족하는 것이 있으면 전부 조회킨다.

 

---

 

3) 다중 컬럼 서브쿼리 

 

이름 그대로 서브 쿼리의 컬럼이 하나가 아니라 두개 이상일 때 쓰이는 방법이다. 서브 쿼리에서 반환되는 컬럼의 개수와, 메인 쿼리의 WHERE 조건절에서 동일한 개수의 컬럼과 비교하여 데이터를 조회한다.  

 

 

[실습]

 

문제) 각 연련층 가장 높은 금액과 일치하는 아이스크림의 정보를 조회하여라.

 

 

 

먼저 각 연령층에서 가장 높은 금액을 조회해봤다. 이녀석을 서브 쿼리로 사용할 것이다.

 

 

 

가장 기본적인 " 다중 컬럼 서브 쿼리" 의 형태이다. 저렇게 WHERE절 뒤 ( ) 안에 비교할 컬럼을 넣는다. 그리고 서브 쿼리에도 컬럼이 2개 이상이니 IN연산자를 통해서 비교해야한다.

 

서브 쿼리에서 조회되는 컬럼 2개와, 메인 쿼리의 조건문에서 사용되는 컬럼 2개를 동시에 비교하여 일치하는 데이터를 조회했다. (1500원 아이스크림은 2개라서 2개)

 

---

 

지금 내가 위에 쓴 예시들 보다, 정말 복잡하고 힘든 쿼리문들을 앞으로 많이 볼 것이다. 이번에는 이정도로 간단하게 정리를 하고, 다음번에 좀 더 심화과정으로 20줄 넘어가는 복잡한 쿼리문을 이용해서 나의 실습을 해봐야겠다   잘시간이다~ 자고 내일하자!!

 

 

[도움 받은 사이트]

 

[Oracle] 오라클 서브쿼리 종류 및 사용법 (SubQuery)

서브쿼리 개념 알아보기

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

[Oracle] 오라클 ANY, SOME, ALL 연산자 사용법 완벽 정리