1. Cross join
2. Equi-Join or Inner Join
3. left join
4. right join
The cross-join is a very basic type of join that simply matches each row from one table to every row from another table. While not very efficient, it does illustrate the common characteristic of all joins: table unification.
SELECT id, name, address FROM user CROSS JOIN user_info;
Equi-Join or Inner Join
Equi Join is a classified type of Inner Join in Mysql. Equi Join is used to combine records from two table based on the common column exists in both table. The Equi Join returns you only those records which are available in both table on the basis of common primary field name. The null records or unmatchable records are not to be displayed using Equi Join. The Outer Join is used to resolve these problems.
select a.id, a.name, b.address from user a, user_info b ON a.id=b.id;
Left join is used in case of need to return all rows from the left table, even if the right table doesn’t have any match.
select a.id, a.name, b.address from user a LEFT JOIN user_info b ON a.id=b.id;
Right join is used in case of need to return all rows from the right table, even if the left table doesn’t have any match
select a.id, a.name, b.address from user_info b Right JOIN user a ON a.id=b.id;