Great explanation! A word of note for those who were also not understanding (like me) due to the suit system ; The suit strength goes clubs < diamonds < hearts < spades I just found out that apparently that there's two systems of suit strength : clubs < diamonds < hearts < spades (alphabetical) and diamonds < clubs < hearts < spades (alternating) I know the latter. I didn't know the alphabetical order even existed.
Great vidéo! My sum up: Definitions: [from other source]The Optimiser will decide which table will be the _inner_ or _outer_ table. - The outer table is the source of rows to match against the inner table. It is usually read from disk. - The inner table is the table that is probed for matches. It is usually held in memory, is usually the source table for hashing, and if possible, is the smallest table of the two being joined. Nested Loops: TL;DR : good if you only have a small subset of rows to join from the outer table, AND you have an index on the inner table. For each row in the outer table, it will look for all the row in the inner table. Without any index, you get a complexity of o(number of rows in Table1 * number of rows in Table2). This is very inefficient, unless you only have a small number of rows from the outer table to join (or a small subset of the outer table to join), AND you have an index on the joining column of the inner table. Thanks to the index of the inner table, the nested loop (that looks for the matching row in the inner table) will be fast. And as there are few rows triggering the nested loop, the whole operation will be pretty Note that if you also have an index for the outer table, finding the subset of rows that we want to join is even faster; but this is optional. If the number of rows to join from the outer table is small, but not that small, the Optimiser may have a hard time deciding whether to use a Nested Loop or Hash Join, so having up to date statistics is important. Merge Join: TL;DR: good even if you have lots of rows to join, but requires an index on the outer table to be efficient. Both tables are first sorted by the joining column (¿sorted in memory?), and then joined. The sorting allows to read the the inner table bit by bit : the RDMS reeds the 1st row from the sorted outer table, and then looks in the inner table for matching rows. When the next row no longer matches, it means that there won’t be any other matching row, as the table is sorted. This allows to only have to read each table once. Merge joins are thus efficient even if many rows have to be joined. But the sorting operation is expensive… Sorting both tables means a complexity roughly of o(#Table1 * Log #Table1 + #Table2 * Log #Table2) However, if you have an index for the outer table (on the joining column), then only the inner table will have to be sorted. Indeed, indexes are sorted, so the RDBMS will use the outer table index to read it in a sorted manner. Sadly, even if you have an index for the inner table, Oracle DB will still have to sort it [10:28]. Hash Join: TL;DR: works only for equality joins (e.g. not for “joincolumTable1 > joincolumnTable2”). Good for large amount of rows to join. It is the most efficient most of the time. Complexity is o(number of rows in Table1 + number of rows in Table2) A hash table of table1 is computed in memory, and then the value of each of table2 is hashed, and an equality of hash value is looked for in the hash table.
I am incredibly grateful to Chris for their invaluable tutorials on hash join, merge join, and nested loop. These concepts always seemed daunting to me when I encountered them in execution plans, but their clear and concise explanations have helped me gain a much deeper understanding. Their expertise and dedication to educating others are truly commendable. A big thank you for demystifying these complex topics and making them accessible to all!
1.Nested Join - nested for loop - O(N * N) - easiest to implement - time consuming for large dataset - better with either small datasets or index on join attributes 2.Merge Join - sort them first and compare - O(NlogN) + O(NlogN) - Efficient for large datasets - pre-join preparation required (sorting) - scanning of relation happens once while joining - can leverage indexes if available to make it faster 3.Hash Join - using hashing - not good for range queries - creating a hashtable for the query you are searching ( user_id is the key) - used for equi joins - efficient for large datasets - requires additional memory - pre join preparation required -> hash table construction - Hash function should distribute data evenly SQL Engine : Take a look at data , look statistics across all table using cardinality
Woah this is good stuff. Easily one of the best explaination I seen in a long time. Clear and Concise. The anim also nicely done. Totally can visualise and relate in split seconds. Kudos!
this is super good. just for my understanding, you said when we just have few card (5) from outer deck to match nested loop becomes faster as it can start matching from the first card however, hash join still needs to create hash table for all 52 cards from outer table. why should it create hash table for 52 instead of 5, assuming the filter is already applied and it knows those 5 rows already.
Thanks Perhaps I wasn't clear on this - I was thinking of a Top-N, "get the first 5 rows then stop" query. Instead of a where clause that only matches 5 rows, the result set (could) be bigger. But we'll stop as soon as we've returned 5 rows. Because nested loops join rows immediately, it can stop as soon as it reads 5 rows from the outer table (assuming they all join to a row in the inner table). A hash join always builds the hash table on the whole outer table first. If the where clause identifies 5 rows from the outer table, you're right the hash join will only build on these 5 rows.
Excellent explanation of join strategies. And your use of decks of cards helps to visualize each strategy in your head for a better understanding of each one. Thank you.
this video helps me to understand the joining strategies as well helps to make sense & reason out the usage of some of the complex concepts used in Apache Spark, fortunate to find this video, fantastic explanation, very easy to understand the concepts that felt really confusing, thank you very much :)
Thank you for a clear explanation! But I am still wondering why the merge sort needs to start on the previous value if we know it was already joined with the last value from the outer deck. Is there something I have missed? Thanks :)
There could be many rows with the same value. Going back to the previous value is simple way to ensure you always capture them all. It also works for greater/less than comparisons - if the join is C1 > C2 then you'll be on the last row/value in the inner deck after the first row from the outer. But likely need to revisit most of the rows in the inner deck when you go to the second in the outer.
This might be the first comment that I have ever make in youtube :) I am already familiar to join types but watching this was quite nice. you explained it so simple and I love it Chris 👍👍👍. I would like to add this video to my blog also if it is okay for you. Nice moustache by the way :)
this is freaking awesome i learnt something in depth one of the best channel i have seen so far i think using hints we can command the optimizer we want this join type while doing certain query using pinned explain plan
Thanks! You have to be careful using hints. To ensure the optimizer picks a particular plan, typically you need LOTS of hints to ensure it does this. When using Oracle Database we recommend you use SQL Plan Management (SQL profiles & baselines) to manage plans instead.
Great video. Thanks. But I didn't understand the reason why the example of joining only five cards in the outer deck shows the disadvantage of hash join (from 8:17 to 8:25). I mean, why can't we simply apply the hash function to those five values? That only costs five operations to construct the hash memory structure?
How do you know what the top five values are before doing the join? If you want to (inner) join t1 to t2 and get the first five joined rows there's no general way to pick five from t1 that will join to t2. The values you pick from t1 may have no match in t2. Using a hash, you have to read all the rows from t1. Then join to t2, stopping as soon as you have five rows in the results.
52 * ln 52 ~ 205 isn't it? :) In any case, the exact numbers don't matter too much here - they will vary depending on the algorithm used. It's more the relative size of operations needed for each join type.
Very nice Chris. I’ve watched some other video in the past from Connor, but this one is much more elaborated. I was wondering if you could explain in one video of this series about bloom filters. Thank you again for doing this. Excellent work.
Finest video on this topic. How is it possible that it was hidden for so long? Just a suggestion, I think you should include NESTED Loops, Merge join, Hash join in the description so more people searching on youtube will come across this. include more hashtags and all. Thanks for the video, if possible please explain REGEX.
Thanks - these terms are already in the description though; what exactly are you suggesting I do differently? REGEX is a big topic! Maybe I'll cover it one day ;)
Excellent explanation for something that took me years to learn and I even bought some books to try to understand this concepts. Still have questions in my mind like - what is considered a big table ( how many rows - at the end normally the answer is -> It depends). Also how many rows are a few rows to return base on the total numbers of rows in a table. Also explain this with two tables is kind of Ok, now in real life RL when there are 3,4, ... tables - I know at the end is always join two tables but when the query is complex is very tiring to try to figure out what is the best join between table t1 and t3 and now t1 and t4 etc.. - Any way a Big thank you again.
Yeah "big table" doesn't really have a fixed definition! Remember that when deciding join order & method it's not the total number of rows in the table that matters. It's how many rows the optimizer expects to fetch from that table. In general the optimizer tries to start by joining the two tables that you fetch the fewest rows from. Then adding the tables with more rows & finish by joining the table that returns the most. This is because starting with the smallest data set and adding to it is more efficient than starting with the biggest data set. When the optimizer gets the "wrong" join order & method, it's often because the number of rows it estimates is significantly different (an order of magnitude or more) to the actual number it processes.
Thank you for this clear explanation! Subscribed! One question: if both tables are indexed and we use a Merge Join, you said that it would still sort the second table. Could you elaborate on the reason behind it? Excellent content!
Thanks bro, It's really nice and clear explaination. However, I still have 1 question. Why do we need to run "Hash Function" in the whole table? Why don't we hash 5 cards and do the JOIN?
8:15 I don't understand this case. If we need to join only five cards to other deck then why hash join have got to go through every single card in first deck?? Is it not equivalent to "first table(5 cards except first deck) join second table (second deck)"? Then we just need to make hash table with 5 hashes
Thanks to yt and you for recommending and giving a beautiful explanation on this topic ❤️ choosing cards was best....was unable to understand from theory session from various sources
when we compare the complexity we only compared based on the number of matches however the hash join also needs to build hash table, how can we estimate the cost of that to compare with other two.
nice explanation, but stuck with a doubt, so if we have indexes, won't it be 52 iterations to find match in other table which is lesser than compared to hash join having 104 hits, plz explain.
You mean for nested loops? To get all the rows in both tables? Both nested loops and hash joins will read all the data in both tables. A (covering) index means the nested loop only needs to read the joined row on the inner table. But it also needs to read the index too! A hash join full scans both tables, reading each row once. The nested loop could full scan the outer table and use an index to lookup in the inner table. So while it reads the same number of rows, it's still more work. This is because it has to read the index too. It's also faster to full scan all the rows rather than read them one-by-one via an index.
Hello At 8:15, you said that even if we only wanted the join for 5 cards in the first deck, we still had to go through the entire deck I don't really understand why Can't we just put the 5 cards in the hash table ?
The problem is you don't know *which* five cards from the first deck will join to cards in the second deck - if any! In general there is NOT a one-to-one match like with decks of cards. Say the second deck only has aces in; there's no guarantee any of the first five cards you draw from the first deck will be aces. So you have to build the hash table from all the cards in the first deck before you start reading the second deck. Does this help?
HI please one more favour. need post on tuning complex query having multiple joins with example to see changes before tuning and after tuning. some of the tuning changes or tips and how they affect the query and make quick access or give response
I recently ran an Ask TOM Office Hours session going through some real world tuning examples - see the recording at ruclips.net/video/ewJamNU6IY0/видео.html
I'm an Oracle DBA for 30 years and your explanation is the best I ever seen !!! Congrats
You're welcome Peter - glad you found this useful!
bro, not exaggerating, your way of teaching is legendary, keep making videos on different topics and ur views will reach the top.
Hi, I am really surprised you got so less views. You have explained something I took years to understand. Subscribed.
Thanks Kapil, glad this helped you understand :)
Exactly your videos are highly underrated on youtube
Very much agree your explanation was spot on ...and solid too..
I feel so lucky to have come across this video on my second day of SQL home study. Looks like I saved myself years of mystification/confusion!
You're welcome, glad this helped!
Great explanation! A word of note for those who were also not understanding (like me) due to the suit system ; The suit strength goes clubs < diamonds < hearts < spades
I just found out that apparently that there's two systems of suit strength :
clubs < diamonds < hearts < spades (alphabetical)
and
diamonds < clubs < hearts < spades (alternating)
I know the latter. I didn't know the alphabetical order even existed.
I was just ordering alphabetically for this video! Thanks for digging out the other suit sorting method
Great vidéo!
My sum up:
Definitions:
[from other source]The Optimiser will decide which table will be the _inner_ or _outer_ table.
- The outer table is the source of rows to match against the inner table. It is usually read from disk.
- The inner table is the table that is probed for matches. It is usually held in memory, is usually the source table for hashing, and if possible, is the smallest table of the two being joined.
Nested Loops:
TL;DR : good if you only have a small subset of rows to join from the outer table, AND you have an index on the inner table.
For each row in the outer table, it will look for all the row in the inner table. Without any index, you get a complexity of o(number of rows in Table1 * number of rows in Table2).
This is very inefficient, unless you only have a small number of rows from the outer table to join (or a small subset of the outer table to join), AND you have an index on the joining column of the inner table. Thanks to the index of the inner table, the nested loop (that looks for the matching row in the inner table) will be fast. And as there are few rows triggering the nested loop, the whole operation will be pretty Note that if you also have an index for the outer table, finding the subset of rows that we want to join is even faster; but this is optional.
If the number of rows to join from the outer table is small, but not that small, the Optimiser may have a hard time deciding whether to use a Nested Loop or Hash Join, so having up to date statistics is important.
Merge Join:
TL;DR: good even if you have lots of rows to join, but requires an index on the outer table to be efficient.
Both tables are first sorted by the joining column (¿sorted in memory?), and then joined. The sorting allows to read the the inner table bit by bit : the RDMS reeds the 1st row from the sorted outer table, and then looks in the inner table for matching rows. When the next row no longer matches, it means that there won’t be any other matching row, as the table is sorted. This allows to only have to read each table once. Merge joins are thus efficient even if many rows have to be joined. But the sorting operation is expensive… Sorting both tables means a complexity roughly of o(#Table1 * Log #Table1 + #Table2 * Log #Table2)
However, if you have an index for the outer table (on the joining column), then only the inner table will have to be sorted. Indeed, indexes are sorted, so the RDBMS will use the outer table index to read it in a sorted manner. Sadly, even if you have an index for the inner table, Oracle DB will still have to sort it [10:28].
Hash Join:
TL;DR: works only for equality joins (e.g. not for “joincolumTable1 > joincolumnTable2”). Good for large amount of rows to join. It is the most efficient most of the time. Complexity is o(number of rows in Table1 + number of rows in Table2)
A hash table of table1 is computed in memory, and then the value of each of table2 is hashed, and an equality of hash value is looked for in the hash table.
Been doing MSSQL for 20 years and this was by far the best explanation of these joins. Thanks!
You're welcome!
I am incredibly grateful to Chris for their invaluable tutorials on hash join, merge join, and nested loop. These concepts always seemed daunting to me when I encountered them in execution plans, but their clear and concise explanations have helped me gain a much deeper understanding. Their expertise and dedication to educating others are truly commendable. A big thank you for demystifying these complex topics and making them accessible to all!
You're welcome
1.Nested Join - nested for loop - O(N * N)
- easiest to implement
- time consuming for large dataset
- better with either small datasets or index on join attributes
2.Merge Join - sort them first and compare - O(NlogN) + O(NlogN)
- Efficient for large datasets
- pre-join preparation required (sorting)
- scanning of relation happens once while joining
- can leverage indexes if available to make it faster
3.Hash Join - using hashing - not good for range queries
- creating a hashtable for the query you are searching ( user_id is the key)
- used for equi joins
- efficient for large datasets
- requires additional memory
- pre join preparation required -> hash table construction
- Hash function should distribute data evenly
SQL Engine : Take a look at data , look statistics across all table using cardinality
Nice summary. Though - at least in Oracle Database - hash join is worse than "not good" for range queries. The optimizer won't use it at all!
Woah this is good stuff. Easily one of the best explaination I seen in a long time. Clear and Concise. The anim also nicely done. Totally can visualise and relate in split seconds. Kudos!
You're welcome, glad you found this useful :)
This is a core question of DBA Interviews. Very well explained. Heartiest thanks Sir
You're welcome; glad this helps!
Although it should technically be a developer's question :)
Thank you very much! This is hands-down the easiest and most concise explanations I've seen!
This is an amazing level of detail. I'm so glad I found your channel. It is priceless. Thank you so much for sharing all this knowledge with us.
You're welcome, great to hear you find this useful :)
Thanks. Explained visually nested loops in 90 seconds. Appreciate your method with cards. Thank you.
You're welcome; glad you appreciate it!
Probalby the best explanation on this I have ever seen
How you can explain complex matter and in the entertaining way ?! Congratulations ! You nailed it !
You're welcome, glad you found these useful and enjoyable :)
This video deserves more views
Thanks, glad you found it useful :)
Excellent Explanation. I don't think even those who wrote the optimizer's algorithms could have explained it this lucid and simple.
Thanks! Glad you found this informative :)
this is super good. just for my understanding, you said when we just have few card (5) from outer deck to match nested loop becomes faster as it can start matching from the first card however, hash join still needs to create hash table for all 52 cards from outer table. why should it create hash table for 52 instead of 5, assuming the filter is already applied and it knows those 5 rows already.
Thanks
Perhaps I wasn't clear on this - I was thinking of a Top-N, "get the first 5 rows then stop" query. Instead of a where clause that only matches 5 rows, the result set (could) be bigger. But we'll stop as soon as we've returned 5 rows.
Because nested loops join rows immediately, it can stop as soon as it reads 5 rows from the outer table (assuming they all join to a row in the inner table). A hash join always builds the hash table on the whole outer table first.
If the where clause identifies 5 rows from the outer table, you're right the hash join will only build on these 5 rows.
It was unclear for me too, thanks for the explanation.
The best explanation that I have ever seen.
This channel is pure gold. Thx a ton.
Thanks :)
I got addicted to see deeper and deeper in SQL now Thanks for the such nice explanation
i always confused in this, But after your card explaction i understand way u explained. Subscribed
You're welcome!
Excellent explaination Chris Saxon
that was super easy to understand such complicated concepts and with good English for all people. Thank you
You're welcome, thanks!
Excellent explanation of join strategies. And your use of decks of cards helps to visualize each strategy in your head for a better understanding of each one. Thank you.
You're welcome, glad you found this useful :)
Great explanation and the example with the deck of cards is brilliant
First time came across such good explanation of these 3 kind of joins. Kudos !!!
This is amazing how you explain and make it simple. Thank you very much!
this video helps me to understand the joining strategies as well helps to make sense & reason out the usage of some of the complex concepts used in Apache Spark, fortunate to find this video, fantastic explanation, very easy to understand the concepts that felt really confusing, thank you very much :)
You're welcome! Glad you found this useful
Perfect video! You can explain things that everyone can understand it!
Thank you for a clear explanation! But I am still wondering why the merge sort needs to start on the previous value if we know it was already joined with the last value from the outer deck. Is there something I have missed? Thanks :)
There could be many rows with the same value. Going back to the previous value is simple way to ensure you always capture them all. It also works for greater/less than comparisons - if the join is C1 > C2 then you'll be on the last row/value in the inner deck after the first row from the outer. But likely need to revisit most of the rows in the inner deck when you go to the second in the outer.
This is really magic , I could understand all of it with an engaging interest. Thanks for sharing.
Great to hear :)
It's truely magical. Thank you
I don't think anyone can explain better than this.
This might be the first comment that I have ever make in youtube :) I am already familiar to join types but watching this was quite nice. you explained it so simple and I love it Chris 👍👍👍. I would like to add this video to my blog also if it is okay for you. Nice moustache by the way :)
Cool, glad you enjoyed this
Such a good video!! Finally making Joins make sense for me!
Glad this helped :)
this is freaking awesome i learnt something in depth one of the best channel i have seen so far
i think using hints we can command the optimizer we want this join type while doing certain query using pinned explain plan
Thanks!
You have to be careful using hints. To ensure the optimizer picks a particular plan, typically you need LOTS of hints to ensure it does this. When using Oracle Database we recommend you use SQL Plan Management (SQL profiles & baselines) to manage plans instead.
your explanation is beyond amazing bravo
Thanks; glad you found this useful!
This is one the bests videos about this subject. Thank you a lot!!!
Thanks; glad it helped
The most vivid explatnion about joins., easy to understand👍👍 thanks
Best explanation! Thank you sir!
Thank you so much. You make my day. Best explanation ever.
Great video. Thanks.
But I didn't understand the reason why the example of joining only five cards in the outer deck shows the disadvantage of hash join (from 8:17 to 8:25). I mean, why can't we simply apply the hash function to those five values? That only costs five operations to construct the hash memory structure?
How do you know what the top five values are before doing the join?
If you want to (inner) join t1 to t2 and get the first five joined rows there's no general way to pick five from t1 that will join to t2. The values you pick from t1 may have no match in t2.
Using a hash, you have to read all the rows from t1. Then join to t2, stopping as soon as you have five rows in the results.
@@TheMagicofSQL Thanks for the clarification! I took the five cards as already known.😅
very informative, easily and affectively explains the three join strategies
Great, glad you found this informative :)
Absolutely Loved the way you explained. More power to you. Subscribed. :)
Fantastic explanation. You made it so easy to understand . Thanks
Thanks bro , Excellent Explanation
excellent video
Very well put 👏
@6:20 - 52*log52 equals 296.4 because the base is 2 not 10.
52 * ln 52 ~ 205 isn't it? :)
In any case, the exact numbers don't matter too much here - they will vary depending on the algorithm used. It's more the relative size of operations needed for each join type.
@@TheMagicofSQL thanks for the reply!
Understood! That's why appreciation was in first place :)
Wow, Easy to understand with a detailed explanation. Thank you
You're welcome, thanks!
Very nice Chris. I’ve watched some other video in the past from Connor, but this one is much more elaborated. I was wondering if you could explain in one video of this series about bloom filters. Thank you again for doing this. Excellent work.
Thanks Franky! I've added bloom filters to my list of things to create videos about ;)
Thank you so much for this video. It really helped me for my interview.
You're welcome; glad this helped you
Very Good explanation
awesome, great and terrefic explanation. so underrated !
Thanks!
Finest video on this topic. How is it possible that it was hidden for so long? Just a suggestion, I think you should include NESTED Loops, Merge join, Hash join in the description so more people searching on youtube will come across this. include more hashtags and all. Thanks for the video, if possible please explain REGEX.
Thanks - these terms are already in the description though; what exactly are you suggesting I do differently?
REGEX is a big topic! Maybe I'll cover it one day ;)
@@TheMagicofSQL sry I wrote description, I meant video title.
Perfect explanation👏🏻
Thanks!
very simple explaining..good work bro
Absolutely incredible Big Cheer to this guy
This is GOLD ❤❤❤❤
this video shd be viral among all advance database students
Then please share it :)
such clear explanation, thank you sir
such a pleasure to watch, thanks for the video
You're welcome, glad you enjoyed it!
This was really good, thank you.
Best explanation for joins ever 👍👍
Your content (and presentation) is excellent, thank you for your work!
You're welcome; glad you found this useful
Nicely explained!
Really wonderful!
Very well explained !
You have got less likes .. your video deserves lot of likes and appreciation as Its really outstanding👌🏼
You're welcome, glad you enjoyed this - please share it with anyone you think would benefit!
Thanks very much for the detailed study.
Great explanation! Thank You
Excellent explanation for something that took me years to learn and I even bought some books to try to understand this concepts. Still have questions in my mind like - what is considered a big table ( how many rows - at the end normally the answer is -> It depends). Also how many rows are a few rows to return base on the total numbers of rows in a table. Also explain this with two tables is kind of Ok, now in real life RL when there are 3,4, ... tables - I know at the end is always join two tables but when the query is complex is very tiring to try to figure out what is the best join between table t1 and t3 and now t1 and t4 etc.. - Any way a Big thank you again.
Yeah "big table" doesn't really have a fixed definition!
Remember that when deciding join order & method it's not the total number of rows in the table that matters. It's how many rows the optimizer expects to fetch from that table.
In general the optimizer tries to start by joining the two tables that you fetch the fewest rows from. Then adding the tables with more rows & finish by joining the table that returns the most. This is because starting with the smallest data set and adding to it is more efficient than starting with the biggest data set.
When the optimizer gets the "wrong" join order & method, it's often because the number of rows it estimates is significantly different (an order of magnitude or more) to the actual number it processes.
@@TheMagicofSQL Wow Chris. I really appreciate that you had take the time to answer my comment. Thanks again.
You're welcome!
This is really outstanding session.
Thank you for this clear explanation! Subscribed! One question: if both tables are indexed and we use a Merge Join, you said that it would still sort the second table. Could you elaborate on the reason behind it? Excellent content!
"It just does!" Sorry, I don't know the exact reason why Oracle Database always sorts the second table. It may be that this changes in the future.
Thanks bro,
It's really nice and clear explaination. However, I still have 1 question. Why do we need to run "Hash Function" in the whole table? Why don't we hash 5 cards and do the JOIN?
See my response to ArthurMontgomery-d2p - how do you know which 5 rows to pick from the first table?
8:15 I don't understand this case. If we need to join only five cards to other deck then why hash join have got to go through every single card in first deck?? Is it not equivalent to "first table(5 cards except first deck) join second table (second deck)"? Then we just need to make hash table with 5 hashes
Oh, I found the answer below
Great video, thank you.
Thanks to yt and you for recommending and giving a beautiful explanation on this topic ❤️ choosing cards was best....was unable to understand from theory session from various sources
Thanks and welcome
Awesome way of explaining the joins 👍.
You're welcome, thanks!
Awesome Explanation.
I said "Yaaar Kamaaal" when watching this video.
Which mean " Dudeee, Terriffic"
:) Great to hear!
This was a very good explanation. Thanks!
Great explanation! Thank you so much for sharing your knowledge.
wow this is awesome! Thanks for sharing this video.
Absolute gem!!
when we compare the complexity we only compared based on the number of matches however the hash join also needs to build hash table, how can we estimate the cost of that to compare with other two.
Saved my a**. Really nice video.
Great learning, Thank you.
You're welcome!
nice explanation, but stuck with a doubt, so if we have indexes, won't it be 52 iterations to find match in other table which is lesser than compared to hash join having 104 hits, plz explain.
You mean for nested loops? To get all the rows in both tables?
Both nested loops and hash joins will read all the data in both tables. A (covering) index means the nested loop only needs to read the joined row on the inner table. But it also needs to read the index too!
A hash join full scans both tables, reading each row once.
The nested loop could full scan the outer table and use an index to lookup in the inner table. So while it reads the same number of rows, it's still more work. This is because it has to read the index too. It's also faster to full scan all the rows rather than read them one-by-one via an index.
Thank you for well explained. Excellent!
You're welcome, thanks!
Hello
At 8:15, you said that even if we only wanted the join for 5 cards in the first deck, we still had to go through the entire deck
I don't really understand why
Can't we just put the 5 cards in the hash table ?
The problem is you don't know *which* five cards from the first deck will join to cards in the second deck - if any! In general there is NOT a one-to-one match like with decks of cards.
Say the second deck only has aces in; there's no guarantee any of the first five cards you draw from the first deck will be aces. So you have to build the hash table from all the cards in the first deck before you start reading the second deck.
Does this help?
@@TheMagicofSQL Yes thanks
I was thinking of a left join, but in that case, the 5 card set would be on the probing side i guess
It doesn't really matter whether it's an inner or outer; a hash join processes all the rows in the first table before accessing the second
I have a question, when you say to sort the values of the two decks, you mean by creating a clustered index on each bucket or am i missing something?
No - just that the database has to order all the rows from each table on the join columns. Like when you add an ORDER BY to a SELECT statement.
I'm lucky to found this treasure.
HI please one more favour. need post on tuning complex query having multiple joins with example to see changes before tuning and after tuning. some of the tuning changes or tips and how they affect the query and make quick access or give response
I recently ran an Ask TOM Office Hours session going through some real world tuning examples - see the recording at ruclips.net/video/ewJamNU6IY0/видео.html
@@TheMagicofSQL Thank you 😊
this was magic thank you
Great vid 😀
Great video
Very nice video 👍
Thank you so much.
You're welcome!
Awesome video! Love it!!!
Thanks a lot for your awesome videos!
you are very good teacher !!!!!
Thanks :)