MySQL supports the following types of joins:
1. INNER JOIN: An inner join is a join in which the results of the join operation are limited to the rows that satisfy the join condition. For example, the following query returns all the rows from the table ‘A’ and ‘B’ that have matching values in the column ‘ID’:
SELECT * FROM A
INNER JOIN B ON A.ID = B.ID;
2. LEFT JOIN: A left join is a join that returns all the rows from the left table, even if there are no matches in the right table. For example, the following query returns all the rows from the table ‘A’, even if there are no matching rows in ‘B’:
SELECT * FROM A
LEFT JOIN B ON A.ID = B.ID;
3. RIGHT JOIN: A right join is similar to a left join, except that it returns all the rows from the right table, even if there are no matches in the left table. For example, the following query returns all the rows from the table ‘B’, even if there are no matching rows in ‘A’:
SELECT * FROM A
RIGHT JOIN B ON A.ID = B.ID;
4. FULL OUTER JOIN: A full outer join is a join that returns all the rows from both tables, regardless of whether there are matching rows in either table. For example, the following query returns all the rows from both ‘A’ and ‘B’:
SELECT * FROM A
FULL OUTER JOIN B ON A.ID = B.ID;
5. CROSS JOIN: A cross join is a join that returns the Cartesian product of the two tables. For example, the following query returns all the possible combinations of rows from the table ‘A’ and ‘B’:
SELECT * FROM A
CROSS JOIN B;