A Join A Day – Introduction

2012-12-01 - A Join A Day, Fundamentals, General, Series

The Idea

Many of the questions I see when visiting SQL Server forums are related to JOINs. The questions range from simple "How do I return a record from the Person table together with all its records in the Order table?" to more complex ones like "What is the difference between a semi-join and an anti-semi-join?" or "When is a Hash Join better than a Merge Join?".

Every time I come across a question like this, I would like to be able to point the person to a good explanation of JOINs but I have not found a really good one yet.

So I decided to write my own.

A Join A Day

The Format

Throughout the month of December I am going to write one article every day about a join related topic. But I am not going to stop there. I am going to enlist your help. How exactly I can't tell you just yet, but stay tuned. It will be revealed soon.

Most examples will use tables in the AdventureWorks2008R2 database that is provided by Microsoft for free. You can get it here: AdventureWorks Databases – 2012, 2008R2 and 2008

The Content

Each day I am going to update this list to include all the articles that are part of this series. So come back here daily to see what is new.

A Join A Day – Introduction

Posted on Dec 1st, 2012

In December 2012 I am going to publish one post per day about SQL Server Joins. With topics ranging from fundamentals to internals there should be something interesting in there for everyone.

read more...

A Join A Day – The Inner Join

Posted on Dec 2nd, 2012

This post looks at the inner join T-SQL command. After reading you will be able to use it and explain how it handles data and how to identify it in an execution plan.

read more...

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

Posted on Dec 3rd, 2012

It is T-SQL Tuesday time again. For T-SQL Tuesday #37 I would like to ask you to join me in a month of joins.

read more...

A Join A Day – The Cross Join

Posted on Dec 4th, 2012

In this 4th post of the 'A Join A Day' series we are going to take a detailed look at the cross join statement. The cross join is probably the simplest of all join commands, but it still has its surprises. Read on, to find out more.

read more...

A Join A Day – The Left Outer Join

Posted on Dec 5th, 2012

In this fifth post of my A Join A Day series we are going to examine the left outer join. Learn about the difference to an inner join, the importance of the ON clause and take a look at the left join operator used in execution plans.

read more...

A Join A Day – The Right Outer Join

Posted on Dec 6th, 2012

On this sixth day of my A Join A Day series the topic is the right outer join. We are going to compare the right outer to the left outer join and we will see the SQL Server usually avoids them.

read more...

A Join A Day – The Full Outer Join

Posted on Dec 7th, 2012

The full outer join combines the functionality of the left outer join and the right outer join. This post sheds light on this join type and reviles another surprise when we look at the execution plan for a full outer join query.

read more...

A Join A Day – The Cross Apply

Posted on Dec 8th, 2012

The Cross Apply is the topic of this eighth day of the A Join A Day series. The Cross Apply is a unique T-SQL statement that allows us to join a table to a function. Read on for all the details...

read more...

A Join A Day – The Outer Apply

Posted on Dec 9th, 2012

This is day nine of the A Join A Day series and we are going to look at the outer apply statement. We will discover the differences to the cross apply and the similarities to the left outer join. Read on to get all the details.

read more...

A Join A Day – A Self-Join Experience

Posted on Dec 10th, 2012

A self-join is not something that is provided by a special join command. It's just a term for the pretty mundane idea of joining a table to itself. But rest assured - there is more behind it…

read more...

A Join A Day – How to Test a Join

Posted on Dec 11th, 2012

This is an advanced article showing how to use the "0-1-some" heuristic to select the right test cases when unit testing a join query. The example will approach the task following Test Driven Design.

read more...

A Join A Day – The Left Semi Join

Posted on Dec 12th, 2012

The Left Semi Join is a half join: It only includes rows from the left side in the results. A typical example for a left semi join query is a statement containing the EXEISTS keyword. However, this does not always result in an execution plan with a Left Semi Join operator.

read more...

A Join A Day – The Right Semi Join

Posted on Dec 13th, 2012

The right semi join works like the left semi join, it just switches the role of the two sides. A Right Semi Join returns only rows from the right side base on the existence of matching rows on the left.

read more...

A Join A Day – The Left Anti Semi Join

Posted on Dec 14th, 2012

The Left Anti Semi Join is the polar opposite of the Left Semi Join. While it also only returns data from the left table, it returns only those rows that are not returned by the Left Semi Join. Read on to find out how to use it.

read more...

A Join A Day – Right Anti Semi Join

Posted on Dec 15th, 2012

The Right Anti Semi Join, like the Right Semi Join, does not have a corresponding T-SQL statement. In the right circumstances however, the SQL Server optimizer will use the Right Anti Semi Join operator to build an efficient plan.

