When you wanna combine two or more tables based on the common column this is where we use the JOIN clause in Mysql.
A join is pretty much from the word itself to help us understand two or more tables by combining them based on the common factor among them.
Types of joins
- Inner Join: Now if we wanna join another table into a current working table with a common column as a reference INNER JOIN is the thing for you. To put it simply when we want to combine columns from table A into the column from table B that is where we use this. The INNER word is optional we don't have to type that to represent inner join.
- Left Join or Left Outer Join: Here the joining works in the way of all the left table content will be displayed and the right table content will be displayed with the respective matching pair of the left table content thus called the LEFT JOIN.
- Right Join or Right Outer Join: The RIGHT JOIN in MySQL will work exactly as opposed to that of LEFT JOIN. Here the entire record of the right table will be displayed irrespective while the left table content is displayed only about the matching ones. So to put it simply right table content is displayed even though there is no match whereas it is not applicable for the left side table content.
- Full Outer Join: This type of join is the complete opposite of the left and right where this one fetches and displays all the records from the provided tables on both ends with ease no matter they are on the left or right side.
- Cross Join: CROSS JOIN is a type of join where it is used to combine two or more tables across a database matching over its commonly defined or we can say that cross join is used to combine every record from a table to another table with its every record.
- Natural Join: Unlike any other joins in MySQL, the natural join is a unique type of join where the tables are joined automatically using the clause NATURAL JOIN. During this join the tables which are meant to join are automatic sets together using the common column present on both sides which is similar to inner and left join.
- Equi Join: An equijoin works in the way of combining two or more tables based on the equality or the matching columns where the '=' sign is used as the comparison operator to define equality with the pair up of WHERE clause.