January 31, 2012 at 12:25 am
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?
January 31, 2012 at 12:37 am
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
January 31, 2012 at 1:31 am
"diLip" (1/31/2012)
when performing left outer join or right outer join considering tables with uneven number of rows, who does sql serverdecide 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;
January 31, 2012 at 1:33 am
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.
January 31, 2012 at 1:34 am
Thank You much. that makes sense.
January 31, 2012 at 2:23 am
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
January 31, 2012 at 3:19 am
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 🙂
January 31, 2012 at 3:30 am
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
January 31, 2012 at 3:37 am
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!
January 31, 2012 at 3:44 am
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