SQL Joins Explained with Examples - Inner Join, Left Join, Right Join, Full Join, Cross & Self Join

hi in this video we're going to talk about joins understanding joints and knowing how joints work is so critical in database work in this video we're going to talk about what joints are the different types of joints questions you can answer with these types of joints i'm also going to walk you through very specific examples using syntax in sql server i'll see you in a bit oftentimes when you work with databases the data that you need to pull or display or put in a report they reside in different tables so we need a way to put them together this operation is called join a join is an operation that allows you to combine multiple tables together based on common fields with this operation you're going to get access to all the fields from all the tables that you're using in your joins usually in transactional databases the structure that you're going to see is going to be similar to this this is a typical design that you're going to find when you have what we call normalized databases you're going to have some strong entities and if these strong entities have many-to-many relationships we are going to resolve them using what we call associative tables or associative entities before we go through the different kinds of joints i want to answer a few questions first that often come up when we talk about joints the first one does the associative entity have to have a composite primary key and the answer is no it's more of a design decision when you're first starting to learn databases in a lot of the books that you're reading or a lot of the resources that you come across they're going to show the associative entity with a composite primary key a composite primary key means that one of the keys come from one of the tables and the other key comes from another table so they're both foreign keys into this one table and you've technically assigned them another constraint called primary key which means they're going to be unique and not nullable in this one table however it's just a design decision you can very well have another primary key and just leave these two as foreign keys and not add on another constraint another common question that comes up is does the join need to happen on the primary key in the foreign key relationship do we need to have a primary key and foreign key before we can do a join the answer is no we don't have to join on the primary key and foreign key relationship there are times and there are reasons where you're going to be joining just on some common fields that are not necessarily the primary key nor the foreign key this is another common one how many columns are you going to have in your resulting table in a lot of the books that you're going to read when you're starting out and trying to learn databases they're going to simplify the results it makes it easier to understand those first basic concepts however the number of columns you're going to get will be the number of columns from one table that you're joining plus the number of columns in another table that you're joining a common misconception is that when you join based on a common field that common field exists only once in your resulting table and that's incorrect you may decide after the join to display only one of those fields but the immediate result retains both columns when we think about it these fields have actually very different meanings in the tables that they reside in for example a customer id in a customer table means a customer exists in that master list in that customer master list a customer id that resides in an orders table means a customer that has purchased or has ordered from us again those are two very very different meanings let's now start talking about the different types of joins the setup that we're going to use for our examples will be this let's assume that we have a database that has students courses and then the registration information the registration table is what we call our associative entity this table helps us resolve what we call many-to-many relationships what we're saying is a student can take many courses and a course can be taken by many students the different types of joints that we're going to talk about will be inner join outer join cross join and self join there's also different types of outer joins left join right join and full outer join and we are going to talk about all those in this video as well first let's talk about an inner join an inner join matches records based on common fields that you specify for example if we were doing an inner join on student and registration based on student id the operation will find matching values based on those student ids what's important to note is that inner joins are destructive joins it's destructive because it doesn't keep any records that don't match for example in here we have student id one that matches three times so it's going to keep all of these records student id number two also matches a record however student id number three does not have a matching record in registration therefore student id number three will not exist in our result so our result will only have four records and note in here that the first two columns are coming from the student table and the next four columns are coming from the registration table student id number three does not exist because it doesn't have a corresponding value in the registration table an outer join is a type of join that is a preserving join it will match up records based on the common field that you specify however depending on the type of outer join because we have three types we have left right and full outer join depending on the type of outer join it will preserve either the table to the left of the operator table to the right of the operator or both tables from both sides of the operator [Music] let's take this example if you were doing a left join on student and registration and you've placed student to the left of your join operator so student is to the left a left outer join will preserve all records from the student table please remember that right now we're only talking about the joins there are possibilities that some records are going to be excluded later on in your where clause but at the join level all records from students will be preserved and it will also find matching values on the registration table so in this case it will find a match for student id number one so it's going to be three matches in here student id number two will also match however student id number three even though there is no matching record in registration it is going to be left in the result set so we can see in this result that student id number three is left in our result however the columns that correspond to registration are left as null a null means a missing value or a non-existent value it means there's no match for student id number three in the registration table a typical question we might ask for this type of join would be which students have not taken any courses and in this case we can see student id number three has no corresponding record in registration therefore this student has not taken any courses from us how about a right outer join it is important to note that outer joins are positional a left outer join will always look to the table to the left of the operator and preserve just that side a right outer join will look to the table to the right and preserve that table if we for example switch registration and student in our query and move the student to the right of the join operator and this time used a right outer join it will still preserve student so if this was a right outer join one will match up a few times so in here it's going to match that one two and three two will match up again three will still not match however because this is a right outer join an outer join which is a preserving join we are still going to see student id number three in our result set so it's only the default positions that have changed but technically we have the same result as a left outer join and remember that in a database query we can move our columns around in our select clause here is another example of a right outer join if we were doing a right outer join between course and registration and course is on the right hand side of the operator it's going to retain all the forces whether or not those courses have been taken by students a typical question we might ask for this type of operation will be which courses have not been taken by students and again we can answer this question using a left join as well so left joints and right joins are really equivalent our choice of using a left join or right join sometimes is based on our own preferences or sometimes it simplifies some queries if you go from one direction or another how about a full outer join in a full outer join we preserve tables from both sides and technically the result of a full outer join is the result of your left join stacked on top of the result of your right join in this case if we were doing a full outer join on these two tables registration and course it means that we are going to find all of these records whether they have corresponding values on the right hand side and we're also going to find all these courses whether you see them in the registration table note in here that in registration we have a course id comp 3838 that doesn't exist in our course table and it's going to be preserved in our full outer join notice as well that con 4677 although it hasn't been taken by students it will also still show up in the result of our full outer join i just want to note in this case that having a course id in the registration table that does not exist in our course table in our main course list is a data quality issue all the courses that we have in registration must exist in course otherwise we're technically saying that students can take some phantom courses that don't really exist in our main course list a cross join is what we call a cartesian product and what it does is it basically pairs up a record from one table to all records from another table it's a product operation because the total number of records you're going to have in your result will be the number of records from one table times the number of records you have in another table so you have to be very careful when you're doing this you need to know why you're doing a cross join because even small tables can produce a lot of records so in this particular example if we are doing a cross drawing between student and course it is basically going to take every record from student match it up for all courses the next student match it up to all three courses the third student match it up to all three courses your result will look like this a self join is not necessarily another type of join a self join can either be an inner join or an outer join or a cross join the key thing in a self join is you're joining that table to itself and when you're doing this you need to make sure that the tables have different names or different aliases so in a self join it is important that you refer to the tables differently in this operation i hope you're finding this video useful so far and if you haven't subscribed yet please consider subscribing let us jump to sql server management studio in here i've already set up a sample database so i've called my database sql bell and in here i have three tables i have the student table i've also populated this with exact same records we had in the slides i also have the course table as well as the registration table i purposely did not create any foreign key constraints because we are going to demonstrate some data quality issues with our data the diagram of our mini database looks like this again there are no foreign key constraints that's why you're not seeing any lines between any of these tables [Music] so for an inner join the question that we might be asking will be which courses did students register in in this case we can just do a select start from student we can give it an alias we can go ahead and join this our registration we're also going to specify exactly how we're joining these and right now we are using the student id as our common field when we execute this we can see in this result set that we don't see student id number three so remember inner joins are destructive joints it will take away any records that don't have matching values in the other table i'm going to copy this exact same query and we're going to use this for our left outer join example in here a question that we could be asking will be which students did not take any courses and in this case i can simply change my inner join to a left join so instead of inner it's going to be left and i'm also just going to place them on the same line so it's easier for us to see what happens with a left join or any kind of outer join and let me just execute this so we can see the results side by side a left join will preserve all the records from the left table and we can see in here that all the students are appearing in our result set student id number three that does not have any matching registration records will have nulls for all of those columns that belong to registration note also in here that i have not typed in outer because the outer keyword is optional we don't have to type that in i have mentioned previously that left joins and right joins are technically equivalent we can achieve the same result set using either of these operators as long as we place the tables correctly so in this case if we wanted to achieve the exact same result set using a right join instead we can do it so let's try that out i'm going to copy this query over so in this case instead of a left join we're going to specify right join and instead of students being on the left hand side we are going to move the students to the right because we want to preserve all the students let's move registration to the left and from a display perspective if we want the exact same result we're just going to have to display all student columns first so s dot star comma registration dot star i'm going to run these two queries together the one with the left join the other one with the right join just so we can show that the results are exactly the same these are the results please note that these two queries don't necessarily answer our question right away which students did not take any courses that will require that we add a where clause so in this case we could potentially have a where clause the registration student id is null and if we execute this now we're gonna find student id number three the student who has not taken any courses if we wanted to answer the question which courses were not taken by students and in this case using a right outer join this indicates to us that we need to preserve the courses even though they don't have any matching values in registration so in this case select star from we're going to start with registration give it an alias we're going to do a right join right join course c on registration.course id is equal to c dot course id so we're matching on the course id that's their common field and again to directly answer the question we typically need to have a where clause that finds the one that has a non-matching value so in this case where the registration course id is missing let's execute this it looks like comp 4677 has not been taken by any of the students if we want to display all registrations and courses regardless of whether they have matching records on the other table we can use a full outer join in this case let's copy our query from the right outer join because it's going to be very similar all we're going to change is the keyword the right join now becomes a full join and again the outer keyword is optional so if we execute this so this is our result and in here we can find that there is a record where the course fields are null or missing which means there's no matching values and there is also a record where all the registration columns are null meaning there is no matching values for call 4677 in registration so a cross join is a cartesian product meaning it's going to match up records from one table to all records in the other table so to demonstrate this let's just quickly query all students in all courses if we do a cross join we're going to match up every student to every possible course this also means that the number of records we're gonna have will be the number of records in the first table which is three times the number of records in the other table which will be three so three times three is equal to nine there's a couple ways we can do a cross join so let's set this up first select star from soon in sql server there is a keyword called cross join so we can simply type that in cross join course and let's execute this so in here we can see that all the courses have been matched up to all three students and we have nine records altogether instead of a cross join we can also simply just apply a comma that's an old style join so instead of a cross drawing and place that in just a comma we're going to get the exact same result so again be very careful with cross joins even with small tables you can generate a lot of records and that could be a lot of trouble imagine having a small table with a thousand records another table with a thousand records that automatically generates a million records in results and for a self join it's simply joining that table to itself and the key thing is making sure that you have a different alias when you're doing a self join so in this hypothetical query so let's assume we want to do a self join for registrations trying to figure out the number of terms between course registrations for a particular student so our query could look something like this select star from from and here registration for now i'm going to assign it a different alias let's call this r1 and let's just say we're doing an inner join inner join registration different alias r2 on and in this case perhaps we are looking at the students so let's say r1 dot student id is equal to r2 dot student id and perhaps what we want to know is where the student terms are different so in this case where r1 dot term is not equal to r2 dot term this query will probably not generate any meaningful results but this is really just to demonstrate how we could do a self join in sql server that's it for our lesson on joins i hope you found it useful and if you're already familiar with joins hopefully you found it a worthwhile refresher see you again next time

Channel: sqlbelle

Views: 3,632

Rating: 4.9459457 out of 5

Keywords: SQL Joins Explained, SQL Server, left outer join, Transact-SQL, T-SQL, full join, full outer join, cross join, self join, database basics, learning databases, sql joins, sql tutorial, sql join, inner joins, sql for beginners, sql server joins, sql joins multiple tables, sql tutorial for beginners, right joins, full outer joins, sql joins with examples, sql joins tutorial for beginners, sql joins interview questions, Sql joins explained with examples, sql joins tutorial

Id: 4k5ODpvDZrY

Length: 21min 28sec (1288 seconds)

Published: Mon Oct 19 2020