SQL既問題, 唔識既咪入黎copy and paste或者亂吹呀!!!

用grouping來做filter的例子

有三個tables, customer, po 定單, poline 定單項目

找出生意額大於1000的顧客

subquery 寫法
select * from customer where customerID in (
select P.customerID from po P, poline L
where p.poID = L.poID
having sum(L.invoice_amount) > 1000
group by P.customerID
)

用 table join 的寫法
select C.customerID,C.custName,C.custRegion,C.salespersonID,sum(L.invoice_amount) as amount
from customer C, po P, poline L
where C.customerID = P.customerID and P.poID = L.poID
having sum(L.invoice_amount) > 1000
group by C.customerID,C.custName,C.custRegion,C.salespersonID

上述的例子subquery明顯比 table join清楚,容易理解,server處理也比table join快,
因為table join有很多group by要處理,但subquery找不到每個顧客的生意額.

下面是最佳寫法
select C.*,X.amount from customer C,
(select P.customerID,sum(L.invoice_amount) as amount
from po P, poline L
where p.poID = L.poID
having sum(L.invoice_amount) > 1000
group by P.customerID
) X
where C.customerID = X.customerID

subquery的最差寫法
select C.*,X.amount from customer C,
(select P.customerID,sum(L.invoice_amount) as amount
from po P, poline L
where P.poID = L.poID and P.customerID = C.ciustomerID
having sum(L.invoice_amount) > 1000
group by P.customerID
) X
最差寫法因為用了implicit join,把和customer table的join clause放在subquery內,
上面兩個subquery的例子,subquery都可以策出來獨立運作,唯有上面方法不能.