 Hey guys welcome to SSUnitak so you will decide and this is continuation of ADF tutorial. So today we are going to see about the joint transformation. To use the joint transformation to combine data from two sources or streams in mapping data flow, the output stream will include all the columns from both the sources matching based on the joint condition. So the joint transformation is very similar to the joints inside the SQL server. And joint types are mainly five different types that you can see here. The first which is the inner joint, second is the left outer joint, third is the right joint, then the full outer joint, then the custom cross joint. So these five types of joints are available inside the Azure Data Factory. The same thing as we have already seen inside the SQL server. We are having the inner joint, left outer joint, right outer joint, full outer joint and cross joint. So in case of the inner joint whatever the data sets that we are having inside the left and inside the right. So all the matching data sets from the left and the right side will be inside the inner joint. So this part will be returning in case of the inner joint. In case of the left outer joint it will return all the data from the left table and matching data from the right table. So in this case it will be returning whole from this. In case of the right outer join it will be returning only from the right side table all the data and matching data from the left side table, so this will be returning. In case of full outer join it will return all the data from the left table all the data from the right table and matching and non-matching data all will be returning. After that the custom cross join. So this will be like cross join if your left table is having 4 rows and your right table up is also having 4 rows so the output that will be 16 rows. So this is the joints inside the SQL server the same we can say inside the SEO data factory. So now go to the browser and we will try to implement this in practical. So here we are having these two sources one is the payroll file and second is the employee file in case of the payroll file we can see the employee ID is like 1, 2, 3 and 5 and when we can go inside the employee data file so this employee data file will have the data for employee 1, 2, 3 and 4. So 4 is not available in the payroll and 5 is not available in the employee so that we can see. Now we can go inside the SEO data factory here let me try to add a new data flow and let me quickly call this data flow as join transformation. Now here we can add the source so as I told you it is required to have two input sources or streams. So in our case we have two files inside the SEO blog stories so we can click on the add source and here we have already created the data set for employee file. So we can use the same as we can go here and inside the data set we can see this employee file. So this we can select and inside the projection we can see all these four columns. Now we can go inside the data preview and we will try to look inside the data preview. So it should be returning four rows with employee ID 1, 2, 3 and 4 so that we can see here. Now we are required to add one more data source. So this data source is mainly focused on payroll file. So we did not created the data set for the payroll file so I am going to use the inline query. So inline query we have seen in the last video of this video series. So we can go here and this is for the delimited file so we can select this delimited text. Then the link service so SSU testing is the link service so we can select that. Now we can go in the source option and under the source option we can find the file path. So we can browse and here we can go in the input and after that we can select this payroll one. Click on ok. Go to the projection and we can import the schema. If you are not going to import the schema so this inline query will not be going to say how many columns it has. So here as we can see it has four columns but the column names is not correct. So we can go in the source option again and here we can see like first row as header. So we can select it and go to the projection and try to import the schema again. Now we can wait. So that's it. We can go in the data preview and here let me try to refresh. So it should have four rows here with the employee ID 1 2 3 and 5. So that we can see. Now we have done with the source side. Now we are required to add the join. So here the first one under this multiple inputs and outputs. So we can see this join. So it is taking two input and providing one output only. So let me try to click on the join and under this join we can check the properties. So the stream name that we can see join one that is ok. Live as it is. Left stream that is the source one that we can see. What will be the right stream? So right stream will be the source two that we have created for the payroll data. So let me try to select from this drop down this right stream. Now here we can see the join types. So the join type that is the inner join by default then left outer join right outer join full outer join then the custom cross join. So in this video we are going to see about full outer join inner join left outer join and right join. We will cover the cross join in later of this video series. So let me go in the inner join and here we can see the join condition. So we have to specify the join conditions here. So let me click on this drop down. So we just want to match with the employee ID from the source one and from the source two. So we have selected as we can see now here it is reflecting an error because you can see this is in red. This is because as we can see the data type of this source one stream that is ABC. It means this is text and this is something like sort. So the data type is not matching. So what we can do on that case go to the source again and this is the sort. This is not the text actual. So we can go in the source option. Sorry inside the projection and inside the projection we can change this as sort. Now we can go on the join and this red symbol has gone. So this is not good. Now here we can see the plus sign. So once we click on the plus sign then it will be adding another column. So if your condition is based on multiple columns like the employee ID and then we want to match with the employee name and the combination of multiple columns will be making the on condition. So on that scenario we can click on this plus and we can add the other columns here. So in our case the employee ID is the only one. So let me delete this. Now we can go in the optimize. So under the optimize we can see the broadcast and the partition option. So I am not going to touch this by default leave as it is. Now we can go inside the data preview and try to refresh this. So this time it should be going to return the all the columns from the left table and the right table. So that will be the source one and source two. So that we can see. So as we have used the inner join so only matching data which is the employee ID 1, 2 and 3. So that is why it is returning only these three row and all the columns from the left table and from the right table. Now we can go inside the join setting and try to change this from inner join to left outer join. And now go to the data preview and try to refresh it again. So this time it will be returning four rows with employee ID 1, 2, 3 and 4 and inside 4 the employee ID is not available inside the right stream. So that is why we can see this is null and the payroll month, salary and text. So everything is null. Whatever the data that is available in the left table is getting from here. Now here go back to the join setting again and try to make this as right outer join and go to the data preview and try to refresh. Here we can see like four rows it is returning with the employee ID 1, 2 and 3 which is the matching one and employee ID 5 which is available in the right stream. So that is not in the left so that is why we can see null values in the left table columns. Now go to the join setting and let me try to make this as four outer join. Go to the data preview and try to refresh it. So this time it will return five rows with the employee ID 1, 2, 3, 4 and 5 and in case of the employee ID 4 the right table will not have the employee 4 there so the columns from the right table will have null and in case of employee ID 5 that we can see left table did not have the employee ID 5 so the column values will be null for those. So this is the join types that we have seen. Now here we can use fuzzy matching option. So fuzzy matching is going to join between two string type of columns. So here we don't have any string type of column so don't worry for now we will be going to cover this use fuzzy matching option while covering this cross join. So after doing this we want to load this inside the destination. So we can click on this plus symbol and here we can see the option for the sync. So we can click on this sync and we can add. So this will have total seven columns that we couldn't see. So thank you so much for watching this video. If you really like this video please subscribe our channel to get many more videos see you again in the next video.