SQL JOINS Interview Question | What does different SQL Joins return?

okay so in this video let's try to answer a very frequently Asked SQL interview problem that is you are given two different tables with some data and you are asked to find the number of records that will be returned by using different type of joints on these two tables that is inner join left join right join and full outer join now the tricky part is that the data that is present in these two tables is having some duplicate values it might have some null values Etc and also you are not allowed to query the data

you just need to look at these two table data and figure out the number of records that will be returned when you do these type of joints now in order to correctly answer this particular question you need to completely understand how internally SQL does different type of joints and that is exactly what I will be explaining in this video and I'm going to explain that by taking up two different data sets so let's start with scenario one as you can see I have been given two different tables table one and table two and I can

see that in table one I have seven records and in table two I have six records now what I need to do is just just by looking at these two tables and the data in these two tables I need to figure out how many records will be returned by each of these joints so inner join left join right join full join and we shall also see what will happen with natural join and cross join now what I'm going to be doing is I'm going to be explaining you how SQL internally does the join and then

we'll try to figure out the number of Records returned by each of this joint and then I am going to execute the query that I have already created basically the tables that I have already created with the same data and we'll see if whatever explanation that I have given if I'm getting the correct output using these queries or not okay so straight away let's start with the first one that is inner join okay now what exactly is an inner joint inner join will always fetch the matching records so in the two tables that I have

I can see that both these tables only have one column okay and I'm going to call these columns maybe let's say an ID column okay that is what I have done in these two tables I have created the tables with the same ID column okay now both tables just have one column so when I'm doing an inner join I can join these two tables only by using these two columns right now I know inner join will return the matching record so how SQL basically starts processing your queries is it's going to take let's say a

left table and each record from the left table it's going to try to join with every record in the right table okay so my left table here is my table one and let's say my right table is a table two so every record from left table is trying going to join with every record from the right table and wherever there is a match it's going to return that record okay so here I can say I'm going to start with the value 1 that is the very first record that is one right so this one SQL

will try to see if this value 1 is present in any of the record in the right table that is in my table two okay and I can see that it's present here as well as it's present here okay so this will basically do a match so it's going to return one record and this will again do a match so this is also going to return one record okay and once it processes these two records then this first record from the left table is going to try to match with the other four records for my

right table but here you can see the value is not one so one will try to match with two it's not going to do a match and again it's going to one is going to match with two again it's not going to do a match same thing will happen with 4 and same thing will happen with null because 1 is not equal to null right so all of these matches is going to return 0 records okay this will return 0 0 and a zero here okay so in total the very first record of my left

table will only join with two records from my right table so the number of Records returned here will be 2 okay and the same thing will happen with this so once SQL processes the very first record of your left table it's then going to move to the second record of your left table and the second record of the left table will then try to match with every other record from the right table okay so in this case this one will again match with this and it will match with this but it will not match with

any of this right because the values are different right and hence this will again return two records and the same thing happen for the third record in the left table okay so this will again fetch two records because this will only match with these two records and it will not match with any of these records now once SQL has processed these three records SQL will then try to process the fourth record in the left table that is 2 right this two will try to match with every other record from the right table so two will

try to match with one it's not going to do a match so this will return Zero Records okay and let me uh let me clear out all of this and then this two will try to match with this one here this will again return Zero Records but this two will match with these two in the right table so this will return one record and this two will again match with this two value here and this will again return one record okay but two will not match with four and null so this will return 0 and

a zero okay so I can say that this two present in my left table will match with two records from my right table so this will again return two records okay once these four records are processed the next thing is to process these three records and you can see here the value is 3 and this 3 is not present anywhere in my right table so it will not match with any of the record in my right table so this will basically return me Zero Records and the same thing will happen with this three that is

the sixth record in my left table this will also return zero records and this will also return Zero Records so what I can say is by doing the inner join only these many records will get returned because only these will get matched with the right table records here right so this is 2 plus 2 plus 2 plus 2 that is eight I hope this is clear now let's try to verify this by executing the query for the tables that I've already created the same table with the same data you can see here I have created

table 1 with the seven records here and the table two with the six records exactly the same data as I have shown here okay now when I do a inner join here now you can see that it is fetching me eight records and you can see that the only records that are matching are one and two because these are the only two records or values that is present in both these tables and inner join will only fetch matching records okay so I hope this is clear and this is what is the output of inner join

