How many types of join operation are there?
|
Let there be a database of all the class 12th boys students in a school. The table name is Boys. Show IDNamePercentage %1Rohan562Rohit853Amit754Ravi795Saiz656Tejan847Rishabh75 Let there be a database of all students in the school who are interested in different sports. The table name is Interest. IDNameGenderSport3AmitMCricket23AmanMChess5SaizMCricket10ShreyaFBadminton6TejanMChess15SakshiFChess2RohitMCricket16TejanMChess35ShubhiFCricket1RohanMChess4RaviMChess Now, if someone asks me to find the data of all the 12th class boys in the school who are interested in Cricket. How would I proceed? You would say just filter out all the boys students from the Interest table. But that would not work as there can be student of same name in different class with different IDs. As in the Boys table above, there is a boy named Tejan in class 12th and also there is a boy named Tejan with ID=16 in the Interest Table but they are not same. So the filtering method can lead to errors. We can find the solution by joining both the tables and then filtering results based on some conditions. Let's see how? Note- The above table will be used as a reference to explain all the different types of Join further in this article. What is Join in DBMS?Joins in relational algebra are simply cartesian products followed by selection. In the above example, if we combine both the Boys table and Interest table such that the ID of students in the Boys table is same as the IDs of students in Interest table, then it will be easy for us to filter out the desired result of all the boys student of class 12th who are intrested in Cricket. If we perform Inner Join on both tables with one condition as : Boys ⋈(Boys.ID = Interest.ID and Interest.Sport=Cricket) Interest The join condition (Boys.ID = Interest.ID and Interest.Sport=Cricket) first performs Cartesian product on both tables and then makes selection to give only those class 12th boys who are interested in Cricket. The Result of the above Relational algebra query will be : IDNameGenderSport3AmitMCricket5SaizMCricket2RohitMCricket Types of JoinsThere can be more than one way to join the database tables. So different types of Joins are:-
Inner JoinIt selects the values present in both the Table performing Inner join.
Theta Join Theta Join is used to join two tables based on some conditions. The condition can be on any attributes of the tables performing Theta join. Any comparison operator can be used in the condition. A ⋈θ B where θ is the condition for join. Let's understand Theta Join with the Boys and Interest tables used above : What if we want to find all the boys student in class 12th who like chess and have percentage greater than 70%. How can we find it out with the help of Theta join? Theta Join - Boys ⋈(Boys.ID = Interest.ID and Interest.Sport = Chess and Boys.Percentage > 70 ) Interest So the condition here is Boys.ID = Interest.ID and Interest.Sport = Chess , so while performing join, we will have to check this condition every time two rows are joined. The result of Theta Join will be:- IDNamePercentageGenderSport6Tejan84MChess4Ravi79MChess Equi Join Equi join is same as Theta Join, but the only condition is it only uses equivalence condition while performing join between two tables. A ⋈(... = ...) B, where (... = ... ) is the equivalence condition on any of the attributes of the joining table. In the above example, what if we are told to find out all the students of class 12th who have interest in chess only? We can perform Equi join as : Equi join: Boys ⋈(Boys.ID = Interset.ID and Interest.Sport = Chess) Interest Result after performing Equi join: IDNamePercentageGenderSport6Tejan84MChess1Rohan56MChess4Ravi79MChess Natural JoinNatural join is also considered a type of inner join but it does not use any comparison operator for join condition. It joins the table only when the two tables have at least one common attribute with same name and domain. In the result of the Natural Join the common attribute only appears once. It will be more clear with help of an example : What if we are told to find all the Students of class 12th and their sports interest we can apply Natural Join as : Natural Join: Boys ⋈ Interest So when we perform Natural Join on table Boys and table Interest they both have a common attribute ID and have the same domain. So, the Result of Natural Join will be: IDNamePercentageGenderSport3Amit75MChess5Saiz65MCricket6Tejan84MChess2Rohit85MCricket1Rohan56MChess4Ravi79MChess In the table the common attribute ID is only displayed once in the result. Outer JoinOuter Join in Relational algebra returns all the attributes of both the table depending on the condition. If some attribute value is not present for any one of the tables it returns NULL in the respective row of the table attribute.
Let's see how these Joins are performed. Left Outer Join It returns all the rows of the left table even if there is no matching row for it in the right table performing Left Outer Join. A Left Outer Join B Let's perform Left Outer Join on table Boys and Interest and find out all the boys of class 12th and their sports interest. If we perform Left Outer Join on table Boys and table Interest such that Boys.ID = Interest.ID . Then Result of the Join will be: Boys.IDBoys.NameBoys.PercentageInterest.IDInterest.NameInterest.GenderInterest.Sport1Rohan561RohanMChess2Rohit851RohanMChess3Amit751RohanMChess4Ravi791RohanMChess5Saiz651RohanMChess6Tejan841RohanMChess7Rishabh75NUllNULLNULLNULL Clearly, we can observe that all the rows of the right table and left Table, i.e., Table B and A are present in the result. What are the types of join operations?The JOIN operations are:. INNER JOIN operation. Specifies a join between two tables with an explicit join clause.. LEFT OUTER JOIN operation. ... . RIGHT OUTER JOIN operation. ... . CROSS JOIN operation. ... . NATURAL JOIN operation.. How many types of join are there?Four types of joins: left, right, inner, and outer.
How many join operations are there in SQL?As known, there are five types of join operations: Inner, Left, Right, Full and Cross joins. In this article, we will explain the meaning of Joins in SQL, we will describe each one of the Join operation types and we will show the main use cases where it is used by providing examples.
What are the 4 types of joins in SQL?SQL Join statement is used to combine data or rows from two or more tables based on a common field between them. Different types of Joins are as follows: INNER JOIN.
|
