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

메인쿼리(가장 바깥에 있는 쿼리) 안에 있는 쿼리를 서브쿼리라고 하며 서브쿼리는 여러 depth로 표현이 가능하다.

select (select ...)  스칼라 서브쿼리, 서브쿼리를 컬럼화 하겠다는 의미이며,

from (selelct ...) 인라인뷰이며, 서브쿼리를 테이블화 하는 것이다.

where 컬럼1 =(in) (select ...) 일반 서브쿼리로 가장 대표적인 형태이며 상수화를 의미한다. 만들어진 정보를 토대로 테이블을 찾는 다는 의미이다.

 

 

형태에 따른 분류를 하게 되면

 

1. 단일행 서브쿼리

 

이런 식으로 하나의 대상이 오는 것도 가능하다.

select *
  from emp
 where (deptno, sal) = (10, 5000);

 

2. 다중행 서브쿼리

 

3. 다중컬럼 서브쿼리

 

 

오늘 배운 내용이, 상호연관 서브쿼리이다.

4. 상호연관 서브쿼리

상호연관 서브쿼리가 어려운데, 상호연관이라는 의미는 결론적으로 메인쿼리와 서브쿼리가 정보를 서로 주고 받는 것이다. 즉 메인쿼리와 서브쿼리의 정보 교환 방식으로 진행하는데 다중컬럼 서브쿼리, 인라인뷰의 대체 표현식이기도 하다. 

장점으로는 다중컬럼 서브쿼리는 대소비교가 되지 않는데 상호연관 서브쿼리는 대소비교가 가능하다.

대소비교를 해야 하는 문제라면 상호연관 서브쿼리, 인라인뷰, 동등비교라면 다중컬럼 서브쿼리, 인라인뷰, 상호연관 서브쿼리 세 개 전부로 다 풀 수 있다.

나중에는 개인적인 선호도가 생길 수는 있으나 본인에게 익숙한 것 위주로 하다보면 나머지가 기억이 나지 않을 수도 있다.

 

 

예제로 살펴보자.

부서별 평균급여보다 높은 이름, 부서번호, 급여를 출력하여라.

이렇게 될 것 같지만 이렇게 풀 수 없다. 다중컬럼 서브쿼리는 동등비교만 가능하기 때문이다.

deptno와 sal을 동시에 비교하는데 각각의 연산자를 전달할 수가 없다.

즉 avg(sal)보다 큰, 이라는 연산자는 전달이 가능하지만 deptno보다 큰 으로도 연산자가 전달이 되기 때문이다.

deptno는 동등비교, sal은 대소비교이므로 이 둘을 분리해보자.

 

 

 

여기서 첫 번째 서브쿼리로 들어간 select deptno는 항상 참이라서 무시되는 불필요한 조건이다.

실행은 되지만 의미가 없다.

두 번째 서브쿼리로 들어간 select avg(sal)은 3가지가 출력되는데(deptno가 10, 20, 30으로 group by 되므로) sal은 하나이므로 비교가 되지 않아 오류가 생긴다.(ORA-01427: 단일 행 하위 질의에 2개 이상의 행이 리턴되었습니다.)

sal이 avg(sal) 해당조건에 부합하는지 알려면 서브쿼리가 좀 더 자세하게 실행되면 좋겠는데, 그렇게 되려면 세 개 값 중에 어떤 값과 직접적으로 비교하면 되는지 알려주면 된다.

그래서 상호연관이라고 하는 것이다.

 

 

메인쿼리 sal과 비교해야하기위해 서브쿼리에서 정보를 빼와야 하므로 where e1.deptno = e2.deptno 를 명시하여 어느 부서의 sal인지 확인해야 한다. 여기서 where 절은 메인쿼리로 빼서는 안된다.

메인쿼리에는 e2가 없기 때문에 불가하다.

 

상호연관 서브쿼리 내부 실행 순서를 잘 이해해보자.

★상호연관 서브쿼리 내부 실행 순서

1. 메인쿼리 from의 emp (e1)을 읽는다(emp 스캔).

2. 메인쿼리 where에서 첫 번째 행이 해당 조건에 성립하는지 확인한다.

  1) sal을 확인한다(메인쿼리부터 실행되므로 가장 먼저 읽게되는 자료, 800)

  2) 서브쿼리 from의  emp(e2)를 읽는다(emp2 스캔).

  3) 서브쿼리 where실행한다. e2.가 대상이고 e1.이 상수이다. 그래서 e1의 deptno를 확인한다(20).

  4) avg(sal)을 확인한다(2175).

  5) sal>avg(sal) 조건을 체크한다.  800 > 2175  (얘는 탈락)

3. 나머지 행들은 2번 step을 반복하게 된다.

 

절대규칙으로 일대다를 비교할 수 없으므로 다 쪽에 조건을 세부적으로 걸어줘야 하는 것이다.

그리고 메인쿼리에 어떠한 이유에서든지  e2가 등장하면 안된다.

 

 

연습문제를 풀어보자.

연습문제 1. emp 테이블에서 직급(job)별 최소 급여를 받는 직원의 이름, 직급, 급여를 출력하여라.

동등비교이므로 다중컬럽 서브쿼리, 인라인뷰, 상호연관 서브쿼리 세 가지 방법으로 풀이하여라.

 

 

이제 좀 이해가 되는 것 같다.

 

 

다른 연습문제를 하나 더 풀어보자.

student 테이블에서 성별 평균몸무게 보다 적게 나가는 학생의 이름, 성별, 몸무게를 출력하여라.

이 문제는 대소비교이므로 인라인뷰, 상호연관 서브쿼리로 작성하여라.

 

 

복습후기

오늘 진도는 실습 등을 하느라 상호연관 서브쿼리만 배웠다.

월요일은 스칼라 서브쿼리를 배우는데 이게 더 어려울 것이라고 한다.

월요일까지 복습 잘해두고 월요일 잘 넘겨야겠다.