MySQL JOIN

The MySQL JOIN operation is used to combine rows from two or more tables based on a related column between them. It allows you to create a single result set that contains columns from both tables, often based on a common key or relationship. Joins are essential for querying and retrieving data from related tables in a relational database. There are several types of joins in MySQL:

1. INNER JOIN:

• An INNER JOIN returns only the rows that have matching values in both tables.
• It excludes rows from both tables that do not have corresponding values in the other table.

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

2. LEFT JOIN (or LEFT OUTER JOIN):

• A LEFT JOIN returns all rows from the left (first) table and the matching rows from the right (second) table.
• If there are no matching rows in the right table, it returns NULL values for the columns from the right table.

   SELECT columns
   FROM table1
   LEFT JOIN table2 ON table1.column_name = table2.column_name;

3. RIGHT JOIN (or RIGHT OUTER JOIN):

• A RIGHT JOIN returns all rows from the right (second) table and the matching rows from the left (first) table.
• If there are no matching rows in the left table, it returns NULL values for the columns from the left table.

   SELECT columns
   FROM table1
   RIGHT JOIN table2 ON table1.column_name = table2.column_name;

4. FULL JOIN (or FULL OUTER JOIN):

• A FULL JOIN returns all rows from both tables and includes both matching and non-matching rows.
• If there is no match in one table, it returns NULL values for the columns from the other table.

   SELECT columns
   FROM table1
   FULL JOIN table2 ON table1.column_name = table2.column_name;

5. CROSS JOIN:

• A CROSS JOIN returns the Cartesian product of rows from both tables, resulting in all possible combinations of rows between the two tables.

   SELECT columns
   FROM table1
   CROSS JOIN table2;

6. SELF JOIN:

• A SELF JOIN is a join in which a table is joined with itself. It is used when you want to compare and relate rows within the same table, often using different aliases to distinguish between the two instances of the same table.

   SELECT e1.employee_name, e2.employee_name
   FROM employees AS e1
   JOIN employees AS e2 ON e1.supervisor_id = e2.employee_id;

When using joins, it’s essential to specify the correct join condition to relate the tables properly. The choice of the type of join depends on the specific requirements of your query and what data you want to retrieve. Join operations are fundamental for creating complex queries and reports in relational databases.