서브쿼리

이건 뭐냐면 쿼리 안에 쿼리가 들어있는거다. 재귀함수는 자기자신을 호출하는거라 걔랑은 거리가 좀 있고, 쿼리 안에 쿼리가 들어가는 건 약간 마트료시카 비슷하다.

---

서브쿼리의 특징

1. select나 from에도 쓸 수 있지만 보통은 where에 많이 쓰인다.

2. 어째서인지 괄호로 감싸져 있다. 일단 오라클은.

3. from절에 쓰이는 서브쿼리를 제외한 다른 서브쿼리들은 메인쿼리에서 칼럼을 갖다 쓸 수 있지만, 그 반대는 안된다. 그러니까 메인쿼리는 서브쿼리에 있는 칼럼들을 갖다 쓸 수 없다. 무슨 지역변수냐고... (지역변수: 함수나 코드 블록 안에서 선언되어 해당 영역 안에서만 유효하고, 해당 영역이 종료되면 메모리에서 사라지는 변수/전역변수: 프로그램 전체 범위에서 접근하고 사용할 수 있는 변수)

서브쿼리의 분류

서브쿼리는 크게 1) 실행 결과로 몇 개의 행을 반환하느냐, 2) 실행 결과로 몇 개의 칼럼을 반환하느냐, 3) 메인쿼리와 연관이 있느냐에 따라 아래의 분류에 따라 분류한다.

 

1. 단일행/다중행 서브쿼리-실행 결과로 하나의 행을 반환하는 것이 단일행, 여러개의 행을 반환하는 것이 다중행

2. 단일칼럼/다중칼럼 서브쿼리-실행 결과로 하나의 칼럼을 반환하는 것이 단일칼럼, 여러개의 칼럼을 반환하는 것이 다중칼럼

3. 연관/비연관 서브쿼리-메인쿼리와 연관이 있으면 서브쿼리, 없으면 비연관 서브쿼리

 

일단 1, 2번만 놓고 봤을 때 가짓수는 단일행/단일칼럼, 단일행/다중칼럼, 다중행/단일칼럼, 다중행/다중칼럼으로 네 개고 여기에 연관 끼면 조합 여덟개 나온다.

---

단일행 서브쿼리

SELECT (column1), (column2), (column3) FROM TABLE1 WHERE (subquery1); --where문에 사용함

얘를 where절에 주로 사용하는거지 select나 from 뒤에도 온다. 특히 from 뒤에 오는 건 인라인 뷰라고 하고, 어디에 위치하건 서브쿼리는 괄호로 감싸져있는 게 국룰이다. 일단 오라클은 그렇다.

 

select CO.CUSTOMERS.CUSTOMER_ID, FULL_NAME, ORDER_ID, ORDER_TMS, ORDER_STATUS from CO.CUSTOMERS JOIN CO.ORDERS ON CO.CUSTOMERS.CUSTOMER_ID = CO.ORDERS.CUSTOMER_ID
WHERE CO.CUSTOMERS.CUSTOMER_ID = 11;

이너 조인까지 한 결과… 

select CO.CUSTOMERS.CUSTOMER_ID, FULL_NAME, ORDER_ID, ORDER_TMS, ORDER_STATUS from CO.CUSTOMERS JOIN CO.ORDERS ON CO.CUSTOMERS.CUSTOMER_ID = CO.ORDERS.CUSTOMER_ID
WHERE CO.CUSTOMERS.CUSTOMER_ID = (select CO.CUSTOMERS.CUSTOMER_ID from CO.CUSTOMERS where FULL_NAME = 'Michael Kim');

이게 서브쿼리다. 그래서 이게 뭔데요? 뭐가 서브쿼리인데요? 두번째줄 where 옆에 ()로 감싸진게 서브쿼리다. 그리고 이 서브쿼리의 의미가 뭐냐...

 

select CO.CUSTOMERS.CUSTOMER_ID from CO.CUSTOMERS where FULL_NAME = 'Michael Kim'->고객 이름이 Michael Kim인 사람의 고객 넘버를 달라

select CO.CUSTOMERS.CUSTOMER_ID, FULL_NAME, ORDER_ID, ORDER_TMS, ORDER_STATUS from CO.CUSTOMERS JOIN CO.ORDERS ON CO.CUSTOMERS.CUSTOMER_ID = CO.ORDERS.CUSTOMER_ID
WHERE (서브쿼리)->Michael Kim의 고객 넘버로 이 고객의 주문 내역을 줘라

 

이거다. 그러니까 일차적으로 서브쿼리의 연산을 먼저 진행하고 그 다음에 메인쿼리에서 연산을 진행하는거다. 그리고 서브쿼리의 연산인 미카엘 킴의 고객번호는 하나니까 단일행인거다. 그리고 다중행이면 where문에 =가 못 온다. 다중행용 연산자는 따로 있어서…

 

다중행 서브쿼리

select ENAME, JOB from SCOTT.EMP WHERE EMPNO IN (select EMPNO from SCOTT.EMP where MGR = 7839);

얘가 다중행이다. 왜 다중행이냐고? 저 괄호 안에 쟤를 돌리면 매니저 번호가 7839인 직원이 세 명 나온다. 그래서 =가 안 들어가고 IN이 쓰인건데, 다중행 서브쿼리용 연산자가 바로 IN이다. 어디서 많이 보셨다고요? 예, 저거 SQL 연산자로 나왔던 그겁니다.

 

다중행 서브쿼리에서 쓰는 연산자는 다섯개로 고정되어있다.

1. IN: 얘네들 중 하나라도 해당하면

