[복습] SQL | Oracle join 넘어 또 산, 서브쿼리(1) + 복습후기(20231102)

맨날 테스트하던 emp 테이블이 있다. 거기서 부서별 최고 연봉을 출력해보자.

 

드디어 이런 궁금증을 해결할 수 있는 날이 왔다.

"그래서 최고 연봉을 받는 사람이 누구야?"

 

궁금증 해결은 아래에서....ㅎ

 

 

다른 데이터를 살펴보자. movie_table에서 강원도는 어떤 연령대의 영화 이용 비율이 높은지가 궁금하다거나, 시도별 가장 높은 연령대 영화이용비율이 어느 대인지 궁금하다.

그렇다면 시도별, 연령대별 이용 비율을 확인하여야 한다.

 

즉 목적에 따라 1차적 가공이 필요한 데이터이다.

그래서 데이터를 해석하고 1차 가공을 할 수 있는 감각이 여기서 중요하다.

이제까지 공부를 해왔다면 여기까지는 바로 작성할 줄은 알아야 한다.

 

이제 여기서 강원도의 최고 이용비율을 보이는 연령대를 출력하기 위해서는 아래처럼 select문 안에 select가 또 들어가게 된다.

 

이런 건 R과 파이썬에서도 또 하게 되니 지금부터 잘 기억해야겠다.

 

그리고 또 이런 데이터들이 있다고 가정하자.

상품코드   상품명       상품분류코드   가격
 0001         갤럭시               10           10000 
 0002         아이폰               10           11000   
 0003        lg 노트북             20         90000
 0004      삼성노트북           20          81000

여기서 상품분류코드별로 최저가격인 상품명을 확인할 때에도 서브쿼리를 사용할 수 있다.

현재까지 배운 내용으로 보통 group by하여 최소 가격까지는 확인을 한다.

하지만 group by하는 순간 상품명이 사라진기 때문에 서브쿼리가 필요한 것이다.

부서별 최대 급여를 받는 직원의 문제랑 다를 바가 없다.

 

바로 여기까지가 서브쿼리의 첫 번째 사용 예시였다.

두 번째는 테이블 join관계를 단순화하기 위함이다.

이제부터 서브쿼리를 본격적으로 설명하면서 위의 두 예시들을 확인해보겠다.

 

 

서브쿼리란 메인쿼리 안에 있는쿼리(가장 바깥에 있는 쿼리를 메인쿼리라고 함)이다.

select
 from 
where
group by
having
order by

모든 구문에 서브쿼리가 들어갈 수 있다.

주로 select, from, where 절에서 가장 많이 사용한다.

 

1. 위처럼 서브쿼리의 위치에 따라 (현업용어): 스칼라 서브쿼리, 인라인뷰, 일반서브쿼리가 있다. ★ 꼭 알아야 할 용어이므로 기억할 것!

  1) select (select...): 스칼라 서브쿼리로 select 절에 오며 표현식을 대체한다.

  2) from (select...): 인라인뷰 라고 하며 가공된 데이터를 테이블화 한다.

여기서 '뷰' 라는 단어가 앞서 한번 나온적이 있다.

 

https://metime.tistory.com/10

[복습] SQL | Oracle 데이터 조회 명령어 select문

바로 이 글에서 FROM에 데이터가 저장된 테이블 또는 뷰(객체 종류 중 하나) 명 라고 필기해두었다.

from에 들어오는 서브쿼리는 서브쿼리라고 하지 않고 인라인뷰 라고 한다.

  3) where 대상 = (select...): 상수를 대체 하기 위함이며 일반 서브쿼리라고 한다.

 

 

 

 

2. 형태에 따라서는 다음과 같이 칭하는데, 현업 용어는 아닌 교재 용어이므로, 선생님께서는 현업가서 안다고 자랑하지 말라셨다ㅋㅋ. 네 가지가 있는데

  1) 단일행 서브쿼리

  2) 다중행 서브쿼리

  3) 다중컬럼 서브쿼리

  4) 상호연관 서브쿼리

차례로 하나씩 확인해보자.

 

 

  1) 단일행 서브쿼리: 서브쿼리 결과가 단 하나의 행을 리턴(하나의 컬럼)

=, >, < 연산자는 반드시 하나의 상수와 비교되므로 단일행 서브쿼리와 어울리는 연산자이다.

 

다음 예제에 적용해보자.

emp에서 ALLEN보다 급여가 낮은 직원의 이름, 급여를 출력하여라.

