Derived table instead of a subquery

from the Artful MySQL Tips List


From Øystein, who works on the MySQL optimiser team: http://oysteing.blogspot.in/2016/12/improved-query-performance-by-using.html

If you don't have his tables, but do have a version of the Microsoft NorthWind example database, here is a NorthWind version of that query:

select c.companyname, c.customerid, o.orderid, o.orderdate, sum(d.quantity)
from customers c, orders o , orderdetails d  
where o.orderid in (
    select orderid
    from orderdetails
    group by orderid
    having sum(quantity) > 10
  )
  and c.customerid = o.customerid
  and o.orderid = d.orderid
group by c.companyname, c.customerid, o.orderid, o.orderdate
order by o.orderdate
LIMIT 100     ;




Return to the Artful MySQL Tips page