Inner Join, Left Join, Right Join and Full Outer Join in SQL Server | SQL Server Joins

In this video we will talk about four important Joins Inner Join Left Join Right Join and Full Outer Join In order to understand these four joins you can see that I have created two tables here One table is called as Country Table it has threee records you can see One for India two for Nepal Four for Srilanka and then I have one more table called as States Table which has states for the respective countries By using this four Joins lets try to understand How they work in terms of these two tables In order to understand These four joins I have created two table here One table is called as country table you can see the country table has three records one for India two for Nepal and four for Srilanka For these countries we have created one more table called as Tbl state Table And this table has a states for those countries Lets start using these joins let see that how they work on these two tables Lets first start with the easiest one That is Inner Join In order to understand Inner Join I have selected both the Tables data and I have displayed them on the same screen The data from both the tables are now displayed You can see the Tbl country has one for India and this India has two records in the state table you can see one it has Maharashtra and again one has Punjab This first two records I have actually corresponds to the country India In the same way you can see Nepal has one more record here which is Kathmandu You can see two for Nepal and again two has a entry of a Kathmandu here you can see for Srilanka we don't have any entries in the state table In the same way If you see California it has no entries to the country table You can see this value is Null here California also does not have any entries of the country If we make a Inner Join Wherever the data is matching in these two tables on those record will be displayed In other words what will be displayed is only India and Nepal As you can see Srilanka does not have matching records neither California So both of these records will be excluded if we use Inner Joins Let me write a Inner Join and let me show you how the data looks like Write a new query here I am going to say select * from tblcountry inner join tblstate Now what is a Join on The Join is made on the country Id You can see country Id column here as well as country column here I am going to say inner join tbl state on tblcountry.country Id = tblSate.countryid and let execute Now you can see he has displayed me India For India he is saying that there is two states one is Maharashtra the other one is Punjab The second one again for Nepal he is saying there is state called as Kathmandu You can seee he has not displayed me Srilanka and California Why? because they don't have matching record in both the tables So Inner Join shows data which has matching entries in both the tables If they don;t have have the matching entry Then those data will not be displayed Next kind of Join which is very important is The Left Join In Left Join all the data of the left table will be displayed irrespective It has a matching entry or it doesn't have matching entry on the other table In other words if we make a left join all the data from the country table will be displayed But only data which is having matching entries of the right table will be displayed In other words If we make a left join all the data from tbl country will be displayed From the States table we will see only the data Maharashtra, Punjab, Kathmandu and not california In other words if I make a Left join here If I execute it you can see that he has displayed all the records From the Tbl country but from this States Table he has only displayed matching records you can see that California is not displayed from the Tbl State Table Its very important to understand what this Left and Right means what do you mean by Left Table When we see here The Left table is it country or is it state How does it decide which is the left table The left or the right table it is just decided depending on the way you have written the query If I make this query a bit flat here tblcountry is lying on the left side and the tbl state is lying on the right side When we say a left join in other words in the left table is nothing but the tbl country The left is it is from the prospective from the prospective of way your SQL is written and how is your table is written in the SQL statement You can see the tbl country lies on the Left side of the SQL statement while the tbl state lies on the right side of the SQL statement Next kind of Join which is very important is The Right Join The right join is exactly a vice versa of the left join In other words if I write a Right Join here If I say execute You can see that from the right table all the records will be displayed irrespective They have a entry in the left table or not He has displayed here California From the left table only those records will be displayed who have matching records For now it has not displayed me Srilanka because Srilanka does not have any kind of matching records Left join displays all the records from the Left table while Right join displays all the records form the Right table and inner join displays only the matching records Now the last important join is a Full Outer Join You can think about full outer join as a combination of Inner Join + Left Join +Right Join Full outer join will show all the matching records It will show all non matching records from the left table as well as non matching records from the right table It will actually show everything If I write here full outer join and if I execute You can now see he is showing me matching records for India and Nepal You can see the first three records actually displayed the records who have matching entries You can see the fourth record showing Srilanka Now Srilanka did not have any matching records in the state table but it still displayed Srilanka In the same way it also displayed California When California has no matching records in the country table The full outer join is nothing but the sumesion of Inner join + Left join + Right join Let us put all these joins together and lets revise what we have learnt from this session The first important join is The Inner Join which displays the matching records from both the tables i.e both its a left table or right table only the matching records will be displayed any kind of unmatched records those will not will be displayed The second kind of Join is The Left Join where all the records of left table is displayed and only the matching records from the right table will be displayed And the right join is the vise versa of left join In other words in right join all the records from the right table will be displayed irrespective they have a matching records or not only the records which have matching entries of the left table will be dispalyed and a full outer join is the combination of all these joins It will display records which have matching entries It will display records which do not have matching entries So hope that you enjoyed this session In this session we were trying to understand Inner Join, Left Join, Right Join and Full Outer Join Thank you Very Much

Channel: Questpond

Views: 1,231,466

Rating: 4.8727546 out of 5

Keywords: SQL Server, Inner joins, Left joins, Right joins, Full outer joins, cross join, Learn SQL Server, Step By Step C#

Id: KTvYHEntvn8

Length: 8min 10sec (490 seconds)

Published: Fri Mar 30 2012