APPLY - 1

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by bitbucket » APPLY - 1

44 posts, Page 2 of 5

««1 2 3 4 5 »»»

APPLY - 1

Rate Topic

Display Mode

Topic Options

Author

Message

(Bob Brown)

Posted Thursday, September 06, 2012 4:15 AM

Say Hey Kid

Group: General Forum Members
Last Login: Friday, April 19, 2013 10:27 AM
Points: 690, Visits: 1,100

Thanks for the great question. I did good for 6:00 AM but have to research the CROSS APPLY more deeply.

Post #1355172

Stewart "Arturius" Campbell

Posted Thursday, September 06, 2012 4:43 AM

Hall of Fame

Group: General Forum Members
Last Login: Yesterday @ 3:48 AM
Points: 3,125, Visits: 4,311

Good question, Ron, thanks.

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”

Post #1355184

derek.colley

Posted Thursday, September 06, 2012 5:03 AM

SSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 3:40 AM
Points: 496, Visits: 583

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??! Crazy

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

(Bob Brown)

Posted Thursday, September 06, 2012 5:15 AM

Say Hey Kid

Group: General Forum Members
Last Login: Friday, April 19, 2013 10:27 AM
Points: 690, Visits: 1,100

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

ronmoses

Posted Thursday, September 06, 2012 6:01 AM

SSChasing Mays

Group: General Forum Members
Last Login: Today @ 5:12 AM
Points: 649, Visits: 686

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! BigGrin

Ron (a different one)

-----
a haiku...

NULL is not zero
NULL is not an empty string
NULL is the unknown

Post #1355245

mbova407

Posted Thursday, September 06, 2012 6:14 AM

Ten Centuries

Group: General Forum Members
Last Login: Friday, May 17, 2013 6:38 AM
Points: 1,327, Visits: 420

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

sknox

Posted Thursday, September 06, 2012 6:29 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 9:31 AM
Points: 1,039, Visits: 1,356

derek.colley (9/6/2012)

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

sknox

Posted Thursday, September 06, 2012 6:33 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 9:31 AM
Points: 1,039, Visits: 1,356

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

Dana Medley

Posted Thursday, September 06, 2012 6:44 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 3:37 PM
Points: 1,350, Visits: 871

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

Oh no, we're toast! I've got this. *Keyboard clatter* Woah, how'd you do that? I'm a DBA...Booyah

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

Post #1355264

sestell1

Posted Thursday, September 06, 2012 6:45 AM

SSC Eights!

Group: General Forum Members
Last Login: Today @ 10:09 AM
Points: 832, Visits: 1,206

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 »

44 posts, Page 2 of 5

««1 2 3 4 5 »»»

Permissions