서브 쿼리

무엇을 SQL 쿼리라고 하는가?
사전전 의미로 쿼리는 질의인데, DB에 "어떤 어떤 것 주세요~" 라고 요청하는 SQL 절을 쿼리라고 한다.

무엇을 서브 쿼리 라고 하는가?
쿼리 안에 있는 쿼리를 서브 쿼리 라고 한다. 내가 본 시야가 아닌 다른 사람이 본 시야도 같이 참조 하면 더욱 확실해 질 것이다.(이런 여러 시야로 대상을 바라보면, 3D 처럼 구조화 된다.

관련 링크

1. http://kukuta.tistory.com/128
   
2. http://sql.1keydata.com/kr/sql-subquery.php
   

언제 서브 쿼리가 필요한가?
DB에 접근하는 속도를 높이기 위해서, 테이블을 다른 테이블과 관계를 맺어 만든다. 왜냐하면 한 테이블에 많은 정보가 들어 있으면, 속도가 느려지기 때문이다. 이 말은 중복 값을 다른 테이블에 빼고 테이블을 참조 한다는 뜻이다. 이렇게 만들어진 테이블들은 원하는 정보를 가져올 때, 원하는 정보를 찾을 수 있도록 해 주는 키워드가 다른 테이블에 저장되어 있다. 그러므로 다른 테이블에 있는 정보를 가져와 현재 테이블에서 찾고자 할 때, 서브 쿼리가 필요하다.

어떻게 서브 쿼리를 사용 하는가?
서브쿼리는 SQL 절에서 계산필드, FROM, WHERE, HAVING에 들어갈 수 있으며, 대개 WHERE 절에서 많이 쓰인다. 사용 방법은 다음과 같다.

- WHERE 절에서 사용 하기

여기서 보면, IN ( 이곳 ) 에 들어 간 것이 서브 쿼리이다. OrderItems 테이블 에서 prod_id = 'RGAN01' 인 것 중 order_num을 가져와 Orders 테이블에서 order_num 에 비교하여 참인것들만 보여라. 란 뜻이다.

- 계산 필드에서 사용 하기
보여줄 열이 SELECT의 결과로 쓰이는 형태로, 다음과 같이 사용 한다.

결과는 다음과 같다.

- FROM 에서 사용 하기
FROM 에서 사용 할 경우(써본적은 없다.)는 대부분 VIEW 테이블이지 않을까 한다. FROM 절에 있는 서브 쿼리로 결과를 얻은 것이 "테이블" 이라고 생각하고 사용 하면 된다.

마땅히 예제가 떠오르지 않아, 사용하기 위한 코드로써 남겨 둔다.

1 2 3 4 5

SELECT cust_id FROM ( SELECT cust_id FROM orders ) AS foo ORDER BY cust_id;

서브 쿼리 사용 규칙
서브쿼리를 WHERE 절에 사용 할 땐, 출력 결과가 한개 열만 된다. (SELECT * FROM .... 이 안된다는 뜻)
서브쿼리를 계산 필드에 사용 할 땐, (출력 레코드 갯수 * 서브쿼리) 만큼 쿼리 량이 증가 된다. 이는 성능 정하를 나타낸다.
서브쿼리 말고 JOIN 으로도 결과를 나타낼 수 있을 땐, 두개의 성능을 비교 해 보는 것이 좋다.

출처 http://ikpil.com/1101

서브 쿼리 열의 범위

테이블 A의 열은   Q1,Q3,Q4와 Q5에서 사용할 수 있지만 Q2에서는 사용할 수 없다.
이 때 Q1,Q3,Q4와 Q5는 테이블 A의 열의 범위에 있다고 할 수 있다.
테이블 B의 열은 Q3과 Q5에서만 사용할 수 있으므로 Q3과 Q5는 테이블 B의 열의 범위이다.

서브쿼리 알아둘 점!

DB의 중요한 점은 계속 늘어난다는 점.
크로스 조인은 오래걸린다.
상관 서브 쿼리도 느린편.
조인이 서브쿼리 보다는 빠르다!

상관 서브 쿼리는 밑에서 알아 보기로 하고....
엄청 큰 DB 쿼리를 날리거나 많은 값을 읽어오지 않는 이상
큰 차이는 느끼기 어려울 것 같긴 하다...

어쨋든 서브쿼리, 비상관 서브쿼리, 상관 서브쿼리를 포스팅 해보겠다.

우선 서브 쿼리는 내부쿼리 라고도 불리는데,
서브쿼리 만의 규칙이 있다.

서브 쿼리는 언제나 SELECT 문 하나이다.
서브 쿼리는 항상 괄호 안에 있다.
서브쿼리는 세미콜론이 없다. 세미콜론은 항상 전체 쿼리의 마지막에만 위치한다
서브쿼리는 SELECT 절, 열 리스트를 선택하는 경우 FROM 절, HAVING 절
서브쿼리는 SELECT, UPDATE, INSERT, DELETE 와 함께 사용할 수 있다

그다음으로 서브쿼리는 하나의 값 만을 반환한다
( 즉, 한 행의 한 열 )
그 다음, 외부 쿼리는 그 값을 열의 모든 값과 비교한다.

---

다음으로 서브쿼리의 IN 연산자...

IN 연산자는 여러 값을 포함하는데 ' = ' 같은 연산자는 한 값만 사용한다.
--> 대부분 하나의 값을 반환하고 , 'IN' 의 경우는 여러값을 반환하므로 예외적이다.

그리고 NOT IN 도 있는데
NOT IN 은 IN 과는 반대로 해당 테이블의 없는 현재 값을 반환한다.

---

비 상관 서브 쿼리는

서브 쿼리가 우선 실행되고, 그리고 나서 결과가 외부 쿼리의 where 조건으로 이용된다
하지만 서브쿼리는 외부쿼리의 결과와는 상관없이 단독으로 실행될 수 있는 쿼리
외부 쿼리의 어떤 것도 참조하지 않고 단독으로 사용됨

간단하게 쓰는 서브 쿼리는 모두 여기에 해당할 것이다.

---

상관 서브 쿼리는

내부쿼리의 값이 결정되는데 외부쿼리에 의존한다

주관적인 생각에는 조금 고급 쿼리인듯???ㅋㅋ
상관 서브쿼리를 쓸려면 NOT EXISTS 예약어를 이용한다.

SELECT t.name, t.email
FROM test_table AS t
WHERE NOT EXISTS
( SELECT * FROM best_table as b
WHERE t.id = b.id );

예제에서 보면 WHERE 절 밑에 나오는 또 다른 괄호 안에 있는 SELECT 문이
서브 쿼리이고
이 서브쿼리의 WHERE 절을 보면 괄호 밖에서 이용된 test_table 의 id 칼럼을 이용하도록 되어있다
따라서 상관 서브 쿼리는 서브쿼리의 값이 결정되려면 외부쿼리에 의존하게 된다.

=> 쿼리를 좀 해석해 보자면...
test_table 에 있는 사람들 중 현재 best_table 에 없는 사람들의 이름과 이메일을 찾는다.
여기에서 '없는' 에 주의...
이 '없는'이 어디서 왔냐...........

바로 NOT EXISTS 에서 왔다.

그러면 ' 있는 ' 으로 하려면??
바로 NOT 을 없앤 EXISTS 를 쓰면 된다.

EXISTS 도 좀 보면,

NOT EXISTS 와는 반대로 해당 테이블에서
한번이라도 나오게 되면 값을 반환한다.

정도 되겠다.

서브쿼리 사용시 오류사항
* 단일행 서브쿼리오류 – 서브쿼리 리턴값이 1개가 아닌경우
* 메인쿼리와 서브쿼리 칼럼의 수가 일치하지 않을 경우
* 서브쿼리내에 ORDER BY 절을 사용할 경우
* 서브쿼리 검색 결과가 NULL일 경우- NULL과 연산결과는 모두 NULL 됨

서브 쿼리 종류
단일행 서브쿼리, 다중 행 서브쿼리, 다중 칼럼 서브쿼리, 상호관련 서브쿼리

- 단일 행 서브쿼리: 서브쿼리 검색결과로 하나의 행만 메인쿼리로 반환함.
- 다중 행 서브쿼리: 하나 이상의 행을 메인 쿼리로 반환함.
- 다중 칼럼 서브쿼리: 하나 이상의 칼럼을 메인 쿼리로 반환함.
- 상호 관련 서브쿼리: 서브쿼리와 메인 쿼리간에 결과값을 서로 주고 받는 식으로 처리되는 서브쿼리

- 단일행 서브쿼리
‘=‘ 연산자를 사용한 단일행 서브쿼리

예제)
사용자 아이디가 ‘jun123’인 학생과 같은 학년인 학생의 학번, 이름, 학년을 출력하세요.
SQL> SELECT studno, name, grade
FROM student
WHERE grade = (SELECT grade
FROM student
WHERE userid = ‘jun123’);
출력결과)
STUDNO NAME GRADE
------- ------ ------
10101 전인하 4
10202 오유석 4
10107 이광훈 4

