SQL Joins Tutorial For Beginners | Inner, Left, Right, Full Join | SQL Joins With Examples | Edureka

[Music] hi everyone the society on behalf of ADA Rekha and I welcome you to the session on sequel joins so in the session guys will mainly focus on the different types of joints used in sequel now before I start telling you about the different types of joints in sequel let me just take you to the topics for today's session so we'll start today's session by understanding what the sequel and then we'll get into the main topic that is what are joints in sequel once you understand what is it join in sequel we'll get into the different types of joints in sequel and finally I'll end this session by telling you some most common questions asked about joints so I hope I'm clear with the topics all right so that's great so now let's move forward with the first topic that is introduction to sequel now in the era of 2.5 Quinten bytes of data being generated every day data obviously plays a crucial role in decision making for many business operations now this quite quintessentially makes us handle data and various kinds of data bases and this very much gives us the need of using different kinds of data bases now in today's market there are various kinds of data bases like the relational database the hierarchical database the network database and the object oriented database but yes sequel is the core of relational database which is used for accessing and managing the database now if you're someone who is looking forward to learn about sequel and get started with it you need to definitely understand the concept of sequel joins so in that note let's get into the next topic for today's session that is what our sequel joins so if someone asks you what are joints in sequel joints in sequel a commands which I used to combine rows from two or more tables based on a related column between those table the joints are predominantly used when a user is trying to extract data from a table which have one-to-many relationships between them or many to many relationships between their joints in sequel are basically commands guides by which you can join two tables and you can get the rows of two tables based on a related column now if you ask me what are the different types of joints in sequel there are mainly four types of joints in sequence that is the inner joint the left joint the right joint and the full joint so let's look into each one of them one by one starting with the inner join the inner join is a type of joint that returns those records which have matching values in both the tables so if you consider a table a and table B and you apply an inner join on both these tables then all those records would be returned which have matching values in both these tables so for our understanding I'm going to consider two tables that is the employee table and the project's table so when I apply the inner join on the employee table in the project table all those tuples which have matching values in both the tables will be given as output so the syntax for inner join is as you can see on the screen that a select table one taught column 1 table 2 column 2 table 2 column 1 and so on so basically these are the various columns that you want to retrieve from the respective tables from table 1 in a joint table 2 that is basically you are applying the inner join on table 1 and table 2 and then you have to mention the related column so that what we mentioned after the on statement so you'll mention in a joint table 2 on table one that match in column name is equal to table two dot matching column name so if I have to just repeat the syntax for you it's Selleck all the columns basically whatever columns that you need from table 1 in a joint table 2 on table one dot match column name equal to table two that match colony so if you want to see how in the joint can be applied practically let's move on to my my sequel workbench where I've created these two tables that is the employee table and the projects table and let's apply the joint statements on both these tables ok so before I move forward with this let me just open a new query tab and let me just clear all the action outputs so now what I'll do is I'll just write a statement that is select star from employee right so I'll just execute this particular statement and you'll see that you know you'll get the output as all the details from the employee table that I've already created I have around fire rose with 5 employee IDs and the first name last name age email ID phone number and address feed it into this table so similarly I'll show you the project's table right so let me just remove this and let me type in project I just execute this particular statement and you'll see the different column attributes in the project table so as you can see I have the project ID the employee ID the client ID project name and the project start date client ID can be basically considered for all those clients so you can have a separate table for the client IDs so guys if you see in both these tables there is a matching column that is the employee ID right so the basic relation between both these tables is that a specific employee having a specific employee ID can work on n number of projects right he can either work on the project number one two three four five six and so on right so as you can see my employee ID three he works on two projects that is the triple three project and the triple four project and similarly you can see that you know there are employees with the employee IDs nine seven eight but yes the information was not present in the employee table right so let's apply the joint statements on these tables and understand what we get us output so initially I had explained you what the inner joint was so when you apply inner join and two tables you can clearly see that you know you get matching values from both the tables so what I'm going to do is I'm going to apply inner join on both these tables so for that you simply have to write a query so what I will do is I've already executed it before I'll just copy and paste the query over here so that I can explain it for you guys so what I've done is I've just selected the employee ID the employee first named the employee last name the project ID and the project name to be retrieved from both the employee and the project tables and then I've applied an inner join on the employee in the project table so for that I've just written a query as you can see on the screen it's select employee dot M PI D employed at M F named employ dot M L name that is basically the first name and the last name then project or project ID project or project name from employee in a joint project on employ dot M PI D is equal to project or M PI D because M PI D is basically a matching column in both the tables right so let's just execute this particular query so when you execute this particular query you can clearly see that all the matching values from both the tables have been retrieved that is basically all the employees working on a specific project and their respective employee ID employer first name last name project ID in the project name has been retrieved so guys that was about inner join now smooth over to a next type of join that is the left join now as you can see on the screen now the left join or the left outer join returns all those records from the left table and also those records would satisfy a condition from the right table so if you consider table a and table B has two tables on which you want to apply the left join then table a would be considered as the left table and table B would be considered as the right table and when you apply a left join a table a and table B you will clearly see that you know all the records from the left table would be the trave and also the records which satisfy a condition from the right table would also be retrieved also let me tell you that you know the records which have no matching values in the right table the output or the result set will contain the null values right so basically if you have around three rows from the left table which have no matching records from the right table then those records will have null values so the syntax for left join us as you can see in your screen it's just similar to inner join you just have to replace the word inner join two left join you can change the syntax like you know select table one that column one table dude would call him two and so on that is basically the different column attributes that you want to retrieve from table one and then left joint table two on table one that matching column name equal to table two out matching column name so guys that was about left joint so now let's just again shift back to my my sequel workbench and then I'll show you how you can apply the left join I'm just gonna use the similar query so what I'm going to do is I'm going to retrieve the first name the last name the project ID and the project name from both the employee and the project table and then apply a left join on it so for that I've written a query as you can see on the screen that is select employee dot M named employ dot M L named project dot project ID project or project name from employee left joint project on employee that M PI D is equal to project or Empire right so basically that is because you know this is the matching column between both the tables so I'm going to just execute this particular query so once you execute this query you can clearly see the output all the records from the left table and for those records which do not have any matching record for the right table we have a null value present so if you remember we had five employees in the employee table right so basically that was warden Himani IU Shi Swati and famous and if you remember I you she worked on two projects so both the project details are present over here and he meant worked on no project right so since he worked on no project the values present in the project ID and the project name are null values by default so guys that was about the left joint now let's move forward to the next type of joint that is the right joint so the right joint or the right outer join returns all the records from the tide table and also those records would satisfy a condition from the left table so I know where this sounds quite similar to the left outer join yes it definitely is the only difference between both of them is that you know the right outer join returns all the records from the right table and also the records fit satisfy condition from the left table coming to the left outer join the left outer join returns all the records from the left table and also those records would satisfy a condition from the right tip so it's just Y squares of both of them and also you know the records which have no matching values in the left table the output or the result set would contain the null values so guys that was about the right choice now the syntax of right join is as you can see in the screen it's again really similar to the left join it's simple it's select table 1 2 column 1 table 2 wrote column 2 and so on so basically all the column values from both the tables that you want to retrieve from table 1 right join table 2 on table one dot matching column name equal to table 2 got matching column name so the only difference between syntax if you seen the three joints that you saw till now was the word inner left and right else everything else would be C so you just have to apply the same logic so for that what we'll do is we'll again shift back to my my sequel workbench now what I'll do is I'll just copy the query I'll paste it over here again and then I just changed this word to right so I'll just apply write join now I'll just execute this query again so when we execute this query you'll clearly see that you know the employer first name last name project ID and project name are retrieved from both the tables all those records from the right table are we'd retrieved and the records which satisfy a condition from the left table are also relieved but yes those records which have no matching values from the left table have null values present in the record you can clearly see that you know words in Himani are you she and swathi worked on projects and then they had this specific project IDs in project name but yes they were employees who worked on projects 6 7 8 but their employer information was not present in the employ table so that's the reason there is null value present in the records of 6 7 8 project so guys that was about right join now moving forward to a final type of joint that is the full joint so the full join or the full outer join returns all those records which either have a matching value in the left table or in the right table so basically if you consider table a and table B and you apply full join on both these tables then it will return all those records which either have the matching value in table in or in table P now the syntax for full join is really simple it's again the same as the other three joints but you just have to mention the word full join so the syntax is select table 1 column 1 table 2 column 2 table do dot column 1 and so on basically the different column attributes from table 1 full joint table 2 on table one dot matching column name equal to Table two dot matching column name so that's basically again the related column that is basically a matching column now to see how full joint works let me just shift back to my my sequel workbench now let me just tell you one thing over here now since I'm showing you how to apply joins on sequel let me just tell you that you know on my sequel the full joint is not applicable so that's the reason since I'm showing you on my sequel workbench the word full join will not work to just show you the working of full joint I'm just going to use the word Union in between both these queries so what's gonna happen is we're going to retrieve all the values from the left table and the matching value to the right table that is to the left joy and then we go to Union it with all the matching values from the right table and also the values with satisfied condition from the left table so this is nothing but full joint but since we're working on my sequel workbench and the word full joint doesn't work so I'm gonna just use the word Union I'm just going to retrieve the project ID the employer first name and the employee last name so what I'm going to do is I'm gonna just execute this particular query so when I execute this particular query you'll clearly see that you know we are getting all the values from the left table which satisfy a condition from the right table and also all the values from the right table which again satisfy a condition or match the values from the left table right so if you consider the first left joint condition where we got all the employee details and the project details you see that here we've got Watson Himani are you she twice swathi Heyman and their respective project details since him and did not work on any project the project ID was null away here now similarly if we consider the output for the right joint where you know we took all the records from the right table and took matching values from the left table we saw that you know for the project ID is triple six triple seven triple eight there was no employee information present right so that's the reason we have null values over here for employ first name and employed last name so guys that was about full joint so I hope you've understood the different types of joints the syntax is really simple for all the different types of joints guys you just have to change the word in all the different types of choice for any join you use the word inner for the left join you use left joint for the right you use the right join and for the full join you use the word full join so if you just want to apply left join what you simply do is you mention all the columns that is you mentioned the keyword çelik and mention the table name dot column name and then you mentioned from table one left join table two on and then you mention the matching column names from both the tables right so you mentioned table one got matching column name equal to Table two that matching column name so guys that's how you can use the different types of join I hope it's really clear to you guys if you have any further queries then you can definitely let us know in the comment section now that you know the different types of joints in sequel let me just cover few important questions that are generally as in interviews about joints so the first question is what is a natural join and in which situations is a natural joint used a natural join is also a joint operation that is used to give an output based on the columns in both the tables between which the joint operation is implemented so that's what a natural join is guys it basically aims to give you output based on the columns between which this join is applied now basically natural join is used when you want to make sure that you know the number of columns returned are less so for example if you see on the screen we have two tables right that is stable one having two columns and table two having two columns over here the table one had column names column one column two and table two again has column names column one and column three now if you apply a natural join on both these tables you'll clearly see that you'll get an output as column one column two column three and the respective records but in the same scenario if you apply an in and join what you will clearly see that you know you'll get an output like one column one that is basically for table 1 column 1 and then 1 column 2 that is table 1 column 2 and the respective records would be stored now if you see over here you'll clearly see that you know when you apply the inner joint you'll have a redundancy of data that is you know the column 1 data is getting repeated again that is basically it is repeated twice but in this case of natural joints just mentioned once so this was a scenario of two tables where you know the output was so simple but yes if you look into a daily basis where you know database administrators deal with n number of tables and this they were to apply the joint operation on these two tables you'll clearly see that you know if they apply an inner joint then that would create a lot of problem of redundancy of data and obviously more number of columns would be generated but yet in the same scenario if they apply a natural join the number of columns would be reduced at the maximum extent so that's the situation guys where you apply a natural joint so if you ever ask you know in which situations do you apply a natural join you can just answer this question by simply saying that you know you can apply a natural join when you want the number of columns to be less so I hope I'm clear with this point now moving on to a second question that is how to map many to many relationships using joints now it's a known fact that you know the joints are basically used to map one-to-many relationships but yes if there's a confusion you know how to map many to many relationships let me just tell you that you know you need to use two joint statements so to explain you why to join statements are required you can consider the scenario when you have creep tables that is the employee table the projects table and the technologies table now let's assume that you know each employer is working on a single project so this obviously means that you know one project cannot be assigned to more than one employee similarly if you consider a project can be based on multiple technologies and any technology can be used in any number of projects this kind of relationship is basically a many-to-many relationship now to apply the joint operation on the many to many relationships what you can simply do is you can have three tables that is the project's table and the Technologies table itself so basically these are the two tables which have many to many relationships between each other and also we can have an extra table that is the projector technology stable now the projector technologies table will hold the combination of project and technology in every row now let's say we have a project a let's say this project a has three technologies that is DevOps microservices and hadoo now you have to map these technologies to the project right now this project to technology stable will hold the record of every project technology combination so it will hold a record of project a to DevOps and then project a to again Hadoop and project a to micro-services similarly it will have n number of records for n number of projects and M number of technologies so this table is really really important in this scenario and this table basically aims to map the item on the project's table to the items on the technologies table so that multiple projects can be assigned to one or more technologies so now that you have three tables that is the project's table the technologies and the projects to technologies table you need to use two joint statements to link all these tables together that is used the first joint statement to join the project two technologies to project stable and then we have to use the second joint statement to join the project two technologies table to technology stable now this is how all the projects and the technologies which have of many to many relationships between them will be linked with each other so it's really simple guys whenever you have an end-to-end relationship between two tables and you want to apply a joint operation between these two tables you just have to create another table you know which can have all the combinations of the previous table and then you apply a joint operation from table a to table C and then table B table C so I hope I'm clear with this point now let's move forward with our next question that is what is a hash joint now has joints are also a type of joints which are used to join large tables or an instance where the user wants most of the joint table rows so whenever the user wants the most of the joint table rows or when you want to join two large tables you basically use this type of joint that is basically the hash joint the highest joy and algorithm is basically a two-step algorithm so it has mainly the build phase and the pro phase so in the build phase you basically create an in-memory hash index on the left side of input and in the pro phase you go through the right side of input each row at a time and then find the matches using the index created in the build phase so hash join is again a type of joint which is used to join large tables and it has mainly two steps that is the build phase in the prophase in the build phase you basically create an in-memory hash index on the left side of input and in the pro phase you go through the right side of input each row at a time and find the match using the index created in the build phase so guys that was about the hash joint now let's move forward with the next question that is what is a self join and a cross joint so the self join in other words is a join of a table to itself so this basically means that you know each row in a table is joined with itself so when you apply a self join to a table just remember that you know each row in a table is joined with itself now coming to cross join the cross join is a type of joint in which a join Clause is applied to each row for table to every row of the other table so whenever you apply a cross join two tables then just remember that you know a join Clause is applied to each row of a table to every row of the other table also when the where condition is used this type of joint behaves as an inner join and when the where condition is not present it behaves like a Cartesian product so you just have to remember two points the first point is that whenever you apply across join on specific tables our join clauses applied on each row of a single table to every row of the other table and also when the where condition is present this type of join behaves as an energy and when the where condition is not present it behaves like a Cartesian product now let's move forward with a final question that is how to perform joint operation on three tables now if you remember in the previous questions I discussed a question where you know we understood how to map end-to-end relationships using joint operations right so but there if you remember we use three tables it is a project stable the technologies table and the projector technology stable now to apply a joint operation on three tables we use two joint statements right that is to join table a to table C and then table B to table C so basically to apply a joint operation on three tables you need to use two joint statements so guys with this we come to an end of this session I hope you found this session informative and I hope you understood what sequel joints are if you have any further queries related to sequel joints please comment in the comment section below and we'll make sure we reply to you as soon as possible thank you and have a great day I hope you have enjoyed listening to this video please be kind enough to like it and you can comment any of your doubts and queries and we will reply them at the earliest do look out for more videos in our playlist and subscribe to any rekha channel to learn more happy learning

Channel: edureka!

Views: 450,412

Rating: 4.7179704 out of 5

Keywords: yt:cc=on, sql joins, joins in dbms, sql types of joins, sql left joins, sql outer joins, sql join 3 tables, sql multiple joins, sql self joins, sql joins with multiple tables, sql joins multiple tables, sql join statements, sql joins with example, sql joins examples, sql joins explained, sql joins diagram, sql learning, sql server joins, sql server tutorial, database join, database joins, database join types, cross database joinmysql tutorial, mysql training, edureka

Id: bLL5NbBEg2I

Length: 23min 16sec (1396 seconds)

Published: Tue Apr 02 2019