{"id":1465,"date":"2012-12-18T13:31:46","date_gmt":"2012-12-18T18:31:46","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=1465"},"modified":"2014-11-13T13:50:51","modified_gmt":"2014-11-13T18:50:51","slug":"t-sql-tuesday-37-roundup","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/1465\/t-sql-tuesday-37-roundup\/","title":{"rendered":"T-SQL Tuesday #37 – Roundup"},"content":{"rendered":"
\n

Introduction<\/h3>\n

\nAnother week has flown by and it is Tuesday again. And with that I really should not delay writing the T-SQL Tuesday round-up any longer. So here it is:\n<\/p>\n

\nThank you everybody who took the time to participate in this great party. I also want to thank Adam Machanic (B<\/a>|T<\/a>) for inventing T-SQL Tuesday and for letting me host this month.\n<\/p>\n

The Posts<\/h3>\n

\nBelow is the list of all participating posts that I was able to find. Please let me know if I missed anything.\n<\/p>\n

Joins in single-table queries<\/a><\/h5>\n

\nRob Farley (B<\/a>|T<\/a>) writes about joins that we usually don't think about when writing queries because they happen without the JOIN key word being used. They are those implicit joins that SQL Server has to execute to get the data together if a single index is not enough.\n<\/p>\n

T-SQL Tuesday #37: Join me in a month of Joins<\/a><\/h5>\n

\nKoen Verbeeck (B<\/a>|T<\/a>) reminds us of the long forgotten old style join syntax that Oracle queries used to feature. They used WHERE col1 (+) = col2<\/span> as right outer join equivalent. Make sure to read Rob Farley's comment to this post too.\n<\/p>\n

T-SQL TuesDay #37 \u2013 Join me in a Month of Joins<\/a><\/h5>\n

\nJeffrey Verheul (a.k.a. DevJef) (B<\/a>|T<\/a>) reviewed a merge statement written by a college that not only wasted unnecessary CPU cycles by copying data into a table variable without using it afterwards, but worse also updated more rows than intended.\n<\/p>\n

T-SQL Tuesday #37 \u2013 A Month of Joins<\/a><\/h5>\n

\nSteve Jones (B<\/a>|T<\/a>) writes about the importance of readable code. This is one of my personal pet-peeves too. We spend a lot more time maintaining code than writing it in the first place. If the code we have to maintain would be readable all our lives would be so much simpler.\n<\/p>\n

T-SQL TUESDAY 37: HIDDEN JOINS<\/a><\/h5>\n

\nMatt Velic (B<\/a>|T<\/a>) reminds us that queries do not always have to use the key word JOIN<\/span> for SQL Server to select a Join operator.\n<\/p>\n

LEFT JOIN: Location, Location, Location \u2013 #TSQL2sDay<\/a><\/h5>\n

\nRick Krueger (B<\/a>|T<\/a>) reaffirms how important it is to include the complete join condition in the ON<\/span> clause. While you might get it to work otherwise you will more likely end up with buggy code.\n<\/p>\n

Update Table Based on a JOIN<\/a><\/h5>\n

\nTed Krueger (B<\/a>|T<\/a>) shows us how to use the power of joins to qualify the rows to change in an UPDATE<\/span> statement.\n<\/p>\n

T-SQL Tuesday #37 \u2013 Join me in a Month of Joins<\/a><\/h5>\n

\nAlan Dykes (B<\/a>|G<\/a>) explains how you can use an outer self-join to identify gaps in a sequence of age ranges.\n<\/p>\n

A Month of Joins - T-SQL Tuesday #37<\/a><\/h5>\n

\nAmi Levin (B<\/a>|T<\/a>) gives us a quick overview of the strength and weaknesses of the three physical join operators.\n<\/p>\n

T-SQL Tuesday #37 \u2013 RIGHT JOIN, LEFT JOIN, raw, raw, raw<\/a><\/h5>\n

\nMickey Stuewe (B<\/a>|T<\/a>) shows an example of a nested reversed join creating a readability roadblock. Mickey also shows how to rewrite it into a straight and easy to follow join line. — My comment: join zen.\n<\/p>\n

T-SQL Tuesday #37: JOIN Formatting\u2026 It\u2019s all about S-T-Y-L-E\u2026<\/a><\/h5>\n

\nJason E Bacani (B<\/a>|T<\/a>) gives a few suggestions of how to format joins.\n<\/p>\n

T-SQL Tuesday #037 \u2013 SARGability \u2013 Not Just For WHERE Clauses<\/a><\/h5>\n

\nThomas Rushton (B<\/a>|T<\/a>) reminds us that SARGability is a very important concept and applies not only to WHERE clauses.\n<\/p>\n

Shall We Join Or Shall We Split Now?<\/a><\/h5>\n

\nValentino Vranken (B<\/a>|T<\/a>) introduces the Join() and Split() functions in SSRS.\n<\/p>\n

A Join in Many Languages<\/a><\/h5>\n

\nMike Decuir (B<\/a>|T<\/a>) takes a simple join query and shows us how much work it would be to implement that functionality in other programming languages. He is giving examples in Python, Powershell, Pig and R.\n<\/p>\n

Let\u2019s Talk About Joins<\/a><\/h5>\n

\nJason Brimhall (B<\/a>|G<\/a>) gives a long list of different options to write a join against two tables that do not necessarily match on the join column. He ends in a short paragraph about implicit conversions.\n<\/p>\n

TSQL Tuesday #37 \u2013 Joins and Implicit Conversions<\/a><\/h5>\n

\nWayne Sheffield (B<\/a>|T<\/a>) picks up the implicit conversion topic and shows just how much havoc it can wreak on your queries.\n<\/p>\n

TSQL2sDay #37 JOIN<\/a><\/h5>\n

\nTamera Michelle Clark (B<\/a>|T<\/a>) took a survey at her work place to find out if people when joining others at the table during the holiday season would prefer a left or a right join approach.\n<\/p>\n

T-SQL Tuesday #37: Invite To Join Me In a Month of Joins<\/a><\/h5>\n

\nChris Yates (B<\/a>|T<\/a>) gives a brief refresher on the join topic in general based on queries he has seen in the wild.\n<\/p>\n

A Join A Day \u2013 How to Test a Join<\/a><\/h5>\n

\nThis is my own contribution to this T-SQL Tuesday. In this article I am showing how to use the \"0-1-some\" heuristic to select the right test cases when test driving a join query.\n<\/p>\n

Summary<\/h3>\n

\nWhat a great party. Thanks again for participating and writing all those great posts.\n<\/p>\n

A Join A Day<\/h3>\n

\nThis T-SQL Tuesday was part of my \"A Join A Day\" blog post series. You can find the table of contents with all posts published so far in the introductory post: A Join A Day \u2013 Introduction<\/a>. Check back there frequently throughout the month.\n<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

This is the roundup for this month’s T-SQL Tuesday #37. It includes a short review of all participating blog posts.<\/p>\n

[more…]<\/a><\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[5,13],"tags":[],"class_list":["post-1465","post","type-post","status-publish","format-standard","hentry","category-general","category-t-sql-tuesday"],"acf":[],"yoast_head":"\nT-SQL Tuesday #37 - Roundup - sqlity.net<\/title>\n<meta name=\"description\" content=\"This is the roundup for this month's T-SQL Tuesday #37. It includes a short review of all participating blog posts.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/sqlity.net\/en\/1465\/t-sql-tuesday-37-roundup\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"T-SQL Tuesday #37 - Roundup - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"This is the roundup for this month's T-SQL Tuesday #37. It includes a short review of all participating blog posts.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/1465\/t-sql-tuesday-37-roundup\/\" \/>\n<meta property=\"og:site_name\" content=\"sqlity.net\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/sqlity.net\" \/>\n<meta property=\"article:published_time\" content=\"2012-12-18T18:31:46+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T18:50:51+00:00\" \/>\n<meta name=\"author\" content=\"Sebastian Meine\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@sqlity\" \/>\n<meta name=\"twitter:site\" content=\"@sqlity\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Sebastian Meine\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1465\\\/t-sql-tuesday-37-roundup\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1465\\\/t-sql-tuesday-37-roundup\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"T-SQL Tuesday #37 – Roundup\",\"datePublished\":\"2012-12-18T18:31:46+00:00\",\"dateModified\":\"2014-11-13T18:50:51+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1465\\\/t-sql-tuesday-37-roundup\\\/\"},\"wordCount\":875,\"commentCount\":3,\"articleSection\":[\"General\",\"T-SQL Tuesday\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/1465\\\/t-sql-tuesday-37-roundup\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1465\\\/t-sql-tuesday-37-roundup\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1465\\\/t-sql-tuesday-37-roundup\\\/\",\"name\":\"T-SQL Tuesday #37 - Roundup - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"datePublished\":\"2012-12-18T18:31:46+00:00\",\"dateModified\":\"2014-11-13T18:50:51+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"This is the roundup for this month's T-SQL Tuesday #37. It includes a short review of all participating blog posts.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1465\\\/t-sql-tuesday-37-roundup\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/1465\\\/t-sql-tuesday-37-roundup\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1465\\\/t-sql-tuesday-37-roundup\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"T-SQL Tuesday #37 – Roundup\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\",\"name\":\"sqlity.net\",\"description\":\"Quality for SQL\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/sqlity.net\\\/en\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\",\"name\":\"Sebastian Meine\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"caption\":\"Sebastian Meine\"},\"sameAs\":[\"http:\\\/\\\/sqlity.net\",\"https:\\\/\\\/x.com\\\/sqlity\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"T-SQL Tuesday #37 - Roundup - sqlity.net","description":"This is the roundup for this month's T-SQL Tuesday #37. It includes a short review of all participating blog posts.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/sqlity.net\/en\/1465\/t-sql-tuesday-37-roundup\/","og_locale":"en_US","og_type":"article","og_title":"T-SQL Tuesday #37 - Roundup - sqlity.net","og_description":"This is the roundup for this month's T-SQL Tuesday #37. It includes a short review of all participating blog posts.","og_url":"https:\/\/sqlity.net\/en\/1465\/t-sql-tuesday-37-roundup\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2012-12-18T18:31:46+00:00","article_modified_time":"2014-11-13T18:50:51+00:00","author":"Sebastian Meine","twitter_card":"summary_large_image","twitter_creator":"@sqlity","twitter_site":"@sqlity","twitter_misc":{"Written by":"Sebastian Meine","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/sqlity.net\/en\/1465\/t-sql-tuesday-37-roundup\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/1465\/t-sql-tuesday-37-roundup\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"T-SQL Tuesday #37 – Roundup","datePublished":"2012-12-18T18:31:46+00:00","dateModified":"2014-11-13T18:50:51+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/1465\/t-sql-tuesday-37-roundup\/"},"wordCount":875,"commentCount":3,"articleSection":["General","T-SQL Tuesday"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/1465\/t-sql-tuesday-37-roundup\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/1465\/t-sql-tuesday-37-roundup\/","url":"https:\/\/sqlity.net\/en\/1465\/t-sql-tuesday-37-roundup\/","name":"T-SQL Tuesday #37 - Roundup - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"datePublished":"2012-12-18T18:31:46+00:00","dateModified":"2014-11-13T18:50:51+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"This is the roundup for this month's T-SQL Tuesday #37. It includes a short review of all participating blog posts.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/1465\/t-sql-tuesday-37-roundup\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/1465\/t-sql-tuesday-37-roundup\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/1465\/t-sql-tuesday-37-roundup\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"T-SQL Tuesday #37 – Roundup"}]},{"@type":"WebSite","@id":"https:\/\/sqlity.net\/en\/#website","url":"https:\/\/sqlity.net\/en\/","name":"sqlity.net","description":"Quality for SQL","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/sqlity.net\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c","name":"Sebastian Meine","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","caption":"Sebastian Meine"},"sameAs":["http:\/\/sqlity.net","https:\/\/x.com\/sqlity"]}]}},"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2wXuw-nD","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/1465","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/comments?post=1465"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/1465\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=1465"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=1465"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=1465"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}