{"id":1885,"date":"2020-05-26T00:54:56","date_gmt":"2020-05-26T00:54:56","guid":{"rendered":"http:\/\/manualsqlserver.com\/?p=1885"},"modified":"2020-05-26T00:54:57","modified_gmt":"2020-05-26T00:54:57","slug":"comparando-case-y-join-en-sql-server","status":"publish","type":"post","link":"https:\/\/manualsqlserver.com\/?p=1885","title":{"rendered":"Comparando Case y Join en 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\/ManualSQLServer_CaseVsJoins__D.png\" alt=\"\" class=\"wp-image-1886\" width=\"189\" height=\"238\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_CaseVsJoins__D.png 250w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_CaseVsJoins__D-238x300.png 238w\" sizes=\"auto, (max-width: 189px) 100vw, 189px\" \/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Estructura Case comparada con Join<\/h2>\n\n\n\n<p>La estructura Case evalua una expresi\u00f3n condicional y retorna uno de m\u00faltiples resultados.<\/p>\n\n\n\n<p><strong>La estructura Case tiene dos formas:<\/strong><\/p>\n\n\n\n<p>La expresi\u00f3n CASE simple compara una expresi\u00f3n con un conjunto de expresiones simples para determinar el resultado.<br>La expresi\u00f3n CASE buscada eval\u00faa un conjunto de expresiones booleanas para determinar el resultado.<\/p>\n\n\n\n<p>Ambos formatos admiten un argumento ELSE opcional.<\/p>\n\n\n\n<!--more-->\n\n\n\n<ul class=\"wp-block-list\"><li><strong>Importante:<\/strong><\/li><li><strong>CASE se puede usar en cualquier declaraci\u00f3n o cl\u00e1usula que permita una expresi\u00f3n v\u00e1lida.<\/strong><\/li><li><strong>La estructura CASE se puede usar en SELECT, UPDATE, DELETE y SET.<\/strong><\/li><li><strong>Tambi\u00e9n se puede usar en la lista de campos de la instrucci\u00f3n Select y el las expresiones con el operador IN<\/strong>  <strong>(<a href=\"https:\/\/manualsqlserver.com\/?p=172\" target=\"_blank\" rel=\"noreferrer noopener\">Ver Operadores en SQL Server<\/a>) y las cl\u00e1usulas WHERE (<a href=\"https:\/\/manualsqlserver.com\/?p=201\" target=\"_blank\" rel=\"noreferrer noopener\">Ver Filtrado de datos<\/a>), ORDER BY (<a href=\"https:\/\/manualsqlserver.com\/?p=211\" target=\"_blank\" rel=\"noreferrer noopener\">Ver Ordenamientos<\/a>) y HAVING (<a href=\"https:\/\/manualsqlserver.com\/?p=225\" target=\"_blank\" rel=\"noreferrer noopener\">Ver Filtros Having<\/a>).<\/strong><\/li><li><strong>Para mayor informaci\u00f3n <a href=\"https:\/\/manualsqlserver.com\/?p=1482\" target=\"_blank\" rel=\"noreferrer noopener\">ver Estructura Case<\/a><\/strong><\/li><\/ul>\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>En este art\u00edculo vamos a comparar el uso de Case con un Join, es necesario resaltar que si las opciones posibles del Case cambian, es necesario reescribir la consulta.<\/p>\n\n\n\n<p><strong>Usando la base de datos Northwind<br><\/strong>use Northwind<br>go<\/p>\n\n\n\n<p style=\"font-size:22px\"><strong>Ejercicio 1<\/strong><\/p>\n\n\n\n<p>a. <strong>Usando Case para la lista de productos y los nombres de las categorias<\/strong><br>select P.ProductID as &#8216;C\u00f3digo&#8217;, P.ProductName as &#8216;Descripci\u00f3n&#8217;,<br>Case CategoryID<br>When 1 then &#8216;Bebidas&#8217;<br>When 2 then &#8216;Condimentos&#8217;<br>When 3 then &#8216;Confecciones&#8217;<br>When 4 then &#8216;Productos diarios&#8217;<br>When 5 then &#8216;Cereales&#8217;<br>When 6 then &#8216;Carnes&#8217;<br>When 7 then &#8216;Conservas&#8217;<br>When 8 then &#8216;Productos marinos&#8217; End As Categor\u00eda<br>from Products As P<br>go<\/p>\n\n\n\n<p>El costo estimado de la consulta usando Case se puede ver en la siguiente imagen.<br>Costo: 0.0033744<\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_CaseVsJoins_01.png\" alt=\"\" class=\"wp-image-1887\" width=\"656\" height=\"641\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_CaseVsJoins_01.png 567w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_CaseVsJoins_01-300x293.png 300w\" sizes=\"auto, (max-width: 656px) 100vw, 656px\" \/><\/figure>\n\n\n\n<p>b. <strong>Usando Join para listar los productos y su categor\u00eda (<a href=\"https:\/\/manualsqlserver.com\/?p=215\" target=\"_blank\" rel=\"noreferrer noopener\">Ver Joins<\/a>)<\/strong><br>Select P.ProductID as &#8216;C\u00f3digo&#8217;, P.ProductName as &#8216;Descripci\u00f3n&#8217;,<br>C.CategoryName AS Categor\u00eda<br>from Products As P<br>join Categories As C on P.CategoryID = C.CategoryID<br>go<br>El costo estimado de la consulta usando Joins se puede ver en la siguiente imagen.<br>Costo: 0.0189873<\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_CaseVsJoins_02.png\" alt=\"\" class=\"wp-image-1888\" width=\"694\" height=\"502\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_CaseVsJoins_02.png 550w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_CaseVsJoins_02-300x217.png 300w\" sizes=\"auto, (max-width: 694px) 100vw, 694px\" \/><\/figure>\n\n\n\n<p class=\"has-background has-very-light-gray-background-color\"><strong>Si comparamos los valores, Case en este caso es es mas r\u00e1pido, al usar Join la consulta tiene un costo de 5,63 veces mayor, es necesario anotar nuevamente que si las categor\u00edas cambian, la consulta usando Case es necesario reescribirla.<\/strong><\/p>\n\n\n\n<p style=\"font-size:22px\"><strong>Ejercicio 2<\/strong><\/p>\n\n\n\n<p><strong>a. Usando las tablas Region y Terrotories con Case<\/strong><br>select T.TerritoryID As &#8216;C\u00f3digo&#8217;, T.TerritoryDescription As &#8216;Territorio&#8217;,<br>Region = case T.RegionID<br>When 1 then &#8216;Este&#8217;<br>When 2 then &#8216;Oeste&#8217;<br>When 3 then &#8216;Norte&#8217;<br>When 4 then &#8216;Sur&#8217; End<br>from Territories As T<br>go<br>El plan de ejecuci\u00f3n muestra este costo.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_CaseVsJoins_03.png\" alt=\"\" class=\"wp-image-1889\" width=\"652\" height=\"583\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_CaseVsJoins_03.png 510w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_CaseVsJoins_03-300x268.png 300w\" sizes=\"auto, (max-width: 652px) 100vw, 652px\" \/><\/figure>\n\n\n\n<p><strong>b. Usando Join para listar los territorios y las regiones a las que pertenecen<\/strong><br>Select T.TerritoryID as &#8216;C\u00f3digo&#8217;, T.TerritoryDescription as &#8216;Territorio&#8217;,<br>R.RegionDescription AS &#8216;Regi\u00f3n&#8217;<br>from Territories As T<br>join Region As R on T.RegionID = R.RegionID<br>go<\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_CaseVsJoins_04.png\" alt=\"\" class=\"wp-image-1890\" width=\"585\" height=\"529\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_CaseVsJoins_04.png 440w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQLServer_CaseVsJoins_04-300x271.png 300w\" sizes=\"auto, (max-width: 585px) 100vw, 585px\" \/><\/figure>\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","protected":false},"excerpt":{"rendered":"<p>Estructura Case comparada con Join La estructura Case evalua una expresi\u00f3n condicional y retorna uno de m\u00faltiples resultados. La estructura Case tiene dos formas: La expresi\u00f3n CASE simple compara una expresi\u00f3n con un conjunto de expresiones simples para determinar el resultado.La expresi\u00f3n CASE buscada eval\u00faa un conjunto de expresiones booleanas para determinar el resultado. Ambos &hellip; <\/p>\n<p><a class=\"more-link btn\" href=\"https:\/\/manualsqlserver.com\/?p=1885\">Seguir leyendo<\/a><\/p>\n","protected":false},"author":1,"featured_media":1886,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[154,40,204],"class_list":["post-1885","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-consultasdedatos","tag-case","tag-join","tag-plan-estimado","nodate","item-wrap"],"_links":{"self":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/1885","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=1885"}],"version-history":[{"count":1,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/1885\/revisions"}],"predecessor-version":[{"id":1891,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/1885\/revisions\/1891"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/media\/1886"}],"wp:attachment":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1885"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1885"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1885"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}