In MySQL, a join is used to combine rows from two or more tables based on a related column between them. There are different types of joins available in MySQL, including inner join, left join, right join, and full outer join.
The basic syntax for a join in MySQL is:
SELECT columns FROM table1 JOIN table2 ON table1.column = table2.column;
In this syntax,
columns are the columns you want to select from the combined tables,
table2 are the tables you want to join, and
column is the related column between the two tables.
Types of Joins in MySQL
There are four main types of joins in MySQL:
- Inner join: returns only the matching rows from both tables
- Left join: returns all the rows from the left table and matching rows from the right table
- Right join: returns all the rows from the right table and matching rows from the left table
- Full outer join: returns all the rows from both tables, with NULL values for non-matching rows in each table.
In addition to these main types, MySQL also supports cross join (also known as Cartesian product) which returns all possible combinations of rows from both tables.