Using Parentheses with Joins

If this have no name, I’m calling it the Itzik Join

 

Words: 494

Time to read: ~ 3 minutes

A conversation…

…popped up in chat about JOINs based on the following table samples.

Tables
Not the most exciting of tables I’ll admit that…

The premise…

…was that you had to join both Table2 and Table3 to Table 1.

The catch…

…was you had to return all of Table1’s contents but only the contents of Table2 and Table3 where they match.

TableExpectedOutput
Expected output at the bottom…

This meant that a composite join, a LEFT JOIN with multiple ANDs wasn’t going to cut it…

SimpleLeftJoins
That 2 and 4 are not what we want…

The Standards…

…were quickly brought out, with the use of a CTE and a derived table (which is kinda the same thing)

standard
All good here

A Wild Right Join appeared…

…that worked as well which is strange since I’ve never seen a RIGHT JOIN used in real life before apart from in Reporting.

rightjoin
Works but it requires an extra join…

That was it…

…until someone mentioned…

that’d be awesome if i can inner join two other tables instead of the table mentioned after FROM keyword

That sparked a memory from a random chapter in an Itzik Ben-Gan book I read about the use of parentheses in Joins to coerce the optimizer into which joins to evaluate first.

I couldn’t find a mention of it online but the closest I could get to was this link. (Warning, the links in this article are broken so you can’t see it in action)

However, now that it was in my head, I couldn’t help but show off 🙂

Asker: that’d be awesome if i can inner join two other tables instead of the table mentioned after FROM keyword
Me: …wait, what?
A: He’s asking
t1 left join t12
t1 left join t13
t12 inner join t13
M: em…it’s possible but it’s…iffy
A:  i wanna learn it.
do your magic

itzikJoin
Strange but it works…

As far as I can tell…

… it’s a hybrid of a FROM clause and a Derived Table where we self contain it.

Instead of giving the derived table an alias, we just create an ON clause that links it back to our original FROM table and it works.

This makes the optimizer evaluate inside the brackets first and then join that result back to Table1.

itzikJoinPlan
Nice and simple

Funnily enough, although the statistics are the exact same as the derived table and CTE, the plan is different. This plan is actually simpler than the derived table/CTE which seems to need a “compute scalar” to extract the SELECT statement.

standardPlan

scalar
It’s just one of the columns, from my derived table SELECT

Black Magic…

I’ve never seen it used in the wild and I’m not sure when you would use it. It seems to suggest if you need it that there’s something strange with your query. Still, it’s nice to know that it’s there.

Thanks Itzik, you made me seem smarter than I am. I appreciate that 😀

Author: Shane O'Neill

DBA, T-SQL and PowerShell admirer, Food, Coffee, Whiskey (not necessarily in that order)...

One thought on “Using Parentheses with Joins”

What's your opinion?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s