MySQL Joins

Last Updated: 18 June, 2023

In MySQL, joins are used to combine rows from two or more tables based on a related column between them. Joins allow you to retrieve data from multiple tables in a single query and establish relationships between tables.

There are different types of joins in MySQL:

1. INNER JOIN: Returns only the matching rows from both tables based on the specified join condition.

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

2. LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table and the matching rows from the right table. If no match is found, NULL values are returned for the columns of the right table.

   SELECT * FROM table1
   LEFT JOIN table2 ON table1.column = table2.column;
   

3. RIGHT JOIN (or RIGHT OUTER JOIN): Returns all rows from the right table and the matching rows from the left table. If no match is found, NULL values are returned for the columns of the left table.

   SELECT * FROM table1
   RIGHT JOIN table2 ON table1.column = table2.column;
   

4. FULL JOIN (or FULL OUTER JOIN): Returns all rows from both tables. If no match is found, NULL values are returned for the columns of the table without a match.

   SELECT * FROM table1
   FULL JOIN table2 ON table1.column = table2.column;
   

5. CROSS JOIN: Returns the Cartesian product of both tables, resulting in all possible combinations of rows from both tables.

   SELECT * FROM table1
   CROSS JOIN table2;
   

These are the basic types of joins in MySQL. You can use them to combine tables and retrieve data based on specific conditions. Remember to replace "table1" and "table2" with the actual table names you are working with, and "column" with the column name used for the join condition.