이중 쿼리, subQuery

출처 :  http://snowple.tistory.com/360

내가 써먹었던 쿼리. 내가 DBA는 아니고 단순 조회용으로 짠거라 그리 깔끔하지는 못하다 ㅠㅠ

ㅇ특정 테이블내에서 이름 검색

select p.name, p.phone from (select * from testTable where name like '%테%') p 

where name not in ('테스트1');

select p.name, p.phone, p.phone from (select * from testTable where name like '%테%') p 

where phone not in (select phone from testTable where phone like '0102%' );

ㅇ서로 다른 2개의 테이블에서 하나는 카운트와 이름을 가져오고 다른 곳에서는 이름과 그에 대한 num을 가져옴.

select id, carNum, cnt

from

(select id, carNum from table1 where carNum > 0 and carNum < 10)bb,

(select p.name, p.cnt from (select name, count(*) as cnt from table2 group by name) as p where cnt < 10)kk

where id = name order by carNum desc, cnt desc;

서브쿼리(Subquery)

서브쿼리(Subquery)란 하나의 SQL 문 안에 포함되어 있는 또 다른 SQL문을 말한다. 서브쿼리는 메인쿼리가 서브쿼리를 포함하는 종속적인 관계이다.

#메인쿼리
SELECT * 
FROM db_table
WHERE table_fk IN (
	#서브쿼리
	SELECT table_fk FROM db_table_other WHERE ..
	)

조인(Join)은 조인에 참여하는 모든 테이블이 대등한 관계에 있기 때문에 조인에 참여하는 모든 테이블의 칼럼을 어느 위치에서라도 자유롭게 사용할 수 있다. 그러나 서브쿼리는 메인쿼리의 컬럼을 모두 사용할 수 있지만, 메인쿼리는 서브쿼리의 칼럼을 사용할 수 없다.

조인은 집한간의 곱의 관계이다. 따라서, 1:1 관계 테이블이 조인하면 1(= 1*1) 레벨의 집합이 생성되고, 1:M 관계가 조인하면 M(=1*M) 레벨의 집합이 된다. 그리고 M:N 관계의 테이블이 조인하면 MN 레벨의 집합이 된다.
그러나, 서브쿼리는 서브쿼리 레벨과는 상관없이 항상 메인쿼리 레벨로 결과 집합이 생성된다. 

조인쿼리와 서브쿼리 방식을 상황에 맞게 사용하는 것이 중요하다.


서브쿼리를 사용할 때 주의할 점 

① 서브쿼리를 괄호로 감싸서 사용한다.
② 서브쿼리는 단일 행 또는 복수 행 비교 연산자와 함께 사용 가능
③ 서브쿼리에서는 ORDER BY를 사용하지 못한다.


서브쿼리가 사용이 가능한 곳 

- SELECT
- FROM
- WHERE
- HAVING
- ORDER BY
- INSERT문의 VALUES
- UPDATE문의 SET


서브쿼리의 종류

① 단일행 서브쿼리(Single Row Subquery)

1

SELECT *

2	FROM Player

3	WHERE Team_ID = (

4	SELECT Team_ID

5	FROM Player

6	WHERE Player_name = "yonglae"

7

)

8	ORDER BY Team_name;

② 다중행 서브쿼리(Multiple Row Subquery)

1

SELECT *

2

FROM Team

3	WHERE Team_ID IN (

4	SELECT Team_ID

5	FROM Player

6	WHERE Player_name = "yonglae"

7

)

8	ORDER BY Team_name;

③ 다중컬럼 서브쿼리(Multi Column Subquery)

1

SELECT *

2	FROM Player

3	WHERE (Team_ID, Height) IN (

4	SELECT Team_ID, MIN(Height)

5	FROM Player

6	GROUP BY Team_ID

7

)

8	ORDER BY Team_ID, Player_name;

위치에 따라 사용되는 서브쿼리

① SELECT 절에서 사용되는 서브쿼리

1	SELECT Player_name, Height, (

2	SELECT AVG(Height)

3	FROM Player p

4	WHERE p.Team_ID = x.Team_ID) as AVG_Height

5	FROM Player x

② FROM 절에서 사용되는 서브쿼리
FROM 절에서 사용되는 서브쿼리를 인라인 뷰(Inline View)라고 한다. FROM 절에는 테이블 명이 오도록 되어 있다. 그런데 서브쿼리가 FROM절에 사용되면 뷰(View)처럼 결과가 동적으로 생성된 테이블로 사용할 수 있다. 임시적인 뷰이기 때문에 데이터베이스에 저장되지는 않는다.
또한, 인라인 뷰로 동적으로 생성된 테이블이여서 인라인 뷰의 칼럼은 자유롭게 참조가 가능하다. 

1	SELECT t.Team_name, p.Player_name, p.Height

2	FROM Player t, (

3	SELECT Team_ID, Player_name, Back_no

4	FROM Player

5	WHERE Position = 'MF') p

6	WHERE p.Team_ID = t.Team_ID;

③ WHERE 절에서 사용되는 서브쿼리

1	SELECT Player_name

2	FROM Player

3	WHERE Team_ID IN (

4	SELECT Team_ID

5

FROM Team

6	WHERE Team_country = 'KOR'

7

)