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.

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.

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.

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.

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.

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.

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.

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.

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.

[…] Here we are at TSQL Tuesday #37 – the start of the fourth year. Sebastian Meine (@sqlity / blog) is hosting this month’s T-SQL Tuesday blogging party, and he wants us to “write about topics like the good and the bad patterns of joining you have seen out there”. (Sebastian is also running a blog series on A Join A Day.) […]

[…] This T-SQL Tuesday invite is part of my December 2012 “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. […]

[…] This month Sebastian Meine ( b | t ) is hosting T-SQL Tuesday #37. He is writing a post about joins every day this month and as such, joins are the topic of this months T-SQL Tuesday. A Join A Day – Introduction […]