now let's try to move on to the next joint that is left join now when it comes to left join I had previously made a tutorial video on all type of joints and there I had given a formula for how you can figure out the result of a left join right join or full join okay there's a very simple formula that you can remember in order to completely understand how all of these outer join works so we shall try to solve this problem by using the same formula that I had given in one of my

previous videos okay so what I'm going to do is so this is my left join query I'll just move this to the top here and there was a formula that I had given so I say that left join will always return so basically what a left join is it's basically telling SQL to First do a inner join okay so left join is basically first you need to do an inner join okay and then Additionally you need to fetch so additionally or basically I can tell fetch any additional records from left table which is not present

in right table okay so basically this is the formula that you need to remember so left join will always try to do a inner join first that is it's going to fetch all the matching records first and once it fetches all the matching records additionally it is again going back to the left table and it is going to see are there any additional records in the left table which is only present in the left table but not present in the right table if there are then all those records will also get fetched so let's see

in this particular data what do we have so first of all Let Me Clear down all of this so we don't get confused so I told you that left join will first do an inner join so straight away I can tell it will always at least it's going to return eight records okay plus it's going to see are there any additional records in the left table which is not present in the right table so here I can see that this 1 and 2 will join with the records in the right table so I can ignore

this but then I have some records with values three this three is only present in the left table but it is not present in my right table okay and this 3 is present in three different records okay so what I can say is here the inner join will return 2 plus 2 plus 2 plus 2 and this is the additional records in the left table which is not present in the right table so this tree will return one record this tree will again return one record and this three will again return one record okay so

I can just tell a this is 2 plus 2 plus 2 plus 2 is 8 8 plus 3 that is I can say eight plus three this should probably return 11 records okay why this 8 is written from the matching records that is from inner join plus these three is the additional records in the left table which is not present in the right table okay and that is exactly the formula that I have given if you just remember this formula all of this outer joints will become very easy for you to uh answer in the

future okay so now let's try to execute this query and see if we are getting 11 records or not and you can see that I am getting 11 records okay now if you look at the data careful you can see here these eight records are written due to Inner join okay basically these are all the matching records and these last three records are the three additional records which are only present in the left table but not present in the right table how can you say that because you can see that only this ID column here

is basically coming from my left table okay because I put star here so all the columns from the left table will be in the beginning and all the columns on the right table will be at the end here since only one column is present I can say like this so this is coming from my left table it has the value 3 but right table has no matching record so it will return null okay so whenever you are doing a left join all the matching records only present in the left table will have the values that

is present in the left table but since there is no matching record in the right table those columns will return as null this is very important to remember because you can use this to apply certain filters okay so anyways I'm not going to go into all of that but I hope you understand how left join works okay now once you have understood left join the right join is kind of like exact opposite of left joint so what I can say is if I go back to my PG admin here the formula is again exactly the

same as left joint so I can just do one thing I can just copy it here or maybe I'll just write it so right join is equal to basically it will first try to do a inner join it will fetch all the matching records plus additionally it fetches any records from right table which is not present in left table okay so this is the formula exactly opposite as left join okay so it fetches records from right table which is not present in the left table so if I go back to my Excel here you can

see here this will match with all the record basically some of the records in the table too so inner join so this will still hold true right so I'll I'll just remove this so basically what I can say is Right join will again say 8 okay plus okay so this 8 is returned from the inner join plus any additional records from the right table which is not present in the left table so here this one and two is present in both the table so I can ignore that but this 4 is only present in the

right table and this null is also only present in the right table but not present in the left table okay so here I can just tell this will fetch one additional record and this will fetch one additional record okay so I can just tell eight plus two so this should probably return me 10 records okay now let's go back to my PG admin and try to execute this and see if my understanding is right and if I execute this you can see that I am getting 10 records okay so these first eight records are returned

from the inner join that is all the matching records and these two records are the records which is only present in the right table this 4 and null okay don't confuse this null with this null present here this null is the null value that is present here okay that is this particular uh record here okay and this null is the other record that is only present in the right table but not present in the left table okay and why am I getting null here is because there is no matching record for these two values in

the left table hence it's written as null okay so I hope this is clear and I hope you you are clear on the right join okay so this is what my right join will return now let's move on to full join okay now full join the simplest way to Remember full join is also through a formula and let me explain that formula so what I'm going to do is again I'm just going to go here and I'm going to write a formula here so I'm just going to say full join is equal to first of