학생테이블에서 이광훈 학생과 같은 학과의 학생들의 이름과 학과이름을 출력하세요.
select s.name "이름", d.dname "학과이름"
from student s, department d
where s.deptno=d.deptno
and s.deptno = (select deptno
from student
where name='이광훈')
SQL> /

이름 학과이름
---------- ----------------
류민정 컴퓨터공학과
이광훈 컴퓨터공학과
서재진 컴퓨터공학과
임유진 컴퓨터공학과
지은경 컴퓨터공학과
김영균 컴퓨터공학과
박미경 컴퓨터공학과
전인하 컴퓨터공학과

- ‘<‘ 연산자를 사용한 단일행 서브 쿼리
사용예) 101번 학과 학생들의 평균 몸무게보다 몸무게가 적은 학생의 이름, 학과 번호, 몸무게를 출력하여라.

SQL> SELECT name, deptno, weight
FROM student
WHERE weight < ( SELECT AVG(weight)
FROM student
WHERE deptno = 101)
ORDER BY deptno;
출력결과)
NAME DEPTNO WEIGHT
------ ------- -------
박미경 101 52
지은경 101 42
………..
7개의 행이 선택되었습니다.

- 다중행 서브 쿼리
서브쿼리에서 반환되는 결과 행이 하나 이상일 때 사용하는 서브쿼리.
다중 행 비교 연산자를 사용 가능.

