June 18, 2014 at 5:46 pm
I came across this structure today and haven't seen it before:
SELECT blablabla
FROM T1
FULL OUTER JOIN T2 ON
T1.Col1 = T2.Col1
AND T1.Col2 = T2.Col2 ON
T3.Col1 = T1.Col1
AND T3.Col2 = T1.Col2 ON
T4.Col1 = T1.Col1
AND T4.Col2 = T1.Col2
Can anyone explain the gist of what this means? I can follow along until the second "ON".
June 18, 2014 at 5:53 pm
I have no idea how that could even run without an error stating T3 and T4 weren't defined.
If you yanked it out of context, it's probably something like this:
tblA
LEFT JOIN tblB
JOIN tblC
ON tblB.col1 = tblC.col1
ON tblB.col2 = tblA.col2
What this does is inner join tblB and C together before applying the Left Join, instead of left joining and then doing a join afterwards would would significantly restrict the data on missing pieces. It's a syntax technique to control join interaction.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 18, 2014 at 6:04 pm
Sorry, guilty as charged. Was trying to limit the code to only the relevant bit. Is this better?
SELECT blablabla
FROM
T1
FULL OUTER JOIN T2
FULL OUTER JOIN T3
INNER JOIN T4 ON
T3.Col3 = T4.Col3
FULL OUTER JOIN T5 ON
T3.Col1 = T5.Col1
AND T3.Col2 = T5.Col2
FULL OUTER JOIN T6 ON
T3.Col1 = T6.Col1
AND T3.Col2 = T6.Col2
FULL OUTER JOIN T7 ON
T3.Col1 = T7.Col1
AND T3.Col2 = T7.Col2
FULL OUTER JOIN T8 ON
T3.Col1 = T8.Col1
AND T3.Col2 = T8.Col2 ON
T2.Col1 = T3.Col1
AND T2.Col2 = T3.Col2 ON
T1.Col1 = T3.Col1
AND T1.Col2 = T3.Col2
FULL OUTER JOIN T9 ON
T3.Col1 = T9.Col1
AND T3.Col2 = T9.Col2
FULL OUTER JOIN T10 ON
T3.Col2 = T10.Col2
AND T3.Col3 = T10.Col3
June 18, 2014 at 6:28 pm
autoexcrement (6/18/2014)
Sorry, guilty as charged. Was trying to limit the code to only the relevant bit. Is this better?
Well... better yes.
More understandable? Hrm. Hold that thought.
*wanders off to try to dissect this monster*
Okay, this is REALLY oddly put together, and I think I see what they were TRYING to do, but they whiffed the organization.
With my preferred indentiing:
FROM
T1
FULL OUTER JOIN
T2
FULL OUTER JOIN
T3
INNER JOIN
T4
ON T3.Col3 = T4.Col3
FULL OUTER JOIN
T5
ON T3.Col1 = T5.Col1
AND T3.Col2 = T5.Col2
FULL OUTER JOIN
T6
ON T3.Col1 = T6.Col1
AND T3.Col2 = T6.Col2
FULL OUTER JOIN
T7
ON T3.Col1 = T7.Col1
AND T3.Col2 = T7.Col2
FULL OUTER JOIN
T8
ON T3.Col1 = T8.Col1
AND T3.Col2 = T8.Col2
ON T2.Col1 = T3.Col1
AND T2.Col2 = T3.Col2
ON T1.Col1 = T3.Col1
AND T1.Col2 = T3.Col2
FULL OUTER JOIN
T9
ONT3.Col1 = T9.Col1
AND T3.Col2 = T9.Col2
FULL OUTER JOIN
T10
ONT3.Col2 = T10.Col2
AND T3.Col3 = T10.Col3
You'll notice this all starts with that random inner join deep in the structure. I just think the original author didn't back out of it quickly enough, as nothing else hooks off T4, and there's no other limiters in the inner component.
So, T1 FULL (T2 FULL ( T3 INNER T4)) FULL T5... is what this is doing. The INNER join controls the data allowed to the T2/T3 and T1/T3 connectors.
This would be more obviously written as follows:
FROM
T1
FULL OUTER JOIN
T2
FULL OUTER JOIN
T3
INNER JOIN
T4
ON T3.Col3 = T4.Col3
ON T2.Col1 = T3.Col1
AND T2.Col2 = T3.Col2
ON T1.Col1 = T3.Col1
AND T1.Col2 = T3.Col2
FULL OUTER JOIN
T5
ON T3.Col1 = T5.Col1
AND T3.Col2 = T5.Col2
FULL OUTER JOIN
T6
ON T3.Col1 = T6.Col1
AND T3.Col2 = T6.Col2
FULL OUTER JOIN
T7
ON T3.Col1 = T7.Col1
AND T3.Col2 = T7.Col2
FULL OUTER JOIN
T8
ON T3.Col1 = T8.Col1
AND T3.Col2 = T8.Col2
FULL OUTER JOIN
T9
ONT3.Col1 = T9.Col1
AND T3.Col2 = T9.Col2
FULL OUTER JOIN
T10
ONT3.Col2 = T10.Col2
AND T3.Col3 = T10.Col3
Edit: Sorry, there's also a much more obvious way to write this, because T1 and T2 don't do anything prior to the involvement of T3/4, so there's no point.
FROM
T3
JOIN
T4
ONT3.Col3 = T4.Col3
FULL OUTER JOIN
T1
ON T1.Col1 = T3.Col1
AND T1.Col2 = T3.Col2
FULL OUTER JOIN
T2
ON T2.Col1 = T3.Col1
AND T2.Col2 = T3.Col2
FULL OUTER JOIN
T5
ON T3.Col1 = T5.Col1
AND T3.Col2 = T5.Col2
FULL OUTER JOIN
T6
ON T3.Col1 = T6.Col1
AND T3.Col2 = T6.Col2
FULL OUTER JOIN
T7
ON T3.Col1 = T7.Col1
AND T3.Col2 = T7.Col2
FULL OUTER JOIN
T8
ON T3.Col1 = T8.Col1
AND T3.Col2 = T8.Col2
FULL OUTER JOIN
T9
ONT3.Col1 = T9.Col1
AND T3.Col2 = T9.Col2
FULL OUTER JOIN
T10
ONT3.Col2 = T10.Col2
AND T3.Col3 = T10.Col3
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 18, 2014 at 6:33 pm
Ohhhhhh! Okay, thank you. The indentation makes a massive difference in getting my head around it.
Now that that part of it is solved, I guess I just need to read up a bit more on nested joins, which I've never used before.
Thanks!
June 19, 2014 at 1:47 am
This might help:
“You have a query which joins two tables, say Customers and Orders. It’s an outer join because you want all customers whether or not they’ve ever placed an order – but the order can’t be empty, it must actually have some lines.”
Create a customer table with three customers, Peter, Simon and Chris
CREATE TABLE #Customers (CustomerID INT IDENTITY(1,1), CustomerName VARCHAR(20))
INSERT INTO #Customers (CustomerName) VALUES ('Peter'), ('Simon'), ('Chris')
Three orders for Peter, two orders for Simon and none for Chris (boo hoo)
CREATE TABLE #Orders (OrderID INT IDENTITY(1,1), CustomerID INT)
INSERT INTO #Orders (CustomerID) VALUES (1),(1),(1),(2),(2)
Only one of those orders has any items on it – Peter’s first order
CREATE TABLE #Orderlines (OrderlineID INT IDENTITY(1,1), OrderID INT, PartName VARCHAR(20))
INSERT INTO #Orderlines (OrderID, PartName) VALUES (1, 'Peter01'), (1, 'Peter02')
Then you write the obvious query:
-- Query 1
SELECT c.*, o.*, ol.*
FROM #Customers c
LEFT JOIN #Orders o ON o.CustomerID = c.CustomerID
INNER JOIN #Orderlines ol ON ol.OrderID = o.OrderID
The result set isn’t what you might expect – there are only two rows, corresponding to Peter’s two items. The result set looks as if SQL Server has changed the outer join to an inner join and the execution plan confirms it. Most TSQL coders know this and will usually begin testing a query where both child tables are outer joined:
-- Query 2
SELECT c.*, o.*, ol.*
FROM #Customers c
LEFT JOIN #Orders o ON o.CustomerID = c.CustomerID
LEFT JOIN #Orderlines ol ON ol.OrderID = o.OrderID
-Which returns too many rows and is tricky to filter. So they switch to this:
-- Query 3
SELECT c.*, o.*
FROM #Customers c
LEFT JOIN (
SELECT o.*, ol.OrderlineID, ol.PartName
FROM #Orders o
INNER JOIN #Orderlines ol ON ol.OrderID = o.OrderID
) o
ON o.CustomerID = c.CustomerID
-Which generates the correct result set as you would expect, and the execution plan confirms an inner join and an outer join.
You could also bracket your joins, but it’s so counterintuitive and tricky to maintain that I’m not even going to provide an example. If you must have a look, use Google – then forget what you’ve seen. There is another way, and that is to change the order of the ON clauses, so that Orders and Orderlines are inner joined before the product is joined to the Customer table:
-- Query 4
SELECT c.*, o.*, ol.*
FROM #Customers c
LEFT JOIN #Orders o
INNER JOIN #Orderlines ol
ON ol.OrderID = o.OrderID
ON o.CustomerID = c.CustomerID
This also generates the correct result set, confirmed by the execution plan. It’s easy to see what’s going on and the plan is only trivially different from query 3 – it’s slightly cheaper.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 19, 2014 at 7:30 am
What a fantastic explanation! Thank you so much for taking the time!
June 19, 2014 at 8:00 am
...
-- Query 4
SELECT c.*, o.*, ol.*FROM #Customers c
LEFT JOIN #Orders o
INNER JOIN #Orderlines ol
ON ol.OrderID = o.OrderID
ON o.CustomerID = c.CustomerID
...
I don't why, but I have always hated the above syntax. Thanks BG, we can have CTE now, so by all means I would prefer to write:
;WITH CompleteOrder
AS
(
SELECT o.*, ol.OrderlineID, ol.PartName
FROM #Orders o
INNER JOIN #Orderlines ol ON ol.OrderID = o.OrderID
)
SELECT c.*, co.*
FROM #Customers c
LEFT JOIN CompleteOrder co ON co.CustomerID = c.CustomerID
I just find t much easier to understand when reading...
June 19, 2014 at 8:06 am
I am a huge fan of CTE's as well. I guess different structures are inherently more intuitive to different people. Perhaps with enough experience that will change and I will "see" all the joins in the way that I can currently "see" the CTE datasets.
June 19, 2014 at 8:09 am
Eugene Elutin (6/19/2014)
...
-- Query 4
SELECT c.*, o.*, ol.*FROM #Customers c
LEFT JOIN #Orders o
INNER JOIN #Orderlines ol
ON ol.OrderID = o.OrderID
ON o.CustomerID = c.CustomerID
...
I don't why, but I have always hated the above syntax. Thanks BG, we can have CTE now, so by all means I would prefer to write:
;WITH CompleteOrder
AS
(
SELECT o.*, ol.OrderlineID, ol.PartName
FROM #Orders o
INNER JOIN #Orderlines ol ON ol.OrderID = o.OrderID
)
SELECT c.*, co.*
FROM #Customers c
LEFT JOIN CompleteOrder co ON co.CustomerID = c.CustomerID
I just find t much easier to understand when reading...
Me too Eugene, or the derived-table equivalent (Query 3 above).
Simply making a point - join order doesn't matter, but the order of ON clauses does.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 19, 2014 at 8:36 am
autoexcrement (6/19/2014)
What a fantastic explanation! Thank you so much for taking the time!
You're welcome - but the time was taken a while ago to demonstrate a point to a colleague and the example was later extended for use as a TSQL recruiting test.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 19, 2014 at 9:42 am
...and the example was later extended for use as a TSQL recruiting test.
have you ever tried asking on interview what kind of results the following statement will produce:
SELECT COUNT(*)
:w00t:
there is another variation of the sytax using APPLY (exactly the same plan, but different key words :-)):
SELECT c.*, ord.*
FROM #Customers c
OUTER APPLY (SELECT o.*, ol.OrderlineID, ol.PartName
FROM #Orders o
INNER JOIN #Orderlines ol ON ol.OrderID = o.OrderID
WHERE o.CustomerID = c.CustomerID
) ord
June 19, 2014 at 9:50 am
Eugene Elutin (6/19/2014)
...and the example was later extended for use as a TSQL recruiting test.
have you ever tried asking on interview what kind of results the following statement will produce:
SELECT COUNT(*)
:w00t:
there is another variation of the sytax using APPLY (exactly the same plan, but different key words :-)):
SELECT c.*, ord.*FROM #Customers c
OUTER APPLY (SELECT o.*, ol.OrderlineID, ol.PartName
FROM #Orders o
INNER JOIN #Orderlines ol ON ol.OrderID = o.OrderID
WHERE o.CustomerID = c.CustomerID
) ord
You could have some fun with these also:
SELECT COUNT(0)
SELECT COUNT(CAST(NULL AS TINYINT))
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 20, 2014 at 2:45 am
Surely SELECT COUNT(*) should return 42?
June 20, 2014 at 3:30 am
Chris Wooding (6/20/2014)
Surely SELECT COUNT(*) should return 42?
Why so? Of course not!
On a clear, moonless night about 3,000 stars are visible with the unaided eye. A small telescope will increase the number to around 100,000 stars. But this is just the beginning! The stars we can easily see are all in our corner of the Milky Way Galaxy. The entire galaxy numbers about 100 billion stars. And beyond the Milky Way are other galaxies with many shapes and sizes. Around 100 billion such galaxies are known to exist.
So, depending on it's "telescope" power, SQL Server should return one of the above figures, as, at the end, it's COUNT(*)! Instead...
You know what it does return! :hehe:
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply