November 20, 2014 at 6:40 pm
Thank you Sir!
I felt so happy until I read
...which leaves you with
"table_source1" JOIN "joined_table1" ON "table_source1"col1 = "joined_table1"col1
:hehe:
is it a typo or am I lost even more than I thought before?
November 20, 2014 at 6:54 pm
sgmunson (11/20/2014)
I'm pretty sure Mr. Magoo has your answer, but the bigger and maybe more important question, is why leave code hanging around that is formatted that way, when there are so many better options? Surely, it wouldn't be difficult to determine if you have the equivalent query through testing. And what of this query's overall objective? If you know what it's supposed to do, then you should also know whether the equivalent will give you the right results. Ultimately, I don't know that having an answer to your original question actually does you any favors. Just my two cents...
Thanks Steve. Yes I agree, and that's a main reason why I was bugging you - trying to find query's overall objective:-) And yes, I ran some tests assuming the original code to return required results, comparing them with my results from playing with Join and Apply, i.e. changing their orders etc.,
So I thought I figured it when I got 100% match with the real life data that already exists, but unfortunately it's not 100% guarantee it would match tomorrow when data changes:-)
So I believe it's safe to replacing [Left Join+Cross Apply] with Outer Apply, I guess
Thanks!
November 20, 2014 at 8:17 pm
I happened to do a Google search yesterday, and it brought me to an MSDN page that describes the APPLY operator, and an OUTER APPLY is roughly equivalent to a LEFT OUTER JOIN, while a CROSS APPLY is similar to an INNER JOIN, so yes, you are probably right. However, those of us trying to help you can't necessarily know what your objective is unless you quite explicitly state it, and that wasn't clear at all until your last post. It's usually a good idea to state all the details up front and help ends up coming much faster that way. Glad you have it solved.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 20, 2014 at 10:28 pm
...
FROM
Table1 AS a
CROSS APPLY data.nodes('.../Node1') AS T1(F) -- part 1
LEFT JOIN Table1 AS b
CROSS APPLY data.nodes('.../Node2') AS T2(F) -- part 2
ON b.ID = a.ID
Quick thought, this doesn't look right as there is what seams to be a totally unnecessary scan/nodes instance, only way to assess it though is by inspecting the XML structure/data. Another point is that in this case it doesn't make any difference whether CROSS or OUTER APPLY is used as those are only in the scope of the initial application of nodes XML table value function on each column instance.
😎
A cleaner way of writing the same query:
...
FROM
Table1 AS a
LEFT JOIN Table1 AS b
ON b.ID = a.ID
CROSS APPLY a.data.nodes('.../Node1') AS T1(F) -- part 1
CROSS APPLY b.data.nodes('.../Node2') AS T2(F) -- part 2
November 21, 2014 at 4:09 am
btio_3000 (11/20/2014)
so what I am trying to make sure of is that in this code is the logical order always as if it was written like you pointed in one of your replies:
SELECT ... FROM
(SELECT * FROM Table1
CROSS APPLY data.nodes('.../Node1')
) AS X
LEFT JOIN
(SELECT * FROM Table1
CROSS APPLY data.nodes('.../Node2')
) AS Y
ON X.id = Y.id
?
In other words, what if I had two different tables, Table1 and Table2, and the code is like below:
SELECT ... FROM
Table1 AS a
CROSS APPLY data.nodes('.../Node1') -- 1st cross apply
LEFT JOIN Table2 AS b
CROSS APPLY data.nodes('.../Node2') -- 2nd cross apply
ON a.id = b.id
would it always "know" to first take Table1 and cross-apply it for Node1 (X),
then take Table2 and cross-apply it for Node2 (Y),
and finally do X LEFT JOIN Y
?
Thanks!
I wonder, isn't SS optimizer free to execute it in following order as well?
tExpr1 = Table2 AS b
CROSS APPLY b.data.nodes('.../Node2') -- 2nd cross apply
tExpr2 = Table1 AS a LEFT JOIN tExpr1 ON a.id=b.id
result = tExpr2 CROSS APPLY T1.data.nodes('.../Node1') -- 1st cross apply
Optimizer can execute
(Person.Contact JOIN Sales.Individual JOIN Sales.Customer)
as
((Sales.Customer JOIN Sales.Individual) JOIN Person.Contact)
i.e. in reverse order.
https://www.simple-talk.com/sql/sql-training/the-sql-server-query-optimizer/
But can optimizer decide that
Table1 CROSS APPLY tExprA(Table1) LEFT JOIN tExpr1(Table2) ON cond(Table1,Table2)
is the same as
Table1 LEFT JOIN tExpr1 ON cond(Table1,Table2) CROSS APPLY tExprA(Table1)
?
Here x(y) means x depends on y.
November 21, 2014 at 4:35 am
If you look again at the original query:
...
FROM
Table1 AS a
CROSS APPLY data.nodes('.../Node1') AS T1(F) -- part 1
LEFT JOIN Table1 AS b
CROSS APPLY data.nodes('.../Node2') AS T2(F) -- part 2
ON b.ID = a.ID
Notice that neither CROSS APPLY block uses a table alias for the [data] column. It looks as if, for the second CROSS APPLY, the column [data] should be ambiguous because it could be sourced from either Table1 AS a or from Table1 AS b. This actually isn't the case. CROSS APPLY nodes() uses, as table source, data from the table immediately before it in the FROM list. So whilst this works (it runs, but doesn’t return the desired results):
...
FROM
Table1 AS a
LEFT JOIN Table1 AS b
ON b.ID = a.ID
CROSS APPLY a.data.nodes('.../Node1') AS T1(F) -- part 1
CROSS APPLY b.data.nodes('.../Node2') AS T2(F) -- part 2
This doesn’t run:
...
FROM
Table1 AS a
LEFT JOIN Table1 AS b
ON b.ID = a.ID
CROSS APPLY a.data.nodes('.../Node1') AS T1(F) -- part 1
CROSS APPLY a.data.nodes('.../Node2') AS T2(F) -- part 2
And fails with error “Msg 107, Level 15, State 1, Line 32
The column prefix 'a' does not match with a table name or alias name used in the query.”
This however does work, and should return the expected results (it would have been nice to have some data to test with):
...
FROM
Table1 AS a
CROSS APPLY a.data.nodes('.../Node1') AS T1(F) -- part 1
OUTER APPLY a.data.nodes('.../Node2') AS T2(F) -- part 2
The reason for the strange query is probably that the author was unaware of how OUTER APPLY works, but was smart enough to know that you can manipulate joins not by the order of the tables in the FROM list, but by the order of the ON clauses in the FROM list.
In this query:
...
FROM
Table1 AS a
LEFT JOIN Table1 AS b
ON b.ID = a.ID
CROSS APPLY a.data.nodes('.../Node1') AS T1(F) -- part 1
CROSS APPLY b.data.nodes('.../Node2') AS T2(F) -- part 2
The LEFT JOIN is converted to an INNER JOIN by CROSS APPLY b.data.nodes.
Shifting the ON clause down to below the reference to the CROSS APPLY changes the way the query works and the LEFT JOIN persists:
...
FROM
Table1 AS a
CROSS APPLY data.nodes('.../Node1') AS T1(F) -- part 1
LEFT JOIN Table1 AS b
CROSS APPLY data.nodes('.../Node2') AS T2(F) -- part 2
ON b.ID = a.ID
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
November 21, 2014 at 6:32 am
ChrisM@Work (11/21/2014)
This doesn’t run:
...FROM
Table1 AS a
LEFT JOIN Table1 AS b
ON b.ID = a.ID
CROSS APPLY a.data.nodes('.../Node1') AS T1(F) -- part 1
CROSS APPLY a.data.nodes('.../Node2') AS T2(F) -- part 2
And fails with error “Msg 107, Level 15, State 1, Line 32
The column prefix 'a' does not match with a table name or alias name used in the query.”
This however does work, and should return the expected results (it would have been nice to have some data to test with):
...FROM
Table1 AS a
CROSS APPLY a.data.nodes('.../Node1') AS T1(F) -- part 1
OUTER APPLY a.data.nodes('.../Node2') AS T2(F) -- part 2
Runs OK in 2008.
create table Table1 (
id int not null identity(1,1)
,data XML
);
insert Table1 values ('<rt><Node1>node1 val 1</Node1></rt>')
, ('<rt><Node1>node1 val 2</Node1>
<Node2>node2 val 3</Node2></rt>');
SELECT a.id
,T1.F.value('(//Node1)[1]', 'varchar(100)') x
,T2.F.value('(//Node2)[1]', 'varchar(100)') y
FROM
Table1 AS a
LEFT JOIN Table1 b ON a.id=b.id
CROSS APPLY a.data.nodes('//Node1') AS T1(F) -- part 1
CROSS APPLY a.data.nodes('//Node2') AS T2(F) -- part 2
drop table Table1 ;
November 21, 2014 at 7:02 am
Thanks Serg, my bad:
SELECT a.id
,T1.F.value('(//Node1)[1]', 'varchar(100)') x
--,T2.F.value('(//Node2)[1]', 'varchar(100)') y
FROM #Table1 AS a
CROSS APPLY a.data.nodes('//Node1') AS T1(F) -- part 1
LEFT JOIN #Table1 b
CROSS APPLY a.data.nodes('//Node2') AS T2(F) -- part 2
ON a.id=b.id
this doesn't work either:
SELECT a.id
,T1.F.value('(//Node1)[1]', 'varchar(100)') x
--,T2.F.value('(//Node2)[1]', 'varchar(100)') y
FROM #Table1 AS a
CROSS APPLY (SELECT a.id) AS T1(F) -- part 1
LEFT JOIN #Table1 b
CROSS APPLY (SELECT a.id) AS T2(F) -- part 2
ON a.id=b.id
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
November 21, 2014 at 7:30 am
Can I ask what might seem like a dumb question? Why is anyone trying so hard to write this query in such a way as to make it rather difficult to understand it's objective?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 21, 2014 at 7:37 am
sgmunson (11/21/2014)
Can I ask what might seem like a dumb question? Why is anyone trying so hard to write this query in such a way as to make it rather difficult to understand it's objective?
You mean this (already suggested by the OP)?
ChrisM@Work (11/21/2014)
...This however does work, and should return the expected results (it would have been nice to have some data to test with):
...FROM
Table1 AS a
CROSS APPLY a.data.nodes('.../Node1') AS T1(F) -- part 1
OUTER APPLY a.data.nodes('.../Node2') AS T2(F) -- part 2
...
Edit: fixed tags
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
November 21, 2014 at 7:43 am
ChrisM@Work (11/21/2014)
sgmunson (11/21/2014)
Can I ask what might seem like a dumb question? Why is anyone trying so hard to write this query in such a way as to make it rather difficult to understand it's objective?You mean this (already suggested by the OP)?
ChrisM@Work (11/21/2014)
...This however does work, and should return the expected results (it would have been nice to have some data to test with):
...FROM
Table1 AS a
CROSS APPLY a.data.nodes('.../Node1') AS T1(F) -- part 1
OUTER APPLY a.data.nodes('.../Node2') AS T2(F) -- part 2
...
Edit: fixed tags
Nope... the other versions that have been suggested. I'm just suggesting we're beating a dead horse here... That solution was suggested almost from the beginning...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply