SQL 정리 - (2)

집합 연산자와 서브쿼리

• 집합 연산자
  • union all 중복되는 값 같이 보여줌
  • intersect는 inner join과 효과가 같음
  • except a집합 - b집합
• 서브 쿼리
  • 서브쿼리
  • 인라인뷰(from절에 들어간거)
  • any
  • all
  • exists

---

• union all

SELECT *

FROM SALES2007_1

UNION ALL

SELECT *

FROM SALES2007_2

ORDER BY AMOUNT DESC;

---

• except

–전체영화에서 SELECT FILM_ID , TITLE

FROM FILM

EXCEPT –재고가 존재하는 영화를 뺀다

SELECT DISTINCT INVENTORY.FILM_ID , TITLE

FROM INVENTORY

INNER JOIN FILM ON FILM.FILM_ID = INVENTORY.FILM_ID

ORDER BY TITLE; –결과집합 은 재고가 존재하지 않는 영화다.

---

• 서브쿼리

SELECT FILM_ID , TITLE , RENTAL_RATE

FROM FILM

WHERE RENTAL_RATE > (

SELECT

AVG (RENTAL_RATE)

FROM FILM );

• 인라인뷰

SELECT A.FILM_ID , A.TITLE , A.RENTAL_RATE

FROM FILM A , (

SELECT

AVG(RENTAL_RATE) AS AVG_RENTAL_RATE

FROM FILM ) B

WHERE A.RENTAL_RATE > B.AVG_RENTAL_RATE ;

---

• any

SELECT TITLE, LENGTH

FROM FILM

WHERE LENGTH = ANY (

SELECT MAX(LENGTH)

FROM FILM A

, FILM_CATEGORY B

WHERE A.FILM_ID = B.FILM_ID

GROUP BY B.CATEGORY_ID );

---

• exists 해당 집합이 존재하기만 하면 더이상 연산을 멈추므로 성능상 유리함.

SELECT FIRST_NAME , LAST_NAME

FROM CUSTOMER C

WHERE

EXISTS ( SELECT *

FROM PAYMENT P

WHERE P.CUSTOMER_ID = C.CUSTOMER_ID

AND P.AMOUNT > 11 )

ORDER BY FIRST_NAME, LAST_NAME;

---

• group by 안하고 사용할려면 분석함수 사용

SELECT A.FILM_ID, A.TITLE, A.RENTAL_RATE

FROM (

SELECT A.FILM_ID, A.TITLE, A.RENTAL_RATE, AVG(A.RENTAL_RATE) OVER() AS AVG_RENTAL_RATE

FROM FILM A ) A

WHERE A.RENTAL_RATE > A.AVG_RENTAL_RATE;

• Referneces

  • 패스트캠퍼스 sql강의