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.
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
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.
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.
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.
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.
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.
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.
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.
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.
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...
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.
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…
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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?
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.
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.
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.
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.
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.