 Hello everyone. Today we will see nested loop join in query processing. So, learning outcome for this session is students will be able to analyze the cost of join operation using nested loop join algorithm. Now, whenever we want to perform a join of two relations which algorithm will be used and what will be its cost that we have to see. So, today we will see nested loop join. So, what do we mean by nested loop join is that we are having two relations r and s. So, one will be the outer relation and one will be the inner relation. So, here we are considering that r is an outer relation and s is an inner relation because for every tuple of r we are comparing every tuple of s or when we are comparing the tuples of r and s. So, first tuple one tuple from relation r will be taken to the main memory and then all the relations or all the tuples of the relation s will be brought to main memory and compared and that is why the algorithm is for each tuple tr in r and for each tuple ts means that first tuple from r will be taken and then it will be compared with every tuple of s. So, test the pair to whether they are satisfying the condition or not and if they are satisfying the condition that concatenation of these two tuples will be added to the result. So, this is what nested loop join is. Now, we will see this in detail with the example. So, consider we have to perform employee and department join. So, here employee consider as an outer relation and department you consider it as an inner relation. So, how it will work? So, take the first tuple of employee and check the values of common attributes with all the tuples of this department relation. So, first record will be taken and then it will be compared with the first tuple. So, this department ID 0 1 is matching with department ID 0 1 that is why these tuples will be concatenated and will be added to the result. Then this first record will be compared with second, then the first record will compare with third one, then the same record with the fourth one and then the first record with the fifth one. So, all the five records will be compared with the first tuple. So, once this will be finished the complete relation then the second tuple will be brought to main memory and then again this tuple will be compared with all the records of this department. So, in this way every tuple will be compared with the complete relation that is department and this is how nested loop join works. So, this nested loop join does not require any index because we are going sequentially from the first tuple to the last tuple. This is very expensive because as we have seen it will examine every tuple in this two relation. So, consider n r r the tuples in relation r and n s r the tuples in relation s then total number of comparisons will be of course, n r into n s or in the previous example we can see that here there are nine records and here there are five records. So, total comparisons will be 9 into 5 that is 45. So, here complete scan of s will be done for every record of r. Now, we will check the performance of this nested loop join. So, assume that a buffer can hold only one block of relation. So, how many block transfers will be required? So, we have we will be requiring b r r the blocks of the relation r and b s r the blocks of relation s then all the blocks of relation r will be brought to memory only once that is why b r block transfers are required for relation r. But how many block transfers will be required for s? So, for every tuple of relation r the complete relation s is going to be scanned. So, all the blocks of relation r sorry s will be brought to main memory and that is why n r into b s block transfers will be required for relation s. So, total number of block transfers will be n r into b s plus b r this is worst case possibility. Then how many 6 will be required? So, only one seek for every relation s because s will be read sequentially. So, we are taking the first block then second block and third block. So, for one read it will be one seek and we are reading relation s n r times as many tuples in r that is why we will be requiring n r seek operations for relation s. Whereas, for this b r we are bringing the blocks one by one and that is why we will be requiring b r seek operations for relation r. So, total number of seek operations will be n r plus b r for nestled group join in worst case. So, what can be the best case? So, if there is a in-hub phase to hold the relations into the main memory then we will be requiring the reading of blocks only once and that is why the total number of blocks will block transfers will be b r plus b s along with 2 6 because it will be read sequentially again. So, first pointer will be placed at the first block or the first tuple of the first block and then it will be read sequentially that is why we will be taking we will be requiring b r plus b s block transfers. So, one can be the possibility that one of the relation is fitting in main memory. So, if it is that case then it is better to have the inner relation kept it in main memory because it will be read only once and then we can make the we can do the comparison of this relation r and s. So, in this case also the block transfers required will be b r plus b s along with 2 6. Now, let us look at the example here. So, consider there are two relations one is student and other one is text the number of blocks in students are 5000 sorry number of records are 5000 and number of blocks are 100 whereas, in text relation the number of records are 10,000 and the blocks are 400. So, consider student as an outer relation and the text as an inner relation ok. So, number of total comparisons will be of course 5000 into 10000. So, let us look at this. So, this is a relation student and this is a relation text. So, what we will be doing is that first block will be brought to memory and then first block of s will be also brought to main memory and then the from the first block the first tuple will be taken and which will be compared with all the records of this particular block. So, let us look at this here. So, first tuple will be compared with all the tuples from the first block. Then the once this block will be finished next block will be brought to memory then the third block and fourth block it means that for the first tuple all the tuples from the relation s will be compared and once this is done the second record or the second tuple will be brought to memory again this will be compared with all the tuples from all the blocks that is why. So, for one record we are taking all the blocks means 400 blocks of relation s and then there are total 5000 records and that is why the block transfers will be 5000 into 400 for relation s and 100 blocks for relation r. So, total number of block transfers will be around 20 lakhs 100 and the number of 6 that will be so, this will be rate relation 5000 times 1 per tuple of relation r and that is why the number of c corporations will be 5000 for relation s and 100 for relation r. So, total number of 6 will be 5100. So, in best case what we have seen is that all the blocks will be read only once and that is why if both the relations can fit in main memory then the total number of block transfers will be 500 whereas c corporations will be only 2. Now, if we will reverse it that text will be the outer relation and students will be the inner relation then pause the video and try to find out the number of block transfers and c corporations. So, for outer relation for the tuple of outer relation the inner relation will be read completely. So, I hope you have got the same answer what we have been written. So, here the first block or first tuple will be taken from relation text and that will be compared with all the blocks of relation student. So, there are total 100 blocks in the relation student that is why for one tuple there will be 100 block transfers. For the second tuple there will be 100 block transfers in the same manner 10000 tuple there will be 100 block transfers and therefore total number of block transfers will be 10000 into 100 for relation s plus 400 for relation r that is nothing but text relation and the number of block transfers if we can see it has been reduced to 10,400 over there and but the number of 6 are increased because the outer relation is larger than the inner relation and that is where 6 has become now 10,400 as compared to 5,100 and as we know that the sick operation is more expensive than block transfer. So, accordingly we have to see that which relation should be used as an inner relation and which relation should be used as an outer relation. So, variation in the nested loop join is block nested loop join where the comparison will be done block wise. So, every pair of the block from relation r and s will be brought to memory and will be compared and within that block then the comparison will be tuple wise this will say major block accesses. So, in the next lecture we will see block nested loop join. Thank you.