all it will do a inner join okay it will fetch all the matching records plus you can tell that it will fetch additional records from left table which is not present in right table okay this is one thing okay so I'm just going to move this to the right here now this is exactly what I mentioned when I explained the left join right but additionally in a full join it will kind of do inner join then it will try to do what was written from left join then it will also try to fetch any records

that is present in the right table okay which is not present at the left table so I can just tell fetch additional records from right table which is not present in left left table okay so basically in in simple words what I can tell is full join will return the all the data from inner join left join and right join so so let's try to understand this with this example so what I can say is full join of course inner join will return eight records so eight plus so that because all the matching records here

I know that this is going to return eight matching records okay additionally it is going to go into the left table and see are there any additional records in the left table which is not present in the right table so I know that these are the three records which is only present in the left table so here I can straight up I tell these are the three records which is coming from my left table okay which is not present in the right table and additionally again it will go into the right most table and see

are there any additional records in the right table which is not present in my left table that is these two records right so again it's going to do a plus two and this is going to be the total result returned from full joint so that is 8 plus 3 is 11 11 plus 2 is 13 okay so the total output here will be 13. so now let's try to execute this full join here and if I execute you can see that I am getting these 13 records okay so I hope this full join concept is

clear you just need to remember these formulas I'm going to share all of these scripts this formulas everything in my blog so if you want you can refer to that and try to better understand that okay so I hope this is clear now additionally there are two more joints natural joint and cross join let's try to look at that okay now before I can go into natural join let me try to explain cross join now I know most of you guys will already know what a cross join is what cross join basically does is it

does not bother about what values are present in the left and the right table what it's going to do is for every record in the left table it's going to match with every other record in the right table so in other words when it's going to process when SQL is going to process one record from the left table This Record will match with every other record from the right table even though the values are different it will still try to match it will match okay so this one here will match with one two three four

five six okay so this will match with six record this will match with six record this will match with six records six six six and six okay so this six plus six plus six again this that is I think total forty two okay so it's kind of like it is a Cartesian product so every record from left well will match with every record from the right table so this will return 42 records okay it basically does not bother about the values it just looks at the number of records and it just multiplies okay so this

is what I have mentioned here so if I execute my cross join you can see that I am getting 42 records okay so if I go down you can see I have 42 records okay every record from my left table matches with every other record from my right table okay now I hope this is pretty clear cross join nothing too complex about this but what exactly is a natural joint now natural join is a joint which is not supported in all the rdbms as far as I know in Microsoft SQL Server it is not supported

and maybe there are some other rdbm mess when it is not supported and this is not a recommended joint it's basically not recommended to use this joint but of course if you might be asked about this in interview so I'll try to cover this okay now what exactly a natural joint does is natural join when you are doing a natural joint it's similar to a cross joint you don't need to specify a joint condition you can see here I just mentioned the left table and the right table okay and I put a natural join here

okay but I have not given any on Cross so there is no joint condition okay so how SQL naturally does the join is it does the join based on the column name okay now if I go bank here and if I see here my table one has column ID okay and my table 2 also has the column ID so natural join will just go into both these tables and it will see if both these tables are having any column which is sharing the same name okay in this case both the tables are having one column

each and the column names are same that is ID since the column names are same it will try to do a inner join based on that column okay so when it try to do an inner join it will try to fetch all the matching records so this will fetch 8 records okay since eight records if same column name exist okay so maybe I'll just move this to the right if same column name exist okay or in natural join there is one additional thing so if same column name exist then it will do a inner join

okay that is all the matching records will be written but if there are no matching column names okay so if same column name does not exist okay if same column does not exist then the output will be basically the output of a cross join okay so what happens is if there are same column name exists then it is going to do an inner join but if the column names are not same in both this table there is no matching column names in both these tables then it will do a cross join okay that is it

is going to return the result as 42 okay so let's try to understand that so what I'm going to do is I'm going to move this to the right and I'll just expand this so you can see this okay so let me explain that so if I execute this I am getting eight records why because both these tables are having the column ID the column names are same so it's going to do a join based on the ID column and it's going to return all the matching records that is fine but let's say I change

the column name of one of these tables so I'll say alter table table underscore one and I'll say rename column ID to ID underscore new okay just for example and if I execute this now the column name is different so if I execute table 1 you can see that the table one the column name is ID new okay and in table two the column name is ID so both these tables does not have any common column names right in that case when I run the same natural join query now if I execute this now you

