What is join and types of join in Mysql

What is MySQL Join?

MySQL JOINS are used with SELECT statement. It is used to retrieve data from multiple tables. It is performed whenever you need to fetch records from two or more tables. In this tutorial, you will learn various MySQL join clauses in the SELECT statement to query data from two tables.

Supported Types of Joins in MySQL

1. INNER JOIN
2. LEFT JOIN
3. RIGHT JOIN
4. Cross Join

INNER JOIN
It is the most common type of join. MySQL INNER JOINS return all rows from multiple tables where the join condition is met.

Syntax
The syntax for the INNER JOIN in MySQL is:

SELECT columns
FROM table1 
INNER JOIN table2
ON table1.column = table2.column;

LEFT  JOIN
Another type of join is called a MySQL LEFT  JOIN. This type of join returns all rows from the LEFT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal (join condition is met).

Syntax
The syntax for the LEFT OUTER JOIN in MySQL is:

SELECT columns
FROM table1
LEFT [OUTER] JOIN table2
ON table1.column = table2.column;

RIGHT  JOIN
Another type of join is called a MySQL RIGHT OUTER JOIN. This type of join returns all rows from the RIGHT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal (join condition is met).

Syntax
The syntax for the RIGHT OUTER JOIN in MySQL is:

SELECT columns
FROM table1
RIGHT [OUTER] JOIN table2
ON table1.column = table2.column;

CROSS JOIN

MySQL CROSS JOIN is used to combine all possibilities of the two or more tables and returns the result that contains every row from all contributing tables. The CROSS JOIN is also known as CARTESIAN JOIN, which provides the Cartesian product of all associated tables. The Cartesian product can be explained as all rows present in the first table multiplied by all rows present in the second table. It is similar to the Inner Join, where the join condition is not available with this clause.

We can understand it with the following visual representation where CROSS JOIN returns all the records from table1 and table2, and each row is the combination of rows of both tables.

SELECT column_name(s)
FROM table1
CROSS JOIN table2;

Related Articles

What Is MVC Architecture? And Why Should You Care?

CodeIgniter Interview Questions And Answers

Function Overloading And Overriding In PHP

Difference Between GET And POST Method In PHP

Conclusion

In this article we learn about types of join mysql and what is join in mysql. Hope this article will help you to understand mysql join. Do you have any questions and query related about mysql join you can feel free contact us or comment us we will try to reply ASAP.