SQL is the structured query language which is the programming language to interact (communicate and manage) with the relational database.

To retrieve the rows of multiple tables of data we need to use the join clauses in the SQL query. There are these major join types in MYSQL, These are as followings:

1. INNER JOIN
2. LEFT JOIN
3. RIGHT JOIN

To provide the practical examples on the join types let's create following tables.

store

address

1. INNER JOIN

JOIN and INNER JOIN are identical in MYSQL. While joining the table if both table contains the match records from the both tables then only those information it returns from the query.

For e.g.

SELECT * 
FROM store
JOIN address ON store.address_id=address.address_id;

This will return the following:

2. LEFT JOIN

LEFT JOIN and LEFT OUTER JOIN are identical in MYSQL. This join gives the match result and if there is not match then those records will be available from the left table and related null values from the right table.

For e.g.

SELECT * 
FROM store
LEFT JOIN address ON store.address_id=address.address_id;

This will return the following:

3. RIGHT JOIN

RIGHT JOIN and RIGHT OUTER JOIN are identical in MYSQL. While using this RIGHT JOIN this will return the rows from the table which are matched rows and for not matched values it return the right table rows by providing null values to left tables rows values.

For e.g:

SELECT * 
FROM store
RIGHT JOIN address ON store.address_id=address.address_id;

This will return the following results:

So the above are some of the basic join types in mysql.