접근을 한 단계씩 해보자.

 

먼저 ALLEN의 급여를 알아야 한다.

 

1600이 출력되었다. 그러면 이 1600보다 낮은 직원을 찾으면 된다.

 

그런데 여기서, ALLEN의 급여가 오른다면? 

두 번째 코드를 계속 수정해가며 출력할 수는 없다.

위의 두 쿼리를 결합해보자.

 

위에서 where절에 들어가는 서브쿼리는 상수를 대체한다는 말이 이와 같은 뜻이었다.

이렇게 결합하면 ALLEN의 급여가 바뀌어도 변경되는 값을 반영할 수 있어 좋은 코드이다.

 

비슷한 예제를 하나 더 풀어보자.

SMITH와 같은 부서 직원들의 이름, 부서번호, 급여를 출력해보자(SMITH 포함).

 

만일 SMITH의 번호를 먼저 찾는 쿼리르 짜서 그 결과 상수 값 그대로 쿼리를 짜게 된다면, 만일 SMITH가 부서이동을 하게 된다면 그 변경 결과가 반영되지 못한 좋지 못한 코드가 될 것이다.

 

그런데 이걸 왜 단일행 서브쿼리라고 하는걸까.

이는 서브쿼리 결과가 단 하나의 행이 나오게 되기 때문이다.

=, <, >과 어울리는 쿼리는 반드시 단일행 서브쿼리를 사용하게 된다.

 

 

 

  2) 다중행 서브쿼리: 서브쿼리 결과가 여러 행이 리턴되는 경우의 형태

  =   → in으로 대체

<, > → all, any로 대체

위 뜻을 예제로 하나하나 확인해보자.

 

emp 테이블에서 이름이 M으로 시작하는 직원의 부서와 같은 부서 직원의 이름, 부서번호를 출력하여라.

단일행 서브쿼리에서 배운대로 코드를 일단 작성해보면

 

에러가 출력된다. 이 때 출력되는 오라 메시지는

ORA-01427: 단일 행 하위 질의에 2개 이상의 행이 리턴되었습니다.

이런 에러가 출력된 이유는 어울리지 않는 연산자를 사용했기 때문이다.

 

따라서 위에 = 은 in으로 대체하라고 하였기 때문에

in으로 수정하면 결과가 올바르게 출력된다.

 

다른 예제를 풀어보자.

이름이 M으로 시작하는 직원의 급여보다 높은 급여를 받는 직원의 이름, 부서번호, 급여를 출력하여라.

역시 위와 같은 에러가 발생하며 ORA 메시지도 같다. 

> 와 같은 부등호를 쓰고자 한다면 1. 서브쿼리 하나로 출력되게 하거나(단일행 서브쿼리로바꾸어서), 2. 연산자를 수정하여 다중행 서브쿼리로 나타내는 것으로 처리하면 된다.

먼저, 1. 서브쿼리 하나로 출력되도록, 즉 단일행 서브쿼리로 바꾸어서 출력하는 방법은 다음과 같다.

문제의 의미를 파악해보면 M으로 시작하는 직원들 보다 더 높은 급여를 받는 직원을 추출해야 하니까, M으로 시작하는 직원 중 가장 높은 급여를 받는 사람보다 더 높은 급여를 받아야 한다. 그럼 max 함수를 사용하여 아래와 같이 처리하면 될 것이다.

 

다중행 서브쿼리로 접근하고자 한다면 바꿔야 할 연산자로 수정하여보자.

일단 all연산자와 any 연산자의 차이를 살펴보자.

 

> all 의미는 최대 리턴

< all 의미는 최소리턴

> any 의미는 최소리턴

< any 의미는 최대리턴

 

이거 헷갈린다.. 특히 any 개념이 이해가 잘 되지 않는 것 같다. 그냥 all을 써야겠다.

다중행 서브쿼리로 수정하게 되면 이렇게 수정할 수 있다.

 

이런 내용은 SQLD 시험에도 나온다고 하니 그래도 다 공부는 해야겠네.

 

728x90

 

  3) 다중컬럼 서브쿼리. ★ 정말 중요하다. 업무적으로 많이 쓰이는 형태라고 하셨다.

드디어! 나왔다! ㅋㅋㅋ 본 게시글 가장 처음에 나온, 과연 누가 부서별로 최대 급여를 받는 것인가.

 

