most asked scenario based sql Interview question & answer | no of Records of 5 types of joins

hello welcome to SS unitex social decide so today we are going to see a very common SQL interview question and answer so more than 90 of the interviews interviewer is asking this question or this type of the question I am taking guarantee if you are watching this video without skipping then you should be able to answer this question and this type of the questions in the interview without failing here let's try to understand the question first so we are having two tables like table A1 and table two table one is having only one column that is ID and table 2 also having one column that is ID so here it is having id1 three times ID 2 2 times then three null in case of table 2 ID 1 2 times ID 2 3 times then 4 null while we are going to do the inner join left outer join right outer join full outer join and cross join what will be the count of the rows so this type of question will be asking very often let me quickly go inside the Excel and we'll try to understand how we can know how many rows it will be returning so here as we can see we are having table 1 and table 2 and the same record that we have seen inside the slide so while we are going to do the join what you have to do you have to pick the first row from the left table and we'll try to match with all the rows from the table two so one will be going to match with the one then one so first we'll be going to match with two times so I am going to write 2 here then we need to pick again second row from here so that we can see one so this one will be going to match with these two and it will again return to next we can see the third one it will again return 2. so this is simply matching each row with all the rows from the table two in case of the two it will be going to match with the three rows as we can see so that's why we can write as three rows in case of the sixth row that is again two it will be going to match with the three times so here we can write as three then we can see 3 will not going to match with anyone and null will not be going to match with anyone because two null values are not same inside the SQL Server so we can just do the sum of all these rows so we can see 12 as in sum so we can say that your inner join will be 12. now in case of the left outer join what you have to do you have to do the same thing that you have done inside the inner join so first we'll be going to match with all the rows so it will return to then two then two then in case of the two it will be going to match three times so we can say three and three now here we can see this 3 is not going to match with anyone and null will not going to match with anyone so in case of these two we can write 1 and 1 because it is having only a single value now we can do the sum of all these so it will be your left join that we can see 14 so 14 will be your left line similarly we can do for the right outer join so right outer join again we can do the same thing we can match with the left well table values one by one with all the values from the right table so it will return to 2 2 3 3 till now we are using inner join now in case of the right out to join now we can see the right side values those has not been matched so 4 is not matched with the left table and null will not be matched with the left table so these two will be having one one so we can see again fourteen so in case of the right join it will 14 now in case of the full outer join we can do the inner join as we have done now we need to write all the values those has not been matched so as we can see 3 and null so like these two values has not matched with the right table so we can write 2 now 4 and null again not match with the left table so it will again 2 then we can do the sum of all these so it will be your full join so we can see 16 so it will be 16. in case of the cross joint we are seeing like it is having total seven rows so 7 multiplied by 7 so that will be your crosswind so that is 49. so by using this approach you will never fail and you should be able to answer this question very easily so let me quickly go inside the ssms and will try to verify what you have seen over here is the same count we are getting or not so here we have the table 1 and table two so these two temp tables I am going to create and inserting the data into this table no records has been inserted and table is created now first we can see inner joint so let me try to execute for the inner join we can see 12 we have also seen inside the axle that is 12. let me go for the left join so in case of the left join it is 14 we have seen 14. similarly we can check for the right outer join so it is again 14 we can see that is 14 then we have 16 in case of the full join so here we can see 16 in case of the full joint and last for the cross join it is 49 that we can see inside the Excel we can verify that so I hope guys you have understand how you can provide the answer to the interviewer thank you so much for watching this video If you like this video please subscribe our Channel and press the Bell icon to get the notification of our newly uploaded videos see you in the next video

Channel: SS UNITECH

Views: 19,598

Keywords: sql, sql interview questions, amazon interview questions, sql multiple joins, top sql interview questions, sql questions, sql answers, sql interview, tcs interview questions, sql questions for freshers, sql interview questions for experienced, important sql queries asked in interview, sql query interview questions, tricky sql queries for interview, sql tutorial, sql server, sql query, interview questions sql, interview question sql join, sql server interview questions

Id: zBdz_E4xJHk

Length: 5min 58sec (358 seconds)

Published: Thu Mar 30 2023