서브 쿼리

게시일: Oct 25, 2011 4:37:30 AM

- SQL 문장 안에 사용되는 별도의 SQL 문장.

- 서브 쿼리, 인라인 뷰 등 기타 세부내용은 책 참조.

- ANY : 서브쿼리의 앞에 위치해서 서브쿼리의 반환값들 중 어느 하나만 비교조건을 만족하면 됨.

- ALL : 서브쿼리에서 반환되는 로우들 전체에 대해 조건을 만족해야함 .

select to_char(sales_dt, 'yyyy-mm') sales_month

, product_id product_id

, sales_volume sales_volume

from (

select to_date('2011-10-01', 'yyyy-mm-dd') sales_dt, '1' product_id, 200 sales_volume from dual union all

select to_date('2011-10-01', 'yyyy-mm-dd') sales_dt, '2' product_id, 300 sales_volume from dual union all

select to_date('2011-10-01', 'yyyy-mm-dd') sales_dt, '3' product_id, 400 sales_volume from dual union all

select to_date('2011-10-01', 'yyyy-mm-dd') sales_dt, '4' product_id, 800 sales_volume from dual union all

select to_date('2011-10-01', 'yyyy-mm-dd') sales_dt, '5' product_id, 500 sales_volume from dual union all

select to_date('2011-10-01', 'yyyy-mm-dd') sales_dt, '6' product_id, 900 sales_volume from dual union all

select to_date('2011-10-01', 'yyyy-mm-dd') sales_dt, '7' product_id, 100 sales_volume from dual union all

select to_date('2011-10-01', 'yyyy-mm-dd') sales_dt, '8' product_id, 300 sales_volume from dual union all

select to_date('2011-10-01', 'yyyy-mm-dd') sales_dt, '9' product_id, 700 sales_volume from dual

)

where sales_volume > any (select sales_volume

from (select 200 sales_volume from dual union all

select 400 sales_volume from dual union all

select 700 sales_volume from dual )

)

--동일한 결과

--where sales_volume > (select min(sales_volume)

-- from (select 200 sales_volume from dual union all

-- select 400 sales_volume from dual union all

-- select 700 sales_volume from dual )

-- )

;

------------------------------------

SALES_MONTH PRODUCT_ID SALES_VOLUME

------------------------------------

2011-10 2 300

2011-10 3 400

2011-10 4 800

2011-10 5 500

2011-10 6 900

2011-10 8 300

2011-10 9 700

------------------------------------

select to_char(sales_dt, 'yyyy-mm') sales_month

, product_id product_id

, sales_volume sales_volume

from (

select to_date('2011-10-01', 'yyyy-mm-dd') sales_dt, '1' product_id, 200 sales_volume from dual union all

select to_date('2011-10-01', 'yyyy-mm-dd') sales_dt, '2' product_id, 300 sales_volume from dual union all

select to_date('2011-10-01', 'yyyy-mm-dd') sales_dt, '3' product_id, 400 sales_volume from dual union all

select to_date('2011-10-01', 'yyyy-mm-dd') sales_dt, '4' product_id, 800 sales_volume from dual union all

select to_date('2011-10-01', 'yyyy-mm-dd') sales_dt, '5' product_id, 500 sales_volume from dual union all

select to_date('2011-10-01', 'yyyy-mm-dd') sales_dt, '6' product_id, 900 sales_volume from dual union all

select to_date('2011-10-01', 'yyyy-mm-dd') sales_dt, '7' product_id, 100 sales_volume from dual union all

select to_date('2011-10-01', 'yyyy-mm-dd') sales_dt, '8' product_id, 300 sales_volume from dual union all

select to_date('2011-10-01', 'yyyy-mm-dd') sales_dt, '9' product_id, 700 sales_volume from dual

)

where sales_volume > all (select sales_volume

from (select 200 sales_volume from dual union all

select 400 sales_volume from dual union all

select 700 sales_volume from dual )

)

--동일한 결과

--where sales_volume > (select max(sales_volume)

-- from (select 200 sales_volume from dual union all

-- select 400 sales_volume from dual union all

-- select 700 sales_volume from dual )

-- )

;

------------------------------------

SALES_MONTH PRODUCT_ID SALES_VOLUME

------------------------------------

2011-10 4 800

2011-10 6 900

------------------------------------

select to_char(sales_dt, 'yyyy-mm') sales_month

, product_id product_id

, sales_volume sales_volume