2. ANY/SOME: 얘네들 중 하나정도는 만족하는 것(where > SOME 1, 2, 3, 4, 5면 일단 1만 넘기면 된다)

3. ALL: 전부 만족해야된다. (where > ALL 1, 2, 3, 4,5면 5보다 커야된다)

4. EXISTS: 이게 존재하는가? YES면 그 값을 반환하고 끝낸다.

 

고객의 아이디나 PK로 검색하는 건 단일행을 반환한다. 아이디와 PK는 둘 다 UNIQUE라는 제약 조건이 붙고, 둘다 NOT NULL이기도 하니까. 그런것처럼 어떤 행을 하나만 띡 집어올 수 있는 칼럼으로 서브쿼리를 짜면 그건 단일행이다. 하지만 이름과 같이 동명이인이 있을 수도 있는 걸로 서브쿼리를 짤거면 다중행으로 짜는 게 런타임에러 안 뜨고 낫다. 아리까리하면요? 서브쿼리로 쓸 거 한번 돌려보십쇼.

 

단일칼럼 서브쿼리

위에서 예시로 들었던게 다 단일칼럼 서브쿼리다. 왜? 서브쿼리 돌리면 칼럼 하나 주잖음. 

 

select EMPNO from SCOTT.EMP where MGR = 7839;

그니까 이 쿼리를 돌리면 행은 세개지만 칼럼은 하나인, 1*3 행렬이 나온다 이겁니다. 그래서

select ENAME, JOB from SCOTT.EMP WHERE EMPNO IN (select EMPNO from SCOTT.EMP where MGR = 7839);

아까 예시로 들었던 얘는 단일칼럼 서브쿼리다. 아니 그럼 다중칼럼은 뭔데요?

 

다중칼럼 서브쿼리

select * from SCOTT.EMP where (DEPTNO, SAL) IN (select DEPTNO, SAL from SCOTT.EMP where DEPTNO = 10 AND SAL > 1000);

얘는 다중행이면서 다중칼럼이라 다중행용 연산자가 들어간 것을 볼 수 있다. 근데 여기서도 다중행용이라고 연산자 다 쓸 수 있는 거 아니고 IN하고 EXISTS만 쓸 수 있다. 어떤 서브쿼리가 다중칼럼 서브쿼리면 where 옆에 비교할 칼럼이 튜플 형태로 묶여있다.

 

다중칼럼 서브쿼리를 쓰실 때는 주의할 점이 있는데, 비교할 칼럼 순서를 바꾸면 안된다. 해봤는데 오류난다.

 

연관 서브쿼리

보통 서브쿼리를 연산할때는 서브 먼저 하고 메인 가는데 얘는 메인 갔다가 서브 갔다가 메인 갔다가를 쿼리 끝날떄까지 해야 한다. 왜죠? 연관 서브쿼리는 메인쿼리가 정보를 줘야 뭘 하니까요.

 

select ENAME, SAL, DEPTNO from SCOTT.EMP M where SAL > (select AVG(S.SAL) from SCOTT.EMP S where M.DEPTNO = S.DEPTNO);

셀프 조인과 연관 쿼리의 대환장 콜라보레이션… 악 내눈! 일단 침착하고 서브쿼리부터 보자.

 

select AVG(S.SAL) from SCOTT.EMP S where M.DEPTNO = S.DEPTNO --서브쿼리
select ENAME, SAL, DEPTNO from SCOTT.EMP M where SAL > --메인쿼리

위에 있는 게 서브쿼리다. 그래서 연산을 하려고 했는데… 오잉? M이 뭔데 여기 껴있는거임? 그래서 서브쿼리 단독으로 연산을 못 한다. 그리고 M이 뭔지가 메인쿼리에 있어서 메인쿼리에서 DEPTNO(부서 번호)를 넘겨줘야 서브쿼리에서 연산을 돌린다. 그리고 그걸 다시 메인쿼리에서 넘겨받는 티키타카가 되는 것이 연관 서브쿼리. 그러니까 서브쿼리 단독으로 연산 안되면 연관이다.

 

스칼라 서브쿼리

스칼라 어디서 많이 들어보신 분? 나는 기벡은 안했지만 스칼라와 벡터의 차이는 안다. 벡터는 크기와 방향을 갖지만 스칼라는 크기 하나만을 갖고 있는데, 스칼라 서브쿼리도 이 스칼라의 특징에서 기인해서 쿼리 결과가 단일칼럼+단일행으로 딱 하나 나온다. 

 

다만 여기서 여러분들이 오해하시면 안되는 게, 서브쿼리의 결과가 뭘 돌리든 딱 하나만 나오는 게 아니라 행이 여러 개 나올 수 있다. 스칼라 서브쿼리의 '단일행+단일칼럼'은 행 하나당이다.

 

select EMPNO, ENAME, SUBSTR((select DNAME from SCOTT.DEPT where DEPTNO = A.DEPTNO),1,3) as DNAME from SCOTT.EMP A;

select에 쓰인 스칼라 서브쿼리다. 어? 이거 돌려보면 결과 엄청 나오는데요? 아니, 내가 위에도 말했지만 어떤 상황에서든 딱 하나 나오는 게 아니라니까…

 

select DNAME from SCOTT.DEPT where DEPTNO = SCOTT.DEPT.DEPTNO;

예? 서브쿼리만 떼서 돌려봤는데 네개잖아요! 아니 그러니까 어떤 상황이든 딱 하나만 나오는게 아니라고요... 부서 번호가 네 개니까 결과가 1*4로 나오는 게 맞다. 근데 쟤가 왜 스칼라예요? 결과가 행당 하나잖아요.