{"id":571,"date":"2017-09-18T21:55:27","date_gmt":"2017-09-18T21:55:27","guid":{"rendered":"http:\/\/www.manualsqlserver.com\/?p=571"},"modified":"2020-05-20T02:04:46","modified_gmt":"2020-05-20T02:04:46","slug":"clausula-union-en-select","status":"publish","type":"post","link":"https:\/\/manualsqlserver.com\/?p=571","title":{"rendered":"Cl\u00e1usula UNION en Select"},"content":{"rendered":"\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"220\" height=\"242\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_ClausulaUNION_D.png\" alt=\"\" class=\"wp-image-1235\"\/><\/figure>\n\n\n\n<h1 class=\"wp-block-heading\"><strong>Cl\u00e1usula UNION en consultas<\/strong><\/h1>\n\n\n\n<ul class=\"wp-block-list\"><li>El operador UNION permite combinar resultados de varias consultas con SELECT en un \u00fanico resultado.<\/li><li>Todas las instrucciones Select deben tener la misma cantidad de campos y todos los campos deben ser datos compatibles.<\/li><li>Los nombres de los campos del conjunto de resultados&nbsp;son iguales a los especificados en la primera consulta.<\/li><li>Los resultados que se repiten son eliminados al hacer una combinaci\u00f3n de select usando Union.<\/li><li>Tenga en cuenta la intercalaci\u00f3n del servidor y de la base de datos para que SQL Server decida cuando dos datos de tipo caracter son iguales o diferentes.<\/li><li>Se puede especificar la palabra <strong>All<\/strong> para que los resultados&nbsp;repetidos no se eliminen del conjunto de resultados final.<\/li><li>Se debe especificar solamente una cl\u00e1usula Order by y se escribe al final de la instrucci\u00f3n.<\/li><li>No es posible hacer UNION con bases de datos que tienen diferente intercalaci\u00f3n.<\/li><li>No se puede incluir campos de tipo nText.<\/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<h2 class=\"wp-block-heading\"><strong>Ejemplos<\/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>Listar los productos de categoria 1 y los productos de categor\u00eda 5<\/strong><\/p>\n\n\n\n<p>Select * from Products where CategoryID = 1<br>union<br>Select * from Products where CategoryID = 5<br>go<\/p>\n\n\n\n<p>La imagen muestra el conjunto de resultados. Note que se han resaltado las categor\u00edas de los productos.<\/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_ClausulaUNION_01-1024x413.png\" alt=\"\" class=\"wp-image-1232\" width=\"820\" height=\"330\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_ClausulaUNION_01-1024x413.png 1024w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_ClausulaUNION_01-300x121.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_ClausulaUNION_01-768x310.png 768w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_ClausulaUNION_01.png 1366w\" sizes=\"auto, (max-width: 820px) 100vw, 820px\" \/><\/figure><\/div>\n\n\n\n<p><strong>Ejercicio 2<\/strong><br><strong>Listar los clientes de Francia (France), Espa\u00f1a (Spain) y Canada.<\/strong><\/p>\n\n\n\n<p>Select CustomerID As &#8216;C\u00f3digo&#8217;, CompanyName As &#8216;Cliente&#8217;,<br>Country As &#8216;Pa\u00eds&#8217;<br>from Customers where Country = &#8216;France&#8217;<br>Union<br>Select CustomerID As &#8216;C\u00f3digo&#8217;, CompanyName As &#8216;Cliente&#8217;,<br>Country As &#8216;Pa\u00eds&#8217;<br>from Customers where Country = &#8216;Spain&#8217;<br>union<br>Select CustomerID As &#8216;C\u00f3digo&#8217;, CompanyName As &#8216;Cliente&#8217;,<br>Country As &#8216;Pa\u00eds&#8217;<br>from Customers where Country = &#8216;Canada&#8217;<br>Order by Pa\u00eds, Cliente<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_ClausulaUNION_02.png\" alt=\"\" class=\"wp-image-1233\" width=\"785\" height=\"744\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_ClausulaUNION_02.png 591w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_ClausulaUNION_02-300x284.png 300w\" sizes=\"auto, (max-width: 785px) 100vw, 785px\" \/><\/figure><\/div>\n\n\n\n<p><strong>Ejercicio 3<\/strong><br><strong>Listar los proveedores de que tienen mas de 3 productos registrados o los que tienen un producto. Ordenador por cantidad de productos descendentemente.<\/strong><\/p>\n\n\n\n<p>select S.SupplierID, S.CompanyName, S.ContactName,<br>(select count(P.ProductID)<br>from Products As P where P.SupplierID= S.SupplierID)<br>As &#8216;Cantidad de Productos&#8217;<br>from Suppliers As S<br>where (select count(P.ProductID)<br>from Products As P where P.SupplierID= S.SupplierID) &gt; 3<br>union<br>select S.SupplierID, S.CompanyName, S.ContactName,<br>(select count(P.ProductID)<br>from Products As P where P.SupplierID= S.SupplierID)<br>As &#8216;Cantidad de Productos&#8217;<br>from Suppliers As S<br>where (select count(P.ProductID)<br>from Products As P where P.SupplierID= S.SupplierID) = 1<br>order by [Cantidad de Productos] desc<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_ClausulaUNION_03.png\" alt=\"\" class=\"wp-image-1234\" width=\"831\" height=\"244\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_ClausulaUNION_03.png 898w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_ClausulaUNION_03-300x88.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_ClausulaUNION_03-768x226.png 768w\" sizes=\"auto, (max-width: 831px) 100vw, 831px\" \/><\/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><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Cl\u00e1usula UNION en consultas El operador UNION permite combinar resultados de varias consultas con SELECT en un \u00fanico resultado. Todas las instrucciones Select deben tener la misma cantidad de campos y todos los campos deben ser datos compatibles. Los nombres de los campos del conjunto de resultados&nbsp;son iguales a los especificados en la primera consulta. &hellip; <\/p>\n<p><a class=\"more-link btn\" href=\"https:\/\/manualsqlserver.com\/?p=571\">Seguir leyendo<\/a><\/p>\n","protected":false},"author":1,"featured_media":1235,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4,9],"tags":[43,51,53,61],"class_list":["post-571","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-consultasdedatos","category-registros-vistas","tag-order-by","tag-select","tag-sqlserver","tag-union","nodate","item-wrap"],"_links":{"self":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/571","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=571"}],"version-history":[{"count":1,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/571\/revisions"}],"predecessor-version":[{"id":1237,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/571\/revisions\/1237"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/media\/1235"}],"wp:attachment":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=571"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=571"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=571"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}