① IN 연산자
메인 쿼리의 비교 조건에서 서브쿼리의 출력 결과와 하나라도 일치하면 메인 쿼리 조건 절이 참이 되는 연산자임. ‘=‘연산자를 OR로 연결한 것과 동일

예제)
정보 미디어학부(부서번호:100)에 소속된 모든 학생의 학번, 이름, 학과번호를 출력하여라.

SQL> SELECT name, grade, deptno
FROM student
WHERE deptno IN ( SELECT deptno
FROM department
WHERE college = 100);

위 서브쿼리를 실행하면 2개의 결과가 나오며 이 두 개의 결과값이 IN 연산자를 통해 메인 쿼리에게 전달된다.

Temp , tdept 테이블을 사용하여 인천에 근무하는 직원의 사번과 성명을 구하세요
select emp_id "사번", emp_name "성명"
from temp
where dept_code in(select dept_code
from tdept
where area='인천')
SQL> /

사번 성명
---------- ----------
19930331 정도령
19950303 이순신
19966102 지문덕
20000334 박지리
20000305 정북악
20006106 유도봉

6 rows selected.

② ANY/ALL
ANY 연산자
메인 쿼리의 비교 조건에서 서브쿼리의 출력 결과와 하나라도 일치하면 메인쿼리 조건 절이 참이 되는 연산자이다.

ALL 연산자 사용
ALL 연산자는 메인쿼리의 비교조건에서 서브쿼리의 검색 결과와 모두 일치하면 메인쿼리 조건절이 참이 되는 연산자임.

<any : 최대값
>any : 최소값
>all 최대값
<all 최소값
두 가지는 부등호가 다르기 때문에 조심

Temp 테이블에서 과장 중 가장 급여를 작게 받는 사람보다 많이 받는 사원들의 사번, 이름, 급여 를 출력하세요.
select emp_id "사번", emp_name "이름", salary "급여"
from temp
where salary >any (select salary
from temp
where lev='과장')
SQL> /

