How Many Rows Returned

  • chgn01

    Hall of Fame

    Points: 3531

    Comments posted to this topic are about the item How Many Rows Returned

    --------------------------------------
    ;-)“Everything has beauty, but not everyone sees it.” ― Confucius

  • SQL_Hunt

    SSC-Dedicated

    Points: 33267

    Fairly Simple. Got 1 mark.

    Thanks.

  • Toreador

    SSChampion

    Points: 11231

    Came down to a choice between 0/Error and 9, but since the correct answer (on a case sensitive database) wasn't there, there was only one choice 🙂

  • Arno Kwetters

    Hall of Fame

    Points: 3335

    I had a little hesitation about the NULL values, but I gambled it right !

    Thanks.

  • This was removed by the editor as SPAM

  • Ed Wagner

    SSC Guru

    Points: 286958

  • twin.devil

    SSC-Insane

    Points: 22208

    nice question. thanks for sharing

  • TomThomson

    SSC Guru

    Points: 104772

    It's a nice question, but I think the explanation lacks something.

    An explanation of how each of the three joins leaves the number of "Jim rows" alone, the first join leaves the number of "Job rows" alone too since the grouping to form the derived table aliased b0 merges the two "Job rows" into one, while the two joins with #B (aliased b1 and b2) each double the number of "Job rows" because there are two "Job rows" IN #B, and since the beginning is with #A which has 2 "Job rows" and 1 "Jim rows" the number of "Jm rows" is 1 after each of teh joins while the number of "Job rows" starts at 2, is still 2 after the first join, is 4 after the second join, and 8 after the third so that after the third join there are 9 rows althogether might be useful to people who don't understand joins particularly well - although it would probably have to expressed a bt more clearly than I've put it.

    Tom

  • Revenant

    SSC-Forever

    Points: 42467

    I needed a coffee break... Nice. Thanks, Gary!

  • NBSteve

    Hall of Fame

    Points: 3227

    For anybody having trouble following the joins, just focus on the Authors in the individual joins.

    First #A has 4 authors: ('Job', 'Job', 'Jim', NULL)

    The subquery takes this same set, but groups by author, combining both 'Jobs'. So this gives ('Job', 'Jim', NULL)

    Since the first #A and the subquery are joined by an INNER JOIN (JOIN default to INNER unless OUTER or CROSS are specified), both 'Job's from part 1 match to the 'Job' in the subquery, and 'Jim' matches 'Jim'. The NULLs are eliminated by the INNER JOIN because the values are unknown and therefore can never match another value, even another NULL. So the result after the first JOIN is now only 3 items: ('Job', 'Job', 'Jim')

    The second JOIN matches the previous ('Job', 'Job', 'Jim') to #B ('Job', 'Job', 'Jim', NULL). Again, the NULL doesn't match and gets eliminated, but now because there are 2 'Job's on the left and 2 'Job's on the right, each 'Job' joins to both of the others, giving 4 'Job's. So after the second JOIN, we now have ('Job', 'Job', 'Job', 'Job', 'Jim')

    The final join takes this ('Job', 'Job', 'Job', 'Job', 'Jim') and joins again to ('Job', 'Job', 'Jim', NULL). Same thing as previous step, only this time, the 4 'Job's on the left each join to both 'Job's on the right, giving 8 'Job's plus the 1 'Jim'. So the final result set includes 8 'Job's and 1 'Jim'. ('Job', 'Job', 'Job', 'Job', 'Job', 'Job', 'Job', 'Job', 'Jim')

  • Thomas Abraham

    SSChampion

    Points: 10761

    NBSteve (6/15/2015)


    For anybody having trouble following the joins, just focus on the Authors in the individual joins. ...(Nice explanation deleted) ...

    That was a very good explanation. I appreciate it.

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • Arno Kwetters

    Hall of Fame

    Points: 3335

    Thanks NBSteve, that is how one should explain this.

  • Koen Verbeeck

    SSC Guru

    Points: 258941

    Nice question, thanks.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • SQL_kode_kracker

    Right there with Babe

    Points: 732

    Nice one..thanks 4 sharing:-)

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Yep, you have to be careful with joins that you know exactly the kinds of connections you can get.

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply