Joins in sql server - Part 12

Oh welcome to presume technologies I am linkit this is part 12 of signal server in this session we'll understand the different types of joints that are available in sequel server specifically cross join in a joint and outer joints before continuing with this session I strongly recommend watching parts three and five of this video series now joins in sequel server I used to retrieve data from two or more related tables in general tables are related to each other using foreign key constraints so I have this employee table on the right here which is got ID name gender salary and department ID columns and then I have the department table table itself here which has got the department name the location that Department is present at and the department head now if you look at these these two tables are related using this foreign key Department ID now if you are not sure what foreign key is please watch part 3 of this video series so these two are related tables now let's say I asked you to write a query which will give me an output as shown here on the left hand side I want the name of the employee gender salary and the department name and if you look at the output columns that I require the first three columns name gender and salary they are present in the employee table whereas the department name is present in the department's table so both you know these columns all these columns are not coming from a single table their spread across two different tables so obviously if I have to retrieve this output then I will have to join these two tables okay so to join these two tables in sequel server we have got different types of joints for example we have inner join outer join and cross joins and again the outer joins in sequel server which we'll be talking about in a bit are divided into three different categories left outer join right outer join or full and full outer join okay you can say left outer right outer full load or you can just say left join right join or full join now let's talk about inner join now let's say you know I want output like this you I want the name of the person their gender salary and the department name okay so let's see how we do this using inner join okay and look at this if you have two tables you know in the inner join the matching rules between the two tables are retrieved for example if you look at the employee table here you know everybody has department ID except james-andrew so if you look at James and Russell's record the department ID are now which means these rows doesn't have a matching department ID from the department you know departments table so when I join these two tables using inner join only the matching rows between these two tables are retrieved which means the matching rows between these two tables are you know these eight rows from 1 till 8 which is what I am getting and if you look at the output I don't get James and Russell's records why because their department ID does not match with the ID column in the department's table so inner join will only give you matching rows between both the tables involved in a joint now let's see how to write the inner join query itself so if you look at the two tables that we have in employee table we have got ten rows and in the departments table we have got four rows and what do we want we want the name gender salary and department name columns in the output from which table from TBL employee table and you know the first three columns are present in TBL employee table but the department name column is present in the department's table so I will have to join with that table and to join with that table you use the join keyword so PBO department so we are saying I want to join TBL employee table with TBL department table okay now if you want to join these two tables how do you want to join these two tables what's the common column between them the common column is the department ID column and to specify the join condition you use the on claws so on TBL employee so in the table employee we have the department ID column so use that to look up TBL department table and the ID column within that table okay so it's pretty simple select whatever columns you want from you know TBL employee now I want to join this table with the TBL department so we use the join keyword on how do you want to join those two tables now between these two tables the common column is the department ID you know department ID in TBL employee table join that with ID column in TBL department table so obviously if we execute this query we should get eight records okay now if we execute all of them together okay and if you look at the output here in the messages in the TBL employees table we've got eight rows and departments we've got four rows and in the join query we only got eight rows why because if you look at James and Russell their records doesn't have a matching department ID that's why inner join has not returned those two records so if you look at the join output we don't have James and Russell's records in the output we only have the matching records between these two tables okay and when you write an inner join query you can say inner join or just join I would say always write inner join because this will say your intentions explicitly okay alright so that's about inner join so inner join returns only the matching rows between both the tables non matching rows are eliminated okay now let's say my requirement is in such a way that I not only want the matching rows I want the non matching rows as well from the employees well basically I'm saying I want all the employees irrespective of whether they are assigned to a department and I want all the employees so how do we do that so indirectly you are saying okay I want the matching rows plus the non matching rows from the employees table which means you want everything from the left table matching and non matching okay that's when you actually go for left to join so if you look at left to join returns all the matching rules plus non matching rows from the left table so there are eight matching rows so this department ID matches all for these eight rules the department IDs match with the department IDs but these two rows doesn't have a matching department ID okay so but in departments table we have total ten rows so all matching and non matching rows will be retrieved when you use a left join so when we write a left join query we should get ten rows so how do we write the left join query you know everything remains exactly similar except that instead of inner join you will say left join that's it look at this previously we have got eight records when we executed that query but let's now execute this and see how many records we get so we first go to the messages look at this in employees table we have got ten rows in departments four rows and in the query left to join query that we have written we have got ten rules again so if you look at the output now we should have caught both James and Russell's records as well but if you look at the department name column it's basically not and it makes sense why because they don't have a department ID they are not assigned a department yet so that's why the department name will be now okay so left to join basically returns all the matching rules between both the tables plus non matching rows from the left table and this picture depicts exactly that scenario and when you use a left join you can say left just left join or you can say left outer join okay the query results will not be affected so the outer keyword is optional there all right now let's say I want all the rows from the right table including if you look at the two tables here you know you have a department called other department and that departments ID is for now this new employee in the employees table belongs to this other department okay so if there is a requirement you know if somebody asks you okay give me all the employees okay which means I want all the matching records between both the tables plus you know non matching records from the right table okay so from the department's table I want you know other department record as well irrespective of whether there is an employee assigned to that department or not so we want all the rows from the right table okay plus I mean all the rows including the non matching ones okay so basically right join will return you all the matching roles between both the tables plus non matching rows from the right table okay so let's see what happens you know if we convert this left outer join to right outer join okay so between employees and department table there are eight matching rows and in the TBL department table there is this other department which you know no other employ I mean no employee belongs to this other department okay so obviously this other department true does not have a matching record in employees table okay and we want this record as well okay so how do we do that using right outer join so when we execute this we should get around nine records so nine records so look at this all the matching rows until Sarah we've got and other department row okay and if you look at the name gender and salary columns are null why because no employee is assigned to this department so obviously will not have any name gender or salary okay so obviously whenever you want to return all the matching roles between both the tables that are involved in a joint plus non matching rows from the right table that's when we go for right outer join okay so you might have guessed by now if I want all the matching rows between both the tables plus non matching rows from the left table and non matching rows from the right table then what type of join do I use full join so full outer join gives me exactly that result these are the matching rows from both the tables these two are non matching rows from the left table and the last row here other department is the non matching row in the right table full outer join will give you all the records from both the left and right tables including the non matching rows okay so obviously when we execute this query we should get around eleven records eight matching records plus two non matching from left and one non matching from the right table so how do we write a full outer join query just specify full outer join so when we execute this query you should see until here all these matching records these two are non matching records in the employee table and this is a non matching record in the department's table so together we have eleven records again you can specify full outer join or just say full join okay so so far we have seen inner join left join right join and outer join full outer join okay but apart from all these types of joints we also have another joint called cross join so what does it cross join do and how do I write that query first let's write the query you know and we look at that but and then we'll decide what cross join is going to do so if it's a cross join instead of saying full outer join all I say is cross join now remember a cross join will not have a non class okay I'll tell you in a bit why a cross join shouldn't have a non Clause so when I delete that cross join and then when I execute this query look at this look at the number of rows that we are getting 40 rows and if you remember how many rows are there in these tables ten rows in the employees table and for roles in the department's table and when I when we execute the cross join query we are getting 40 rows so basically what's happening the number of rows in the employees table is being multiplied by the number of rows in the department's table so a cross join will give us the Cartesian product of the tables that are involved in a cross join okay so that's what cross join is all about cross join produces the Cartesian product of the two tables involved in the joint for example in the employees table we have ten records in the department's table if we have four records a cross join would produce 40 rows between these two tables and as I told you cross join shouldn't have an on clause okay so what basically a cross join does is it will take each record from the right table and then associate that with every record in the left table so in the department's table we have got four rows you know ID payroll HR and other department so it will take ite Juro from the department's table associate that to every one of the employees in the employees table and similarly it will take the next record which is payroll and associate that so it basically gives us you know the number of rows in the employees table multiplied by the number of flows in the department's table so it gives us a Cartesian product and since this gives us a Cartesian product it doesn't really make sense to have you know an on cloth cross join okay and look at this so far we have seen you know writing query for joins you know we have seen how to write inner join left outer right out of loader and cross join now you know depending on what we have done until now it's pretty simple to to develop a generic formula for joints so what is that select what columns you want or just say column lists okay select your column list for example I want name gender salary department name etc select column list from you know maybe your left the table and then you specify the join type in a joint cross join whatever and once you specify the join type you know with which table you are joining so right table and on clause for specifying join condition okay so if you compare this with what we have okay look at this if it's an inner join you know it would have been something like this so inner join and on we will have TBL department dot ID is equal to TBL employee dot department ID so if you compare this generic formula with this inner join query you know it exactly fits and select whatever columns we want from Clause the left table and then the join type here it's an inner join so we are specifying inner join otherwise if it's across you know left join with a left join right right join etc and then finally on clause and your join condition so this is the genetic formula you know if you have hard time you know remembering the syntax for joins alright so in summary cross join returns the Cartesian products of the tables involved in the join whereas inner join returns only the matching rules non matching rows between the tables are eliminated you know look at these pictures which will make the understanding of joins pretty much easy okay so if you look at the energy only matching rows left join matching rows plus non matching rows from the left table and right join matching rows plus non matching rows from the right table whereas full join matching rows between both the tables non matching from the left and non matching from the right okay on this slide you can find resources for asp.net and C sharp interview questions that's it for today thank you for listening have a great day

Channel: kudvenkat

Views: 851,738

Rating: 4.9017644 out of 5

Keywords: Joins in SQL Server, inner join, cross join, left join, right join, full join, outer join, sql get data from two tables, sql server inner join, sql inner join and outer join, sql inner join tutorial, sql inner join example, sql cross join, sql left join tutorial, sql left join example, sql left join and right join, left outer join in sql, sql right join, sql full outer join, sql full join example, sql right join tutorial

Id: wW4xcQ3FFp4

Length: 17min 43sec (1063 seconds)

Published: Sat Aug 18 2012