In MySQL, INNER JOIN, LEFT JOIN, and RIGHT JOIN are three common types of joins used to combine data from two or more tables based on shared column values. Here’s a detailed look at each join type:

1. INNER JOIN

Definition: INNER JOIN is the most commonly used join type and returns only the rows where there is a match in both tables. If there is no match between the tables, the row will not be returned.

Syntax:

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

Example: Suppose you have two tables, employees and departments, containing employee and department information. You can use an INNER JOIN to retrieve information about employees and the departments they belong to.

SELECT employees.employee_id, employees.first_name, employees.last_name, departments.department_name  
FROM employees  
INNER JOIN departments  
ON employees.department_id = departments.department_id;

Use Case: INNER JOIN is suitable when you need to retrieve related data from two tables, such as getting employee details along with their department information.

2. LEFT JOIN

Definition: LEFT JOIN returns all rows from the left table (the first table in the join clause), along with the matching rows from the right table. If there is no match in the right table, NULL values are returned for columns from the right table.

Syntax:

SELECT columns  
FROM left_table  
LEFT JOIN right_table  
ON left_table.column = right_table.column;

Example: Suppose you have two tables, customers and orders, which contain customer and order information. You can use a LEFT JOIN to retrieve all customers and their orders. If a customer has no orders, the order-related fields will show as NULL.

SELECT customers.customer_id, customers.customer_name, orders.order_date  
FROM customers  
LEFT JOIN orders  
ON customers.customer_id = orders.customer_id;

Use Case: LEFT JOIN is used when you want to retrieve all records from the left table along with any matching records from the right table, such as listing all customers with their order information.

3. RIGHT JOIN

Definition: RIGHT JOIN returns all rows from the right table (the second table in the join clause), along with the matching rows from the left table. If there is no match in the left table, NULL values are returned for columns from the left table.

Syntax:

SELECT columns  
FROM left_table  
RIGHT JOIN right_table  
ON left_table.column = right_table.column;

Example: Using the customers and orders tables again, a RIGHT JOIN can retrieve all orders and their corresponding customer information. If an order does not have an associated customer, the customer-related fields will show as NULL.

SELECT customers.customer_id, customers.customer_name, orders.order_date  
FROM customers  
RIGHT JOIN orders  
ON customers.customer_id = orders.customer_id;

Use Case: RIGHT JOIN is useful when you want to retrieve all records from the right table, along with any matching records from the left table, such as displaying all orders and their customer information.

Summary

• Matching Rows:

• INNER JOIN returns only the rows that have matching values in both tables.

• LEFT JOIN returns all rows from the left table and the matched rows from the right table, with NULLs for unmatched rows.

• RIGHT JOIN returns all rows from the right table and the matched rows from the left table, with NULLs for unmatched rows.

• Use Cases:

• INNER JOIN is suitable for retrieving related data from two tables.

• LEFT JOIN is used when you need all records from the left table with any matched records from the right table.

• RIGHT JOIN is helpful when you need all records from the right table with any matched records from the left table.