can see that I am getting 42 records okay so I am getting 42 records so why basically since there were no common column names natural join did a cross join okay so that is why it's going to return 42 records okay try to remember this what is the difference in natural joint so what happens when there are same column names in both the tables and what happens when the column names are basically there is no same column name in both the tables okay so I hope all of this is very clear not just the output

but also you understand the background of how SQL is basically doing all of these joints now let me quickly go to the scenario two that is I have another scenario here and this was actually the input table data which was shared to me by many people in the comments I think they faced this exact data in the interviews so let's try to solve this particular problem so I have been given two different tables table three and table four and you can see I have uh four seven records in table three and I think five records

in a table four okay and you can see here I have one one one five times and then I have two records with null and then here I have one one three times then I have a record with the value 2 and a record with value null okay so what will be the output of inner joint so again let's try to understand or solve this from scratch so SQL is going to take very like very first record from the left table it's going to process this record or try to match this record with every other

record from the right table so this one will try to match with every other record here so this one will match with this it will again match with this and it will again match with this so I can say that this is going to match with three records okay and this one will again match with these three records it will not match with these two so again this will written three records okay and the same thing will happen for the one here and the same thing for the one here and the same thing for the

one year okay now when it comes to null this is the tricky part that you need to remember in SQL and I think in every other programming languages two null values are never the same okay basically null is kind of a representation of no value okay and you can never treat two null values to be the same even though this looks like exactly the same they are actually not same for SQL okay so this null will not match with this null so this null will not match with any of these records and it will not

match it any of these records so this will return zero records and this will also return Zero Records so the output of my inner join should probably be 3 plus 3 plus 3 plus this one the I think this is 15 okay so this will return 15 records okay now straight away let's try to see the output of all of this and then we'll try to execute the query in our PG admin to see if my output is and if my understanding is right or wrong okay so what about left joint so left join will

again do a inner joint okay plus additionally it is going to go into the left table and see any additional records in the left table which is not present in the right table okay in this case this one is already matched with some records here so that is fine these two are the only values which did not match with any records here right even though I have a null value here I told you two nulls are not same so I cannot consider these to be a match okay so these are the only two values which

is only present in the left table but not present in the right table so I can just tell 15 plus 2 which is probably equal to 17. now similar to the left join the next thing is the right joint so Eddie again right join will first do a inner joint so that is 15 matching records plus any additional records from the right table which is not present in the left table so here I can say that this 2 is not present in the left table and this null is not present in the left table right

so these two records so again I can just tell 15 plus 2 that it should again return 17 records okay now what about a full join so full join will initially do a inner join okay 15 plus it will go into the left table and see any additional records that is 2 okay plus it will go into the right table and see for any additional record that is again these two records so this will again be plus 2 Okay so this will totally be 19 records okay so let me just move this to the right

okay 15 plus 2 plus 2 19 okay now when it comes to okay before the natural join let's go to cross joint so cross join is basically every record from the left table will match it every other record from the right table so here I have one two three four five six seven seven into five seven into five is 35 so this should be 35 records okay now natural joint since I am treating that both these tables are having the same column names it's going to do an inner join so this should return me 15

records okay if the column names are different then it would return 35 records I hope that is clear okay so this is basically what I have come up with now let's try go to PG admin and try to execute the query so I have created these two tables table three with the seven records and table four with the five records as I have shown here okay now let's try to do each of these join queries one by one and see if my output is right okay so this first time doing inner join and you can

see that I'm getting 15 records okay I hope this is clear you can see here I am getting the 15 records okay so and I think I have mentioned 15 records here okay now let's try to do an left join and see if I'm getting 17 records and you can see that I am getting 17 records so my understanding and explanation is the is correct and that is exactly what I have mentioned here okay now let's try to see uh out right join so when I do right join again you can see that I am

getting 17 records okay so again exactly what I mentioned here and now let's do a full join so I should hopefully get 19 records so if I execute this you can see that I am getting 19 records okay so that is also right now when I do a natural join I'm getting 15 records okay so that is exactly what I mentioned here and finally when I do a cross join so let me do a cross join here and now you can see that I'm getting 35 records okay so basically everything that I mentioned here is

correct and I hope the explanation is pretty clear for all of you now I hope you enjoyed this video if you did let me know in the comments below and if you have any other feedback or any other data set that you would like me to solve definitely share them in the comments below thank you so much for watching and see you soon in the next one bye