Number of rows returned by different JOINs

  • abbas_kapasi

    SSC Rookie

    Points: 48

    Comments posted to this topic are about the item Number of rows returned by different JOINs

  • pmadhavapeddi22

    SSCertifiable

    Points: 5306

    Interesting one 🙂

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Way too easy as you only have to check the first query to get to the right answer.

    Thanks for the effort though.

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

  • domenico.delbrocco

    Mr or Mrs. 500

    Points: 587

    Finally a question for my level.

    Thanks

  • Raghavendra Mudugal

    SSChampion

    Points: 10658

    Koen Verbeeck (7/3/2014)


    Way too easy as you only have to check the first query to get to the right answer.

    Thanks for the effort though.

    +1

    (did the same) Thank you for the post, nice one.

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • Mighty

    SSCrazy Eights

    Points: 8813

    Definitely way too easy.

    Still easy, but if table B would have the values 1 and 2, then at least you could have explained something about the different join types.

  • Raghavendra Mudugal

    SSChampion

    Points: 10658

    Mighty (7/3/2014)


    Definitely way too easy.

    Still easy, but if table B would have the values 1 and 2, then at least you could have explained something about the different join types.

    This should hold for some time... (except CROSS)

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • Igor Micev

    SSC-Dedicated

    Points: 33109

    Raghavendra Mudugal (7/3/2014)


    Koen Verbeeck (7/3/2014)


    Way too easy as you only have to check the first query to get to the right answer.

    Thanks for the effort though.

    +1

    (did the same) Thank you for the post, nice one.

    +1

    Valid, only when all values are same like in the example.

    Igor Micev,
    My blog: www.igormicev.com

  • This was removed by the editor as SPAM

  • twin.devil

    SSC-Insane

    Points: 22208

    Nice question, thanks for sharing

  • Xavon

    SSCrazy

    Points: 2280

    Stewart "Arturius" Campbell (7/3/2014)


    Found myself thinking this was way too easy, where's the catch?

    only to find there wasn't one

    Nice question, thanks Abbas

    +1

    This took me way too long to answer, because I kept looking over it trying to figure out what the trick was. Ultimately I couldn't find it and went with the simple answer.

  • Iulian -207023

    SSCertifiable

    Points: 7509

    thanks for puting it together

  • david.gugg

    SSCertifiable

    Points: 5692

    Easy one, thanks. I needed an easy one after getting the last four in a row wrong.


    [font="Tahoma"]Personal blog relating fishing to database administration:[/font]

    [font="Comic Sans MS"]https://davegugg.wordpress.com[/url]/[/font]

  • Raghavendra Mudugal

    SSChampion

    Points: 10658

    david.gugg (7/3/2014)


    Easy one, thanks. I needed an easy one after getting the last four in a row wrong.

    ...been there... 🙂 totally agree.

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • stephen.long.1

    SSCrazy

    Points: 2577

    Nice question, although somewhat simple. However, the explanation given:

    The values in col1 in both the tables is matched against each other resulting in 6 rows for all the SELECT statements.

    is slightly incorrect. This statement is true for all of the join types, except the cross join, in which col1 is not matched between the tables; you just get all possible combinations. If table A contained 1,2,3, and table B contained 4,5, the cross join would still produce 6 rows.

    I suggest that you submit another QotD similar to this, but with more values in each table, such as table A containing 1,1,1,2,null,null and table B containing 1,1,3,null,null. That would be more challenging, especially with multiple nulls values to consider ("when does null=null?").

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

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