joins

  • when performing left outer join or right outer join considering tables with uneven number of rows, who does sql server

    decide the order of the tables?

  • I'm not sure what you're getting at.

    The number of rows are not important for a join. (at least not if they are even or not)

    And there isn't such a thing as "order" in a relational database, unless you explicitly specify an ORDER BY clause.

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

  • "diLip" (1/31/2012)


    when performing left outer join or right outer join considering tables with uneven number of rows, who does sql server

    decide the order of the tables?

    The optimizer considers different join orders and physical join types (nested loops, hash match, merge), assigns an estimated cost to each, and chooses the cheapest one. For example:

    USE AdventureWorks

    GO

    -- Hash Match

    -- Left Outer Join

    SELECT

    *

    FROM Production.ProductSubcategory AS ps

    LEFT JOIN Production.Product AS p ON

    p.ProductSubcategoryID = ps.ProductSubcategoryID

    GO

    -- Merge Join

    -- Right Outer Join

    SELECT

    ps.ProductSubcategoryID,

    COUNT_BIG(*)

    FROM Production.ProductSubcategory AS ps

    LEFT JOIN Production.Product AS p ON

    p.ProductSubcategoryID = ps.ProductSubcategoryID

    GROUP BY

    ps.ProductSubcategoryID;

  • Koen Verbeeck (1/31/2012)


    The number of rows are not important for a join. (at least not if they are even or not)

    The optimizer has to choose one row source in a join as the outer input, and one as the inner input. All physical join algorithms (nested loops, hash, merge) have different performance characteristics depending on the size of each input. In the case of hash join, for example, the table chosen as the outer (build) input dictates the size of the hash table needed.

    And there isn't such a thing as "order" in a relational database, unless you explicitly specify an ORDER BY clause.

    We are talking about join order here, not row order.

  • Thank You much. that makes sense.

  • SQL Kiwi (1/31/2012)


    Koen Verbeeck (1/31/2012)


    The number of rows are not important for a join. (at least not if they are even or not)

    The optimizer has to choose one row source in a join as the outer input, and one as the inner input. All physical join algorithms (nested loops, hash, merge) have different performance characteristics depending on the size of each input. In the case of hash join, for example, the table chosen as the outer (build) input dictates the size of the hash table needed.

    Yes, but I don't think it matters if a table has an even or uneven number of rows.

    SQL Kiwi (1/31/2012)

    And there isn't such a thing as "order" in a relational database, unless you explicitly specify an ORDER BY clause.

    We are talking about join order here, not row order.

    Right :blush:

    Reading questions early in the morning...

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

  • Koen Verbeeck (1/31/2012)


    Yes, but I don't think it matters if a table has an even or uneven number of rows.

    This may be a language thing, but we are talking about the size of one table versus another. The optimizer can transform a left join to a right join and considers this because for e.g. a hash join, the memory requirement of the right join might be less than the left join, or vice-versa 🙂

  • SQL Kiwi (1/31/2012)


    Koen Verbeeck (1/31/2012)


    Yes, but I don't think it matters if a table has an even or uneven number of rows.

    This may be a language thing, but we are talking about the size of one table versus another. The optimizer can transform a left join to a right join and considers this because for e.g. a hash join, the memory requirement of the right join might be less than the left join, or vice-versa 🙂

    It's probably a language thing 😀

    I understand that the optimizer can switch joins, but I'm confused by the "uneven rows" part.

    I guess it doesn't matter if a table has for example 100002 rows or 100003 rows. The fact that one number cannot be divided by two shouldn't have an impact on the optimizer, right?

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

  • Koen Verbeeck (1/31/2012)


    It's probably a language thing 😀

    I understand that the optimizer can switch joins, but I'm confused by the "uneven rows" part.

    I guess it doesn't matter if a table has for example 100002 rows or 100003 rows. The fact that one number cannot be divided by two shouldn't have an impact on the optimizer, right?

    Yes, "uneven" in "...considering tables with uneven number of rows..." was intended to mean that the two tables were of different size, not that any particular table had an odd number of rows!

  • SQL Kiwi (1/31/2012)


    Koen Verbeeck (1/31/2012)


    It's probably a language thing 😀

    I understand that the optimizer can switch joins, but I'm confused by the "uneven rows" part.

    I guess it doesn't matter if a table has for example 100002 rows or 100003 rows. The fact that one number cannot be divided by two shouldn't have an impact on the optimizer, right?

    Yes, "uneven" in "...considering tables with uneven number of rows..." was intended to mean that the two tables were of different size, not that any particular table had an odd number of rows!

    Aaaah. So it truly is a language thing 😀

    In Dutch, odd is "oneven", which is one letter apart from "uneven". But they do indeed mean two different things.

    It makes more sense now. Thanks for your patience 😀

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

Viewing 10 posts - 1 through 9 (of 9 total)

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