SQL 구문정리

3. 집합 연산자와 서브쿼리

1. 집합 연산자

union

## 기본 union -- 단순한 데이터 결합 + 같은 값(중복)제거

select * -- name or amount
from
        sales2007_1
union
select *
from
        sales2007_2 ;

##  order by -- 기본적으로 구문 맨 마지막 문장에 넣어준다

select *
from 
        sales2007_1
union
select *
from
        sales2007_2
order by amount DESC ;

union all -- 결합 시 중복된 데이터도 모두 출력

" union all >> union, 추후 개발자의 사후 관리 용이 "

## 기본 union all -- 중복값 포함 모두 출력

select *
from 
        sales2007_1
union all
select *
from
        sales2007_2
order by amount DESC ;

intersect -- 두 개 이상의 select문들의 결과 집합을 하나의 결과 집합으로 결합

" inner 조인 연산과 결과는 동일, but 추후 유지보수가 용이하므로 더 선호된다 "

select
        employee_id
from 
        keys
intersect
select
        employee_id
from
        hipos 
order by employee_id DESC ;

or

select
        k. employee_id
from
        keys k, hipos h
where k. employee_id = h. employee_id
order by k. employee_id ;

or

select 
        k. employee_id
from keys k
inner join hipos h 
on (k. employee_id = h. employee_id)
order by k. employee_id DESC ;

except -- 상위 select문의 결과 집합에서 하위 select문의 결과집합을 제외한 결과를 리턴

"차집합"

## 재고가 존재하는 영화 추출 -- 1:M 관계 '영화 하나 당 여러개의 재고id 존재'

select
        distinct inventory. film_id, film. title
from
        inventory
inner join
        film
on inventory. film_id = film. film_id -- 가용재고
order by film. title ;

## 재고 없는 영화 추출 -- (전체 영화 - 재고가 있는 영화)

select 
        film_id,
        title
from film
except
select
        distinct i.film_id, f.title
from inventory i
inner join
        film f
on i. film_id = f. film_id
order by f.title ;

2. 서브쿼리

중첩 서브쿼리, 인라인 뷰, 스칼라 서브쿼리

## 기본 쿼리로 평균보다 큰 rental_rate 집합 구하기

select
        avg(rental_Rate) -- 2.98
from film ;

+

select
        film_id,
        title,
        rental_rate
from film
where rental_rate > 2.98 ;

## 중첩 서브쿼리 -- 서브쿼리 -> 메인쿼리

select
        film_id,
        title,
        rental_rate
from film
where rental_rate >
(
select
        avg(rental_rate)
from film
) ;

## 인라인 뷰 -- from절을 오픈

select 
        a. film_id,
        a. title,
        a. rental_rate
from film a, 
(
select
        avg(f. rental_rate) as avg_rental_rate
from film f
) b
where a. rental_rate > b. avg_rental_rate
;

## 스칼라 서브쿼리 -- 인라인뷰의 심화

select -- 3. 기본 쿼리처럼 조건부로 결과값 추출
        a. film_id,
        a. title,
        a. rental_rate
from
(
select  -- 2.  avg_rental_rate가 추가된 가상의 테이블 생성
       f2. film_id,
        f2. title,
        f2. rental_rate,
        (
           select 
                    avg(f. rental_rate) -- 1. 인라인뷰를 통해 컬럼으로서 추가
           from film f
         ) as avg_rental_rate
from film f2
) a
where a. rental_rate > a. avg_rental_rate

any -- 서브 쿼리에 의해 반환된 값과 비교하여 결과값을 추출한다.

      -- 서브 쿼리의 값이 어떠한 값이라도 만족을 하면 조건이 성립된다.

select -- 다수의 category별 가장 긴 상영시간 값들 중 조건을 만족하기만 하면 성립
        title,
        length
from film
where length >= any
(
select max(length) -- category별 가장 긴 상영시간값 리턴
from
        film f,
        film_category fc
where f. film_id = fc. film_id
group by fc. category_id
);

** any가 없을 경우, 서브쿼리의 값이 다수이기 때문에 에러 발생.

## =ANY는 in과 동일

select
        title,
        length
from film
where legnth = any
(
select max (length)
from 
        film f,
        film_category fc
where f. film_id = fc. film_id
group by fc. categpry_id
);

or

select
        title,
        lengh
from film
where length in
(
select max(length)
from 
        film f,
        film_categoty fc
where f. film_id = fc. film_id
group by fc. category_id
);

all -- 서브쿼리의 모든 값을 만족해야 조건이 성립된다.

" 결국, 만족하기 제일 까다로운 조건이 기준이 된다 "

select 
        title,
        length
from film
where length >= all -- 이 경우 가장 긴 상영시간 값을 만족하는 상위 결과들만 추출
(
select max(length)
from 
        film f,
        film_categoty fc
where f. film_id = fc. fim_id
group by fc. category_id
);

select
        film_id,
        title,
        length
from film
where length > all -- 평가기준 별 평균 상영시간 값을 다 만족하는 상위 결과들만 추출
(
select 
        rating,
        round(avg(length), 2)
from film
group by rating 
)
order by length ;

exists -- 서브 쿼리 내에 집합의 존재여부를 판단하면 연산을 멈춤. 

" 참이면 출력하고 거짓이면 리턴하지 않는다 " -> 존재하지 않는다면 연산 x

select
        first_name,
        last_name
from
        customer c
where
exists (
                select 1
                from payment p
                where p. customer_id = c. customer_id
                    and p. amount > 11
                  )
order by first_name, last_name ;

Quiz 1

아래 SQL문은 film 테이블을 2번이나 스캔한다.

film 테이블을 한번만 scan하여 동일한 결과 집합을 구하는 sql을 작성하라.

select
        film_id,
        title,
        rental_Rate
from film
where rental_rate >
(
    select
            avg(rental_rate)
      from film
);

## 중첩 서브쿼리 -> 인라인 뷰

select
        film_id,
        title,
        rental_rate
from
(
select 
        f. film_id,
        f. title,
        f. rental_rate,
        avg(f. rental_rate) over () as avg_rental_rate -- 전체 평균인 분석함수 사용
from
        film f
) a
where rental_rate > avg_rental_rate ;

Quiz 2

아래 SQL문은 execpt 연산을 사용하여 재고가 없는 영화를 구하고 있다.

해당 sql문을 except 연산을 사용하지 말고 같은 결과를 도출하라.

select
        film_id,
        title
from film
except
select distinct
        i. film_id,
        f. title
from inventory i
inner join film f
on i. film_id = f. film_id
order by title ;

## 재고 없는 영화

select
        f2. film_id,
        f2. title
from film f2
where not exists (
                                    select 1
                                    from inventory i,
                                              film f
                                     where i. film_id = f. film_id
                                         and i. film_id = f. film_id
);

or

select
            f. film_id,
            f. title
from film f
where not exists (
                                select 1
                                from inventory i
                                where 1=1
                                    and f. film_id = i. film_id
)  ;