다시 문제를 보자. emp 테이블에서 부서별 최대 급여를 받는 직원 이름, 부서번호, 급여를 출력하여라.

 

STEP 1. 

 

위 쿼리가 상수가 된다.

 

그러므로 위 쿼리를 메인쿼리 상수로 전달하자.

STEP 2.

 

이제 위 쿼리에서 상수 대신에 서브쿼리를 전달하면 된다.

STEP 3. 

 

step 별로 잘 이해하면 될 것이다. 코드가 어려운 것이 아니라 데이터를 바라보는 시각을 갖는 것이 어려운 것이다. 그래서 엑셀을 많이 다루어본 사람이 코드를 잘 짠다고 말씀하셨다.

 

 

그러면 문제를 하나 풀어보자.

student 테이블에서 학년별 키가 가장 큰 학생의 이름, 학년, 키를 조회하자.

 

여기서 학생의 전공명까지 보고자 한다면,

이렇게 살을 붙여가는 느낌으로 진행하면 된다.

 

 

문제를 조금 바꾸어서, 

emp 테이블에서 부서별 평균 급여보다 적게 받는 직원의 이름, 부서번호, 급여를 출력하고자 할 때,

다중컬럼 서브쿼리는 대소비교가 되지 않는다. 이게 다중컬럼 서브쿼리의 단점이다.

의미적으로나 문법적으로 무의미하다. 일단 위 문제를 코드로 작성을 해보겠다.

 

에러가 발생한다. 부서를 대소비교 하는 것도 무의미하니 위 코드는 당연히 오류가 날 수밖에 없다.

이 때는 그냥 join을 써서 해결하면 되는 것이다.

그럼 부서별 평균급여를 출력하는 쿼리를 테이블화 하여 아래와 같이 진행하게 되는데,

 

메인쿼리 where 절에서 에러가 난다. where절에서는 그룹함수를 사용할 수 없기 때문이다.

우리야 avg(sal)이 서브쿼리에서 가져온 걸 눈으로 볼 수 있지만 컴퓨터는 그걸 알 수가 없기 때문이다.

그래서 서브쿼리 avg(sal)에 alias를 걸어준다.

 

이 방법이 바로 인라인뷰 인 것이다.

 

즉, 다중컬럼 서브쿼리로는 위 문제는 표현이 불가하다. 두 조건을 묶어서 비교하기 때문이다.

deptno와 sal을 하나로 묶어서 비교하기 때문에, 서로 다른 조건을 쓸 수가 없다.

그 해결은 분리이며, join은 출력의 목적을 가지고 있지만 필터링의 목적 또한 가지고 있기 때문에 from 으로 가서 처리를 하는 것이다.

인라인뷰를 통해 deptno와 sal이 각각 분리될 수 있도록 하였다.

★아주 중요한 해결 방법이므로 기억해두자.

 

그럼 마지막 문제를 풀며 마무리 해보자.

student 테이블에서 학년별 평균체중보다 많이 나가는 학생의 이름, 학년, 체중을 출력하여라.

 

하나의 팁인데, 서브쿼리를 드래그해서 컨트롤 + l(알파벳 L) 하면 서브쿼리만 실행된 결과가 result 창에 출력된다.

이렇게 출력하여 데이터가 잘 나왔는지 확인하면 될 것 같다.

 

역시 접근법은 데이터를 만들어두고 문법을 적용해야 한다는 흐름이 잡히면 인라인뷰를 쓰는 것이다.

 

  4) 상호연관 서브쿼리

는 아직 안 배운 것 같다. 내일 하려나...? 내가 놓친 건 아니겠지.

 

 

복습후기

코드 짜기에는 자고 일어나서 하는 게 제일 잘되는 것 같다.

맨날 아침 5시 40분에 일어나서 학원 갔다가 집에 돌아오면 22시 가까이 된다. 그 때 남은 복습을 이어서 하려면 너무 머리가 아팠다. 그래서 그냥 자고 일어나서 새벽에 복습했다. 

오늘은 두 시간 조금 넘게 자고 일어난 거긴한데, 초반에 이렇게 무리하면 오래 못 갈 것 같긴 하지만 지금은 가장 어려운 부분을 하고 있기도 하고 금요일이기도 하니 오늘만 무리해서 주말에 조금 늦잠 자고 다시 공부하면 될 것 같다.

 

서브쿼리가 종류도 많고 이름도 어렵고 접근 방법도 다양하여 머리가 아픈데 주말동안 정리를 잘 해두어야겠다.