How Many Rows Returned

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

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

  • Fairly Simple. Got 1 mark.

    Thanks.

  • 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 🙂

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

    Thanks.

  • This was removed by the editor as SPAM

  • nice question. thanks for sharing

  • 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

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

  • 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')

  • 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

  • Thanks NBSteve, that is how one should explain this.

  • Nice question, thanks.

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

  • Nice one..thanks 4 sharing:-)

  • 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