사번 이름 급여
---------- ---------- ----------
19970101 김길동 100000000
19960101 홍길동 72000000
19930331 정도령 70000000
19930402 강감찬 64000000
19950303 이순신 56000000
19970201 박문수 50000000
19966102 지문덕 45000000
19970112 연흥부 45000000

③ EXISTS 연산자
서브쿼리에서 검색된 결과가 하나라도 존재하면 메인쿼리 조건절이 참이 되는 연산자임.

- 다중 칼럼 서브쿼리
서브쿼리에서 여러 개의 칼럼 값을 검색하여 메인쿼리의 조건절과 비교하는 서브 쿼리이다. PAIRWISE 방식과 UNPAIRWISE 방식이 있다.

- 상호 연관 서브쿼리
메인쿼리와 서브쿼리간에 검색 결과를 교환하는 서브쿼리이다

예제)
학생중에 동아리의 등급이 일반 회원인 학생의 학번과 이름, 주민등록번호를 출력하라.
mysql> select stu_no, stu_name, id_num
> from student
> where stu_no in
> (select stu_no
from circle
where president =2);

두번 이상 장학금을 지급 받은 학생중 장학 금액이 학기 별로 서로 다른 학생의 경우의 학번출력
mysql> select distinct stu_no
> from fee f
> where stu_no in
> (select stu_no
> from fee
> where jang_total <> f.jang_total );

학번이 가장 큰값을 찾는 방법
mysql> select max(stu_no)
> from student

학적 테이블에서 학번이 가장 큰 3명의 학번을 내림차순으로 출력하라.
mysql> select stu_no
> from student s1
> where 3 >
> (select count(*)
> from student s2
> where (s1.stu_no < s2.stu_no))
> order by stu_no desc;

→ 가장 큰 학번 보다 더 큰 학번은 없다. 두 번째 큰 학번 보다 더 큰 학번은 하나이며, 세번째 큰 학번보다 더큰 학번은 두개이다.
서브 쿼에서 각 학번에 대해 더 큰 학번의 수를 계산하는 데 이 3개의 학번 보다 더 적다면 최종결과로 나온다.

등록테이블에서 장학금을 지급 받은 학생 중 가장 작은 장학금액을 지급 받은 학생 8명의 학번, 장학금 총액을 출력하라.
mysql> select distinct stu_no, jang_total
> from fee f1
> where 8 >
> (select count(*)
> from fee f2
> where f1.jang_total > f2.jang_total)
> order by f1.jang_total desc;

"20061011"학생이 가입한 동아리에 소속된 모든 학생의 학번과 이름을 출력하라.
mysql> select stu_no, stu_name
> from student s
> where not exists
> (select * from circle c
> where c.stu_no = '20061011' 
> and not exists
> (select * from circle c2
> where c.cir_name = c2.cir_name  and s.stu_no =c2.stu_no));

→ 동아리에 가입하지 않은 학생들과 "20061011" 학생이 가입한 동아리를 2중 부정(부정에 대한 부정)으로 만들어 "20061011" 학생이 소속된 동아리("컴맹탈출")의 모든 회원의 학번과 이름을 출력하는 예제

적어도 한 번 장학금을 받은 학생 대하여 학번, 등록년도, 학기, 장학금액 중 가장 큰 장학금액, 등록 일자를 출력하라.
mysql> select stu_no, fee_year, fee_term, jang_total, fee_date
> from fee f1
> where jang_total =
> (select max(jang_total)
> from fee f2
> where f1.stu_no=f2.stu_no);

적어도 한 번 이상 수강신청을 하고 등록한 학생에 대하여 학번, 이름, 주민등록번호를 출력하라.
mysql> select stu_no, stu_name, id_num
> from student s
> where not exists
> (select stu_no
> from fee f
> where fee_div ='Y'
> and not exists
> (select * from
> attend a
> where s.stu_no = a.stu_no
> and a. att_div= 'Y'));

서브쿼리 사용시 오류사항
* 단일행 서브쿼리오류 – 서브쿼리 리턴값이 1개가 아닌경우
* 메인쿼리와 서브쿼리 칼럼의 수가 일치하지 않을 경우
* 서브쿼리내에 ORDER BY 절을 사용할 경우
* 서브쿼리 검색 결과가 NULL일 경우- NULL과 연산결과는 모두 NULL 됨