DB 서브쿼리 서브쿼리 서브쿼리 중요 (여러번날릴 쿼리를 한방에 할수있단다) 한방쿼리
서브쿼리를 안쓰면 쿼리를 나누어날려야한다
USE NorthWind
SELECT categoryid, categoryname -- 1
FROM categories
WHERE categoryname = 'Dairy products'
SELECT * --2
FROM products
WHERE categoryid =
SELECT * --3 (서브쿼리단점 속도가 느리다 SELCET 가 2번나와서)
FROM products
WHERE categoryid =(
SELECT categoryid
FROM categories
WHERE categoryname = 'Dairy products' -- products의 내용만 나온다
)
-- 조인의 단점 (조인속도가 훨신빠르다)
SELECT p.productid , p.productname, p.supplierid, p.categoryid, p.quantityperunit -- 기티생략
FROM categories AS c, products AS p
WHERE c.categoryid = p.categoryid
AND c.categoryname = 'Dairy products'
결과는 같다
/////////////////////////////// 서브 쿼리 //////////////////////////////
SELECT categoryid , SUM(unitprice)
FROM products
WHERE categoryid = 4
GROUP BY categoryid
--->
SELECT categoryid, (SELECT categoryname FROM categories WHERE categoryid =4) AS 'categoryname' , SUM(unitprice)
FROM products
WHERE categoryid = 4
GROUP BY categoryid
--------------------
SELECT productid , productname, unitprice
FROM products
WHERE categoryid =5
-------------------- 서브쿼리 예 서브쿼리는 DELETE INERT INTO 다된다
SELECT SUM(a.unitprice)
FROM
(
SELECT productid , productname, unitprice
FROM products
WHERE categoryid =5
)AS a -- 가상 테이블
SELECT SUM(a.unitprice)
FROM
(
SELECT productid , productname, categoryid, unitprice
FROM products
WHERE categoryid =5
)AS a , categories AS c
WHERE a.categoryid = c. categoryid