Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

APPLY - 1 Expand / Collapse
Author
Message
Posted Thursday, September 06, 2012 4:15 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 10:34 AM
Points: 701, Visits: 1,140
Thanks for the great question. I did good for 6:00 AM but have to research the CROSS APPLY more deeply.
Post #1355172
Posted Thursday, September 06, 2012 4:43 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:40 AM
Points: 3,683, Visits: 4,818
Good question, Ron, thanks.


____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1355184
Posted Thursday, September 06, 2012 5:03 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, August 22, 2013 9:13 AM
Points: 496, Visits: 601
Good question, I had it in my head that CROSS APPLY acted like an INNER JOIN and OUTER APPLY acted like an OUTER JOIN, but in fact the CROSS apply would return unique combinations, excluding NULLs on the right - hence my answer of 5.

Never mind, another QOTW chance tomorrow...


---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!

Want to get the best help? Click here http://www.sqlservercentral.com/articles/Best+Practices/61537/ (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
Post #1355202
Posted Thursday, September 06, 2012 5:15 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 10:34 AM
Points: 701, Visits: 1,140
derek.colley (9/6/2012)


Derek, Tried the CAST statement in you Post Signature and got an error. Is it supposed to be valid SQL? Thanks.
Post #1355217
Posted Thursday, September 06, 2012 6:01 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, April 14, 2014 10:58 AM
Points: 819, Visits: 832
This is the first QotD in a long time (ever?) that, after getting it right, I audibly said "WOO-HOO!"... at work, in an open office area. So yeah, a few strange looks.

Thanks for the great question, I can't wait to get part 2 wrong!

Ron (a different one)


-----
a haiku...

NULL is not zero
NULL is not an empty string
NULL is the unknown
Post #1355245
Posted Thursday, September 06, 2012 6:14 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 6:27 AM
Points: 1,378, Visits: 454
I guess I don't understand the question. How is using cross-apply with a where join any different then an inner join with the ON (or where) join

SELECT 
tblA.S
, tblA.R
, tblB.S
FROM
#A tblA
INNER JOIN
#B tblB
ON tblA.R = tblB.R


Post #1355250
Posted Thursday, September 06, 2012 6:29 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 7:12 AM
Points: 1,247, Visits: 1,594
derek.colley (9/6/2012)
Good question, I had it in my head that CROSS APPLY acted like an INNER JOIN and OUTER APPLY acted like an OUTER JOIN, but in fact the CROSS apply would return unique combinations, excluding NULLs on the right - hence my answer of 5.

Never mind, another QOTW chance tomorrow...


CROSS APPLY does act like an INNER JOIN, but not like a DISTINCT. So it does exclude the NULLS (you can see that because there are no rows for Doug in the result set), but it does not only return unique combinations. But that is moot because the result set for this question is all unique combinations anyway:

S	R	S
Alpha D1234 1001
charlie D1234 1001
beta A1122 4001
harry A1122 4001
Alpha D1234 2001
charlie D1234 2001
bravo C1342 5001
beta A1122 3001
harry A1122 3001

If you change the values in table #B to produce non-unique combinations, you still get 9 results:

S	R	S
Alpha D1234 1001
charlie D1234 1001
beta A1122 1001
harry A1122 1001
Alpha D1234 1001
charlie D1234 1001
bravo C1342 1001
beta A1122 1001
harry A1122 1001

Post #1355259
Posted Thursday, September 06, 2012 6:33 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 7:12 AM
Points: 1,247, Visits: 1,594
mbova407 (9/6/2012)
I guess I don't understand the question. How is using cross-apply with a where join any different then an inner join with the ON (or where) join

SELECT 
tblA.S
, tblA.R
, tblB.S
FROM
#A tblA
INNER JOIN
#B tblB
ON tblA.R = tblB.R




This particular example isn't, which is why I wouldn't code this with a CROSS APPLY in production. But you can't JOIN to a table-value function (which is what CROSS APPLY was written for), and you can also put things like TOP X in a subquery with CROSS APPLY to limit the results from the right table, which you can't do in an INNER JOIN. Since this QotD is APPLY - 1, I'm guessing (and hoping) bitbucket has some more detailed examples of how to use CROSS APPLY coming in future questions.
Post #1355260
Posted Thursday, September 06, 2012 6:44 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 8:24 AM
Points: 1,718, Visits: 1,248
Thank you for the excellent question this morning Ron. I had to dig around in my head this morning for a while to figure it out, but caffeine makes it possible. No zombie mode for me today and you've given me a few things to read back up on.



Yeah, uh huh, you know what it is. Everything I do, I do it big

Post #1355264
Posted Thursday, September 06, 2012 6:45 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 9:10 AM
Points: 1,192, Visits: 2,024
Nice question. I've only ever used it with a TVF... It is interesting to know it works with subqueries as well.
Post #1355266
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse