 So in this module I will talk about retrieving data from multiple tables and for that purpose we will use command which is called as the join command and I will talk about different types of join commands and give a very overview brief overview about the join command. In the next module I will talk about how those join commands are used on the database and using PHP we will retrieve the results. Obviously I will not go into a lot of details because it is the intro of the main modules which are going to follow. Now you will be thinking that why do we need to join the tables right? Why we need to join the tables? Now what you might be thinking is that if we have to join the tables what was the purpose of separating the tables in the first place? That is an open contradiction. It will appear to you it is an open contradiction that is a good question that is a good observation. If you recall in module number 10 I had spoke about the cardinality of the relationships that was the 1 to 1 relationship 1 to many and many to many and so on. Now if in real life there was only 1 to 1 relationship which is not the case because there are other relationships also then it would have been okay to have a single table but that is not the case that is not the case that is why when we have 1 to many relationships then we need to have multiple tables. Why because we have thousands of rows tens of thousands of rows transactions are taking place and if we don't divide the tables then there will be a lot of repetition of data and the size will increase and the performance will degrade because if there is duplication and I bring in data I am bringing in duplicate data that will that is not an efficient way of using the bandwidth. So that is why we separate the tables. Now when we have to run the query I will give you the examples things will became clear to you we need to combine the tables that is called as a join and for that purpose we use the join command and how do we retrieve the tables how do we retrieve the data from multiple tables by using the join command. The join command runs on a column or multiple columns which are common to the tables being joined and they have the common value with kind of a reference to bring in what is required right and the results can be stored as a table they can be used and on the screen also and they can be processed also. So what are the different types of joins there are four main types of joins which is the inner join which is the full outer join the left join the right join and there is something which is called as the self join also. These things will become clear when I show it to you on the next slide in the form of Venn diagrams. You can see over here that this is the inner join over here this is the inner join okay and this is the full join. Now the full join basically consists of all the records which matched on both the tables and the null values for unmatching records right. So then so this full join is kind of a logical combination of the left table join and the right table join also right or not. So it's a logical combination and usually in terms of performance this is the fastest this is the fastest okay inner join is the fastest right. So this gives the best performance you I believe now you understand right. So in the next module I will talk about the details. Now I have spoken about the trivial join right this is the then the full join also and this is something very interesting using subselects. Now in prior to PHP 4.1 there was no there was no subselects the advantage of subselects is that it runs the select command which is nested within another select command. So I can have this select command running on two different tables. So in the nested select command I will go into more details you get the results and those results are used as the keys in the outer select command to retrieve the data. Now it may look like a join also it can be implemented as a join also and in some cases it may give performance in terms of join which is better than the this subselects right or not. So that is one application domain but that was not there prior to PHP 4.1 this another thing over here which is the union union was not there prior to PHP 4 but then it was introduced. So what the union does is that it one by one as the results come from different tables it can work on those results. So it provides a union of the results so we have those four types of joins and then we have the subselect and the subselect as I told you acts as or it can be replaced by joins also and of course in summary the best performance is provided by the inner join and of course the outer join is a logical combination of the left join and the right join and finally we combine the tables because the data is dispersed across the tables and we have dispersed the data so as to save space and to give performance. Now very briefly very very briefly if you look into the concept of operating systems the data is fetched in the form of frames and in the form of pages. Now try to understand if there is repetition of data the frame size is fixed or the page size is fixed then the data brought in with repetition will be less as compared to I bring in the data of the same page or the frame size which is non-repeating it means that in fewer page faults I will bring in more data that will give good performance right or not you understand. So using by by removing the duplicates without using additional hardware or processing power one can get better performance so that is the benefit of separating the tables but of course since the data is spread so if you look at only one table or or separately tables from other each other then it does not gives the complete picture that I will explain to you in the next module.