{"id":664,"date":"2017-09-18T22:10:22","date_gmt":"2017-09-18T22:10:22","guid":{"rendered":"http:\/\/www.manualsqlserver.com\/?p=664"},"modified":"2020-07-17T15:38:00","modified_gmt":"2020-07-17T15:38:00","slug":"subconsultas-como-tabla-derivada","status":"publish","type":"post","link":"https:\/\/manualsqlserver.com\/?p=664","title":{"rendered":"Subconsultas como tabla derivada SQL Server"},"content":{"rendered":"\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_SubconsultaTablaDerivada_D.png\" alt=\"\" class=\"wp-image-1214\" width=\"250\" height=\"291\"\/><\/figure>\n\n\n\n<h1 class=\"wp-block-heading\"><strong>Subconsultas como tabla derivada<\/strong><\/h1>\n\n\n\n<ul class=\"wp-block-list\"><li>Las subconsultas como tabla derivada son instrucciones select\u00a0que sirven como conjunto de resultados desde donde se realiza\u00a0una instrucci\u00f3n select externa. Esta instrucci\u00f3n Select se utiliza despu\u00e9s de la cl\u00e1usula From.<\/li><li>Para entender mejor debemos recordar que en una subconsulta podemos reconocer como m\u00ednimo dos instrucciones Select, una consulta externa que dentro de ella tiene una o mas instrucciones select que son las consultas internas. (<a href=\"http:\/\/www.manualsqlserver.com\/?p=219\">Ver Subconsultas<\/a>)<\/li><\/ul>\n\n\n\n<!--more-->\n\n\n\n<script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script>\n<ins class=\"adsbygoogle\"\n     style=\"display:block; text-align:center;\"\n     data-ad-layout=\"in-article\"\n     data-ad-format=\"fluid\"\n     data-ad-client=\"ca-pub-2636008503986218\"\n     data-ad-slot=\"1902193434\"><\/ins>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Las subconsultas se pueden agrupar de la siguiente forma<\/strong><\/h3>\n\n\n\n<p>1. Subconsultas que devuelven un \u00fanico valor.<br>2. Subconsultas que devuelven un conjunto de datos.<br>3. Subconsultas como tablas derivadas.<\/p>\n\n\n\n<p>En este art\u00edculo se ver\u00e1 como usar las subconsultas como tablas derivadas.<\/p>\n\n\n\n<p><strong>La estructura de las subconsultas como tablas derivadas tienen una estructura similar a la siguiente instrucci\u00f3n<\/strong><\/p>\n\n\n\n<p class=\"has-background has-normal-font-size has-very-light-gray-background-color\"><strong>select TablaDerivada.Campo1, TablaDerivada.Campo2, TablaDerivada.Campo3<br>from (select * from Tabla) As TablaDerivada<\/strong><\/p>\n\n\n\n<p><strong>Note que despu\u00e9s de la instrucci\u00f3n From se ha especificado una instrucci\u00f3n&nbsp;<\/strong><strong>select que es la que se considera una tabla derivada.<\/strong><\/p>\n\n\n\n<p><strong>Sugerencia: el conjunto de resultados de subconsultas se pueden obtener tambi\u00e9n&nbsp;usando Joins (<a href=\"http:\/\/www.manualsqlserver.com\/?p=215\">Ver Joins<\/a>), se sugiere ver el plan de ejecuci\u00f3n para elegir la que tenga menor costo.<\/strong><\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Ejercicios<\/strong><\/h2>\n\n\n\n<p>Usando Northwind<br>use Northwind<br>go<\/p>\n\n\n\n<p><strong>Ejercicio 1<\/strong><br><strong>Mostrar los c\u00f3digos y los nombres de los proveedores (Suppliers)<\/strong><\/p>\n\n\n\n<p>select P.SupplierID, P.CompanyName<br>from (select * from Suppliers) As P<br>go<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_SubconsultaTablaDerivada_01.png\" alt=\"\" class=\"wp-image-1211\" width=\"702\" height=\"596\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_SubconsultaTablaDerivada_01.png 490w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_SubconsultaTablaDerivada_01-300x255.png 300w\" sizes=\"auto, (max-width: 702px) 100vw, 702px\" \/><\/figure><\/div>\n\n\n\n<p>Note que primero se cre\u00f3 una consulta con los proveedores a la que se le asign\u00f3 el alias P, luego esa consultas sirvi\u00f3 como tabla derivada para a partir de su conjunto de resultados s\u00f3lo se muestre los c\u00f3digos (SupplierID) y el nombre de la compa\u00f1\u00eda (CompanyName)<\/p>\n\n\n\n<p><strong>Ejercicio 2<\/strong><br><strong>Mostrar detalles de compras de los clientes. Primero se va a crear una instrucci\u00f3n que tenga el detalle de las ventas incluyendo los datos del cliente, el producto y la orden. A partir de esa instrucci\u00f3n obtener detalles de ventas.&nbsp;<\/strong><\/p>\n\n\n\n<p><strong>La instrucci\u00f3n select que servir\u00e1 como subconsulta de tabla derivada es la siguiente:<\/strong><\/p>\n\n\n\n<p>select C.CustomerID As &#8216;C\u00f3d. Cliente&#8217;, C.CompanyName As &#8216;Cliente&#8217;,<br>O.OrderID As &#8216;N\u00ba Orden&#8217;, Year(O.OrderDate) As &#8216;A\u00f1o&#8217;,<br>DATENAME(MM,O.OrderDate) As &#8216;Mes&#8217;,<br>P.ProductName As &#8216;Producto&#8217;, OD.Quantity As &#8216;Cantidad&#8217;,<br>OD.UnitPrice As &#8216;Precio Venta&#8217;<br>from Customers As C<br>join Orders As O on C.CustomerID = O.CustomerID<br>join [Order Details] As OD on O.OrderID = Od.OrderID<br>join Products As P on OD.ProductID = P.ProductID<br>go<\/p>\n\n\n\n<p>La instrucci\u00f3n anterior servir\u00e1 como conjunto de resultados para las subconsultas como tabla derivada, a esta consulta se le asignar\u00e1&nbsp;el alias Co de Compras.<\/p>\n\n\n\n<p><strong>2.1. Listar las compras del cliente con c\u00f3digo ALFKI.<\/strong><\/p>\n\n\n\n<p>select Co.[N\u00ba Orden], Co.Producto, Co.Cantidad, Co.[Precio Venta]<br>from (select C.CustomerID As &#8216;C\u00f3d. Cliente&#8217;, C.CompanyName As &#8216;Cliente&#8217;,<br>O.OrderID As &#8216;N\u00ba Orden&#8217;, Year(O.OrderDate) As &#8216;A\u00f1o&#8217;,<br>DATENAME(MM,O.OrderDate) As &#8216;Mes&#8217;,<br>P.ProductName As &#8216;Producto&#8217;, OD.Quantity As &#8216;Cantidad&#8217;,<br>OD.UnitPrice As &#8216;Precio Venta&#8217;<br>from Customers As C<br>join Orders As O on C.CustomerID = O.CustomerID<br>join [Order Details] As OD on O.OrderID = Od.OrderID<br>join Products As P on OD.ProductID = P.ProductID) As Co<br>where Co.[C\u00f3d. Cliente] = &#8216;ALFKI&#8217;<br>go<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_SubconsultaTablaDerivada_01_a.png\" alt=\"\" class=\"wp-image-1212\" width=\"760\" height=\"467\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_SubconsultaTablaDerivada_01_a.png 684w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_SubconsultaTablaDerivada_01_a-300x184.png 300w\" sizes=\"auto, (max-width: 760px) 100vw, 760px\" \/><\/figure><\/div>\n\n\n\n<script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script>\n<ins class=\"adsbygoogle\"\n     style=\"display:block; text-align:center;\"\n     data-ad-layout=\"in-article\"\n     data-ad-format=\"fluid\"\n     data-ad-client=\"ca-pub-2636008503986218\"\n     data-ad-slot=\"1902193434\"><\/ins>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script>\n\n\n\n<p><strong>2.2. Listar las compras de agosto de 1997<\/strong><\/p>\n\n\n\n<p>select Co.[C\u00f3d. Cliente], Co.Cliente, Co.[N\u00ba Orden], Co.Producto,<br>Co.Cantidad, Co.[Precio Venta], Co.A\u00f1o , Co.Mes<br>from <strong>(select C.CustomerID As &#8216;C\u00f3d. Cliente&#8217;, C.CompanyName As &#8216;Cliente&#8217;,<br>O.OrderID As &#8216;N\u00ba Orden&#8217;, Year(O.OrderDate) As &#8216;A\u00f1o&#8217;,<br>DATENAME(MM,O.OrderDate) As &#8216;Mes&#8217;,<br>P.ProductName As &#8216;Producto&#8217;, OD.Quantity As &#8216;Cantidad&#8217;,<br>OD.UnitPrice As &#8216;Precio Venta&#8217;<br>from Customers As C<br>join Orders As O on C.CustomerID = O.CustomerID<br>join [Order Details] As OD on O.OrderID = Od.OrderID<br>join Products As P on OD.ProductID = P.ProductID<\/strong>) As Co<br>where Co.A\u00f1o = 1997 and co.Mes = &#8216;Agosto&#8217;<br>go<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_SubconsultaTablaDerivada_02-1024x329.png\" alt=\"\" class=\"wp-image-1213\" width=\"772\" height=\"247\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_SubconsultaTablaDerivada_02-1024x329.png 1024w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_SubconsultaTablaDerivada_02-300x96.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_SubconsultaTablaDerivada_02-768x247.png 768w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_SubconsultaTablaDerivada_02.png 1264w\" sizes=\"auto, (max-width: 772px) 100vw, 772px\" \/><\/figure><\/div>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Subconsultas como tabla derivada Las subconsultas como tabla derivada son instrucciones select\u00a0que sirven como conjunto de resultados desde donde se realiza\u00a0una instrucci\u00f3n select externa. Esta instrucci\u00f3n Select se utiliza despu\u00e9s de la cl\u00e1usula From. Para entender mejor debemos recordar que en una subconsulta podemos reconocer como m\u00ednimo dos instrucciones Select, una consulta externa que dentro &hellip; <\/p>\n<p><a class=\"more-link btn\" href=\"https:\/\/manualsqlserver.com\/?p=664\">Seguir leyendo<\/a><\/p>\n","protected":false},"author":1,"featured_media":1214,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4,6,9,231],"tags":[40,51,54,56],"class_list":["post-664","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-consultasdedatos","category-funcionessql","category-registros-vistas","category-subconsultas","tag-join","tag-select","tag-subconsulta","tag-tabla-derivada","nodate","item-wrap"],"_links":{"self":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/664","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=664"}],"version-history":[{"count":1,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/664\/revisions"}],"predecessor-version":[{"id":1216,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/664\/revisions\/1216"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/media\/1214"}],"wp:attachment":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=664"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=664"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=664"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}