T-SQL Tuesday #37 – Roundup

Introduction

Another 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:

Thank you everybody who took the time to participate in this great party. I also want to thank Adam Machanic (B|T) for inventing T-SQL Tuesday and for letting me host this month.

The Posts

Below is the list of all participating posts that I was able to find. Please let me know if I missed anything.

Joins in single-table queries

Rob Farley (B|T) 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.

T-SQL Tuesday #37: Join me in a month of Joins

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

T-SQL TuesDay #37 – Join me in a Month of Joins

Jeffrey Verheul (a.k.a. DevJef) (B|T) 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.

T-SQL Tuesday #37 – A Month of Joins

Steve Jones (B|T) 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.

T-SQL TUESDAY 37: HIDDEN JOINS

Matt Velic (B|T) reminds us that queries do not always have to use the key word JOIN for SQL Server to select a Join operator.

LEFT JOIN: Location, Location, Location – #TSQL2sDay

Rick Krueger (B|T) reaffirms how important it is to include the complete join condition in the ON clause. While you might get it to work otherwise you will more likely end up with buggy code.

Update Table Based on a JOIN

Ted Krueger (B|T) shows us how to use the power of joins to qualify the rows to change in an UPDATE statement.

T-SQL Tuesday #37 – Join me in a Month of Joins

Alan Dykes (B|G) explains how you can use an outer self-join to identify gaps in a sequence of age ranges.

A Month of Joins - T-SQL Tuesday #37

Ami Levin (B|T) gives us a quick overview of the strength and weaknesses of the three physical join operators.

T-SQL Tuesday #37 – RIGHT JOIN, LEFT JOIN, raw, raw, raw

Mickey Stuewe (B|T) 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.

T-SQL Tuesday #37: JOIN Formatting… It’s all about S-T-Y-L-E…

Jason E Bacani (B|T) gives a few suggestions of how to format joins.

T-SQL Tuesday #037 – SARGability – Not Just For WHERE Clauses

Thomas Rushton (B|T) reminds us that SARGability is a very important concept and applies not only to WHERE clauses.

Shall We Join Or Shall We Split Now?

Valentino Vranken (B|T) introduces the Join() and Split() functions in SSRS.

A Join in Many Languages

Mike Decuir (B|T) 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.

Let’s Talk About Joins

Jason Brimhall (B|G) 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.

TSQL Tuesday #37 – Joins and Implicit Conversions

Wayne Sheffield (B|T) picks up the implicit conversion topic and shows just how much havoc it can wreak on your queries.

TSQL2sDay #37 JOIN

Tamera Michelle Clark (B|T) 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.

T-SQL Tuesday #37: Invite To Join Me In a Month of Joins

Chris Yates (B|T) gives a brief refresher on the join topic in general based on queries he has seen in the wild.

A Join A Day – How to Test a Join

This 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.

Summary

What a great party. Thanks again for participating and writing all those great posts.

A Join A Day

This 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 – Introduction. Check back there frequently throughout the month.