read more...

A Join A Day – Intersect

Posted on Dec 16th, 2012

INTERSECT is a keyword that takes two query expressions and calculates the set theoretical intersection from the two row sets. Under the covers SQL Server often uses the Left Semi Join operator for INTERSECT queries. One peculiarity is, that duplicate rows will be removed from the final result.

read more...

A Join A Day – Except

Posted on Dec 17th, 2012

The EXCEPT keyword can be used to calculate the set theoretical difference of two sets of rows. You can use EXCEPT every time you want to return rows from one table that are not in another table. Under the covers SQL Server uses a Left Anti Semi Join too fulfill an EXCEPT requests.

read more...

A Join A Day – Equi-Join vs. Nonequi-Join

Posted on Dec 18th, 2012

A join that is using only equality comparisons is call "Equi-Join". A join on the other hand that has at least one inequality comparison in the join condition is called "Nonequi-Join". Find common use cases and limitations by reading on.

read more...

A Join A Day – Nested Joins

Posted on Dec 19th, 2012

When joining more than two tables together, one additional JOIN key word is needed for each new table. there are many ways to group the tables in a join query together effectively building nested joins. However, the order or even grouping of tables does not change the query. Indeed, the query optimizer often makes use of that and produces an execution plan in which the order of the tables does not match the order in which they are mentioned in the query.

read more...

A Join A Day – Join Hints

Posted on Dec 20th, 2012

SQL Server cannot do an exhaustive search of all possible execution plans when compiling a query. On its quest to find the best plan, it sometimes misses the mark. See how join hints and query hints can help in a situation like that. But be aware, using join hints can backfire. Know the risks before you start using them.

read more...

A Join A Day – The Nested Loops Join

Posted on Dec 21st, 2012

This article introduces the Nested Loops Join algorithm. It shows its strengths and weaknesses to help you identify query situations for which the Nested Loops Join operator in the execution plan is an appropriate choice.

read more...

A Join A Day – The Sort Merge Join

Posted on Dec 22nd, 2012

This article introduces the Sort Merge Join algorithm. It shows its strengths and weaknesses to help you identify query situations for which the Merge Join operator in the execution plan is an appropriate choice.

read more...

A Join A Day – The Hash Join

Posted on Dec 23rd, 2012

This article introduces the Hash Join algorithm The Hash Join algorithm and shows in which situations it is a good choice. This algorithm is the most CPU and memory intensive one but if you are dealing with large tables it will often also be the fastest.

read more...

A Join A Day – Predicate, Probe & Residual

Posted on Dec 24th, 2012

This article talks about predicates, probes and residuals, all of which are used in different places of different join queries. This knowledge can help you to identify why your join query is slow and where it is spending its time.

read more...

A Join A Day – The Life Join

Posted on Dec 25th, 2012

Today's post is all about the life join. This multi-purpose join can be used to expand your horizons and maybe even help to make the world a better place.

read more...

A Join A Day – Loop Join Limitations

Posted on Dec 26th, 2012

Which logical join types can be handled by the Loop Join operator and which can't? Where does the optimizer have to jump through hoops to generate a query plan? This article gives answers to these questions.

read more...

A Join A Day – Merge Join Limitations

Posted on Dec 27th, 2012

The Merge Join algorithm is the fastest in many cases. But how do I know what is possible and more importantly when it is not a good choice at all to use the Merge Join operator in an execution plan?

read more...

A Join A Day – Hash Join Limitations

Posted on Dec 28th, 2012

Which logical join types can be conquered by the Hash Join algorithm? Are there any special cases I need to be aware of for which the Hash Join operator might not be the best fit? Read on to get the answers.

read more...

A Join A Day – UPDATE & DELETE

Posted on Dec 29th, 2012

Do you need to pull in data from several tables to update just one? This post shows how a join can be incorporated in an update or delete statement.

read more...

A Join A Day – The Merge Statement

Posted on Dec 30th, 2012

SQL Server 2008 introduced a single statement insert-update-delete: MERGE. This article introduces this statement, explains how it works and shows that it is really a full outer join under the covers.

read more...

A Join A Day – New Year, New Join

Posted on Dec 31st, 2012

While ANSI-92 joins have been around for over 20 years and the pre-ANSI-92 join syntax has been deprecated 4 SQL Server versions ago, it is still in use today. This article provides guidance on how to rewrite those old queries into the "new" outer form.

read more...

Leave your comments below to let me know your thoughts. If you have a particular question about JOINs post that too so I can include its answer here.

Categories: A Join A Day, Fundamentals, General, Series