from (

select to_date('2011-10-01', 'yyyy-mm-dd') sales_dt, '1' product_id, 200 sales_volume from dual union all

select to_date('2011-10-01', 'yyyy-mm-dd') sales_dt, '2' product_id, 300 sales_volume from dual union all

select to_date('2011-10-01', 'yyyy-mm-dd') sales_dt, '3' product_id, 400 sales_volume from dual union all

select to_date('2011-10-01', 'yyyy-mm-dd') sales_dt, '4' product_id, 800 sales_volume from dual union all

select to_date('2011-10-01', 'yyyy-mm-dd') sales_dt, '5' product_id, 500 sales_volume from dual union all

select to_date('2011-10-01', 'yyyy-mm-dd') sales_dt, '6' product_id, 900 sales_volume from dual union all

select to_date('2011-10-01', 'yyyy-mm-dd') sales_dt, '7' product_id, 100 sales_volume from dual union all

select to_date('2011-10-01', 'yyyy-mm-dd') sales_dt, '8' product_id, 300 sales_volume from dual union all

select to_date('2011-10-01', 'yyyy-mm-dd') sales_dt, '9' product_id, 700 sales_volume from dual

)

where sales_volume = any (select sales_volume

from (select 200 sales_volume from dual union all

select 400 sales_volume from dual union all

select 700 sales_volume from dual )

)

--동일한 결과

--where sales_volume in (select sales_volume

-- from (select 200 sales_volume from dual union all

-- select 400 sales_volume from dual union all

-- select 700 sales_volume from dual )

-- )

;

------------------------------------

SALES_MONTH PRODUCT_ID SALES_VOLUME

------------------------------------

2011-10 1 200

2011-10 3 400

2011-10 9 700

------------------------------------

☞ '= any' 가 in 과 같다고 해서 그 반대의 경우도 같은 것은 아니다. 즉, '<> any' 와 'not in'은 같지 않다.

select to_char(sales_dt, 'yyyy-mm') sales_month

, product_id product_id

, sales_volume sales_volume

from (

select to_date('2011-10-01', 'yyyy-mm-dd') sales_dt, '1' product_id, 200 sales_volume from dual union all

select to_date('2011-10-01', 'yyyy-mm-dd') sales_dt, '2' product_id, 300 sales_volume from dual union all

select to_date('2011-10-01', 'yyyy-mm-dd') sales_dt, '3' product_id, 400 sales_volume from dual union all

select to_date('2011-10-01', 'yyyy-mm-dd') sales_dt, '4' product_id, 800 sales_volume from dual union all

select to_date('2011-10-01', 'yyyy-mm-dd') sales_dt, '5' product_id, 500 sales_volume from dual union all

select to_date('2011-10-01', 'yyyy-mm-dd') sales_dt, '6' product_id, 900 sales_volume from dual union all

select to_date('2011-10-01', 'yyyy-mm-dd') sales_dt, '7' product_id, 100 sales_volume from dual union all

select to_date('2011-10-01', 'yyyy-mm-dd') sales_dt, '8' product_id, 300 sales_volume from dual union all

select to_date('2011-10-01', 'yyyy-mm-dd') sales_dt, '9' product_id, 700 sales_volume from dual

)

where sales_volume <> any (select sales_volume

from (select 200 sales_volume from dual union all

select 400 sales_volume from dual union all

select 700 sales_volume from dual )

)

;

------------------------------------

SALES_MONTH PRODUCT_ID SALES_VOLUME

------------------------------------

2011-10 1 200

2011-10 2 300

2011-10 3 400

2011-10 4 800

2011-10 5 500

2011-10 6 900

2011-10 7 100

2011-10 8 300

2011-10 9 700

------------------------------------

select to_char(sales_dt, 'yyyy-mm') sales_month

, product_id product_id

, sales_volume sales_volume

from (

select to_date('2011-10-01', 'yyyy-mm-dd') sales_dt, '1' product_id, 200 sales_volume from dual union all

select to_date('2011-10-01', 'yyyy-mm-dd') sales_dt, '2' product_id, 300 sales_volume from dual union all

select to_date('2011-10-01', 'yyyy-mm-dd') sales_dt, '3' product_id, 400 sales_volume from dual union all

select to_date('2011-10-01', 'yyyy-mm-dd') sales_dt, '4' product_id, 800 sales_volume from dual union all

select to_date('2011-10-01', 'yyyy-mm-dd') sales_dt, '5' product_id, 500 sales_volume from dual union all

select to_date('2011-10-01', 'yyyy-mm-dd') sales_dt, '6' product_id, 900 sales_volume from dual union all

select to_date('2011-10-01', 'yyyy-mm-dd') sales_dt, '7' product_id, 100 sales_volume from dual union all

select to_date('2011-10-01', 'yyyy-mm-dd') sales_dt, '8' product_id, 300 sales_volume from dual union all

select to_date('2011-10-01', 'yyyy-mm-dd') sales_dt, '9' product_id, 700 sales_volume from dual

)

where sales_volume not in (select sales_volume

from (select 200 sales_volume from dual union all

select 400 sales_volume from dual union all

select 700 sales_volume from dual )

)

;

------------------------------------

SALES_MONTH PRODUCT_ID SALES_VOLUME

------------------------------------

2011-10 5 500

2011-10 4 800

2011-10 8 300

2011-10 2 300

2011-10 6 900

2011-10 7 100

------------------------------------

☞ '<> all' 은 'not in'과 동일하다.