StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POWhat are the advantages of a query using a derived table(s) over a query not using them?
text
Body
copied!<p>I know how derived tables are used, but I still can’t really see any real advantages of using them. </p> <p>For example, in the following article <a href="http://techahead.wordpress.com/2007/10/01/sql-derived-tables/" rel="nofollow noreferrer">http://techahead.wordpress.com/2007/10/01/sql-derived-tables/</a> the author tried to show benefits of a query using derived table over a query without one with an example, where we want to generate a report that shows off the total number of orders each customer placed in 1996, and we want this result set to include all customers, including those that didn’t place any orders that year and those that have never placed any orders at all( he’s using Northwind database ).</p> <p>But when I compare the two queries, I fail to see any advantages of a query using a derived table ( if nothing else, use of a derived table doesn't appear to simplify our code, at least not in this example):</p> <p>Regular query: </p> <pre><code>SELECT C.CustomerID, C.CompanyName, COUNT(O.OrderID) AS TotalOrders FROM Customers C LEFT OUTER JOIN Orders O ON C.CustomerID = O.CustomerID AND YEAR(O.OrderDate) = 1996 GROUP BY C.CustomerID, C.CompanyName </code></pre> <p>Query using a derived table:</p> <pre><code>SELECT C.CustomerID, C.CompanyName, COUNT(dOrders.OrderID) AS TotalOrders FROM Customers C LEFT OUTER JOIN (SELECT * FROM Orders WHERE YEAR(Orders.OrderDate) = 1996) AS dOrders ON C.CustomerID = dOrders.CustomerID GROUP BY C.CustomerID, C.CompanyName </code></pre> <p>Perhaps this just wasn’t a good example, so could you show me an example where benefits of derived table are more obvious? </p> <p>thanx</p> <p><strong>REPLY TO GBN:</strong></p> <blockquote> <p>In this case, you couldn't capture both products and order aggregates if there is no relation between Customers and Products.</p> </blockquote> <p>Could you elaborate what exactly you mean? Wouldn’t the following query produce the same result set as your query:</p> <pre><code>SELECT C.CustomerID, C.CompanyName, COUNT(O.OrderID) AS TotalOrders, COUNT(DISTINCT P.ProductID) AS DifferentProducts FROM Customers C LEFT OUTER JOIN Orders O ON C.CustomerID = O.CustomerID AND YEAR(O.OrderDate) = 1996 LEFT OUTER JOIN Products P ON O.somethingID = P.somethingID GROUP BY C.CustomerID, C.CompanyName </code></pre> <p><strong>REPLY TO CADE ROUX:</strong></p> <blockquote> <p>In addition, if expressions are used to derive columns from derived columns with a lot of shared intermediate calculations, a set of nested derived tables or stacked CTEs is the only way to do it:</p> </blockquote> <pre><code>SELECT x, y, z1, z2 FROM ( SELECT * ,x + y AS z1 ,x - y AS z2 FROM ( SELECT x * 2 AS y FROM A ) AS A ) AS A </code></pre> <p>Wouldn't the following query produce the same result as your above query:</p> <pre><code>SELECT x, x * 2 AS y, x + x*2 AS z1, x - x*2 AS z2 FROM A </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload