mysql 부속질의 (select, from, where)

스칼라 부속질의 - select 부속질의

단일값을 반환

 

ex) 고객별 판매액을 보이시오. 단, 결과는 고객이름과 고객별 판매액을 출력한다.

select (select cname from cust where cust.cid = orders.cid) as cname,
	sum((select price from book where book.bid = orders.bid)) as tot_price
from orders
group by cid;

먼저 구매한 cust를 부속질의로 찾음, 이후 sum 안에서 부속질의로 책 가격을 찾고 sum으로 합산

sum에서 또 부속질의라 괄호 2번인 듯/??

 

사용하는 이유: cname을 출력하고 싶은데 orders에는 cname이 존재하지 않음, price의 합계를 출력하고 싶은데 orders에는 price가 존재하지 않아 부속질의로 가져와 합계 sum을 사용함.

 

ex) orders 테이블에 각 주문에 맞는 도서이름을 입력하시오.

update orders
set title = (select title
             from book
             where book.bid = orders.bid);

 

ex)안양 사는 고객이 주문한 주문정보를 보고 싶다(고객이름, 주문번호, 주문일)

select (select cname
        from customer
        where cust.cid = orders.cid and addr = 'anyang') as cname, oid, odate
from orders;	##틀린 답


select (select cname
        from cust
        where cust.cid = orders.cid) as cnmae, oid, odate
from orders
where cid in (select cid
              from cust
              where addr = 'anyang');	##맞는 답

 

위의 예시가 왜 틀렸냐면, 먼저 안양에 해당하는 cname만 찾고 oid, odate는 그대로 출력하기 때문에 모든 oid, odate가 출력되기 때문에 anyang에 해당하지 않는 cname만 null로 출력될 뿐 전부 출력된다. 따라서 cname을 가져온 후 anyang에 사는 사람을 뒤에 걸러야 하기 때문에 where로 빼서 후에 계산한다.

 

인라인 뷰 - from 부속질의

ex) 고객번호가 2 이하인 고객의 판매액을 보이시오. 단, 결과는 고객이름과 고객별 판매액을 출력한다.

select cname, sum(price)
from (select cid, cname
      from cust
      where cid <= 2) cs,
      orders od,
      (select bid, price
      from book) b
where cs.cid = od.cid and b.bid = od.bid
group by od.cid;

cs, od, b 처럼 이름을 지정해 조건에 해당하는 그 테이블만 사용하도록 가져와 where, group by로 사용

 

ex) 주문한 고객의 이름과 주문일, 책 이름을 보이되, 주문하지 않은 고객의 이름과 주문하지 않은 책 이름도 함께 보여라.

select cname, odate, title
from (select cname, odate, bid
      from cust left outer join orders on cust.cid = orders.cid) cs left outer join book on cs.bid = book.bid
union
select cname, odate, title
from (select cname, odate bid
      from cust left outer join orders on cust.cid = orders.cid) cs right outer join book on cs.bid = book.bid;

from 절 안에서 bid를 뽑는 이유는 이후에 아우터 조인에서 비교를 해야 하기 때문. cname, odate만 cust, orders 테이블에서 따로 빼 cs 가상 테이블로 만들고 비교.

 

중첩질의 - where 부속질의

https://till.tistory.com/entry/mysql-%EB%B6%80%EC%86%8D%EC%A7%88%EC%9D%98

 

ex) 평균 주문금액 이하의 주문에 대해서 주문번호와 금액을 보이시오.

select oid, price
from orders inner join book on orders.bid = book.bid
where price <= (select avg(price) from orders natural join book);

 

ex) 각 고객별 평균 주문금액보다 큰 금액의 주문 내역에 대해서 주문번호, 고객번호, 금액을 보이시오.(상관부속질의)

select oid, cid, price
from (select oid, cid, price
      from orders natural join book) t1
where t1.price > (select avg(price)
                  from (select oid, cid, price
                        from orders natural join book) t2 natural join book);

 

ex) 1번 고객이 주문한 도서의 최고 금액보다 더 비싼 도서를 구입한 주문의 주문번호와 금액을 보이시오.

select oid, price
from orders natural join book
where price > all(select price from orders natural join boo where cid = 1);

 

all: 전부 중에

some(any): 최소한 1개보다

 

정리

select 부속질의: 속성을 가져오기(sum과 같은 계산 속성 가져오기 용이)

from 부속질의: 속성을 가져오고 조건을 주기(조건을 준 가상의 테이블을 만들어 사용함)

where 부속질의: 조건을 추가하기