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.


CREATE TABLE dbo.table1 (
Col1 int NOT NULL
);
CREATE TABLE dbo.table2 (
Col1 int NOT NULL
);
CREATE TABLE dbo.table3 (
Col1 int NOT NULL
);
INSERT INTO dbo.table1 (Col1) VALUES (1), (2), (3), (4), (5), (6);
INSERT INTO dbo.table2 (Col1) VALUES (2), (3), (4), (6);
INSERT INTO dbo.table3 (Col1) VALUES (1), (3), (6);
SELECT * FROM dbo.table1;
SELECT * FROM dbo.table2;
SELECT * FROM dbo.table3;

view raw

ItzikJoins.sql

hosted with ❤ by GitHub

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…


SELECT t1.Col1,
t2.Col1,
t3.Col1
FROM dbo.table1 AS t1
LEFT JOIN dbo.table2 AS t2 ON t2.Col1 = t1.Col1
LEFT JOIN dbo.table3 AS t3 ON t3.Col1 = t1.Col1
AND t3.Col1 = t2.Col1

view raw

ItzikSimpleLeftJoins.sql

hosted with ❤ by GitHub

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)


— cte
WITH FirstJoin AS (
SELECT t2.Col1 AS t2Col1,
t3.Col1 AS t3Col1
FROM dbo.table2 AS t2
JOIN dbo.table3 AS t3 ON t3.Col1 = t2.Col1
)
SELECT t1.Col1,
fj.t2Col1,
fj.t3Col1
FROM dbo.table1 AS t1
LEFT JOIN FirstJoin AS fj ON fj.t2Col1 = t1.Col1;
— derived table
SELECT t1.Col1 AS Table1,
fj.t2Col1 AS Table2,
fj.t3Col1 AS Table3
FROM dbo.table1 AS t1
LEFT JOIN
(
SELECT t2.Col1 AS t2Col1,
t3.Col1 AS t3Col1
FROM dbo.table2 AS t2
JOIN dbo.table3 AS t3 ON t3.Col1 = t2.Col1
) AS fj ON fj.t2Col1 = t1.Col1;

view raw

ItzikStandardCteDerived.sql

hosted with ❤ by GitHub

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.


— right join
SELECT t4.Col1 AS Table4,
t2.Col1 AS Table2,
t3.Col1 AS Table3
FROM dbo.table1 AS t1
JOIN dbo.table2 AS t2 ON t2.Col1 = t1.Col1
JOIN dbo.table3 AS t3 ON t3.Col1 = t2.Col1
RIGHT JOIN dbo.table1 AS t4 ON t4.Col1 = t1.Col1

view raw

ItzikRightJoin.sql

hosted with ❤ by GitHub

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


— Itzik Join
SELECT t1.Col1,
t2.Col1,
t3.Col1
FROM dbo.table1 AS t1
LEFT JOIN (
dbo.table2 AS t2
INNER JOIN dbo.table3 AS t3 ON t3.Col1 = t2.Col1
) ON t2.Col1 = t1.Col1;

view raw

ItzikJoin.sql

hosted with ❤ by GitHub

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

3 thoughts on “Using Parentheses with Joins”

  1. Wouldn’t we get the same results with an IF statement in the select statement?

    SELECT
    table1.col1 AS t1_col1,
    IF(table2.col1 = table3.col1, table2.col1, NULL) AS t2_col1,
    IF(table3.col1 = table2.col1, table3.col1, NULL) AS t3_col1
    FROM
    table1
    LEFT JOIN table2 ON table2.col1 = table1.col1
    LEFT JOIN table3 ON table3.col1 = table1.col1

    Reply
  2. Pingback: Joins And Parentheses – Curated SQL

Leave a Reply

%d bloggers like this: