In MySQL, JOIN is a SQL command that combines rows from two or more tables based on a related column between them. It allows us to retrieve data from multiple tables in a single query.
There are four types of JOIN in MySQL:
1. INNER JOIN: The INNER JOIN retrieves only the matching rows from both tables based on a specified condition. It only returns rows from the tables where there is a match in both tables according to the specified condition. The syntax of INNER JOIN is as follows:
SELECT column1, column2, ...
FROM table1
INNER JOIN table2 ON condition;Example: Suppose we have two tables, Customers and Orders, with the following data:
Customers Table:
+----+----------+-----+
| ID | Name | Age |
+----+----------+-----+
| 1 | John | 25 |
| 2 | Mary | 30 |
| 3 | Peter | 35 |
+----+----------+-----+
Orders Table:
+-----------+------------+-------+
| OrderID | CustomerID | Total |
+-----------+------------+-------+
| 1 | 2 | 100 |
| 2 | 3 | 200 |
| 3 | 1 | 150 |
+-----------+------------+-------+To retrieve the customer name and order total for each order, we can use the following INNER JOIN query:
SELECT Customers.Name, Orders.Total
FROM Customers
INNER JOIN Orders
ON Customers.ID = Orders.CustomerID;This will result in the following output:
+-------+-------+
| Name | Total |
+-------+-------+
| Mary | 100 |
| Peter | 200 |
| John | 150 |
+-------+-------+2. LEFT JOIN: The LEFT JOIN retrieves all the rows from the left table and the matching rows from the right table based on a specified condition. If there is no match in the right table, then NULL values are returned. The syntax of LEFT JOIN is as follows:
SELECT column1, column2, ...
FROM table1
LEFT JOIN table2 ON condition;Example: To retrieve all the customers even if they have not placed any orders, we can use the following LEFT JOIN query:
SELECT Customers.Name, Orders.Total
FROM Customers
LEFT JOIN Orders
ON Customers.ID = Orders.CustomerID;This will result in the following output:
+-------+-------+
| Name | Total |
+-------+-------+
| John | 150 |
| Mary | 100 |
| Peter | 200 |
+-------+-------+
| NULL | NULL |
+-------+-------+3. RIGHT JOIN: The RIGHT JOIN retrieves all the rows from the right table and the matching rows from the left table based on a specified condition. If there is no match in the left table, then NULL values are returned. The syntax of RIGHT JOIN is as follows:
SELECT column1, column2, ...
FROM table1
RIGHT JOIN table2 ON condition;Example: To retrieve all the orders even if they have no customers associated with them, we can use the following RIGHT JOIN query:
SELECT Customers.Name, Orders.Total
FROM Customers
RIGHT JOIN Orders
ON Customers.ID = Orders.CustomerID;This will result in the following output:
+-------+-------+
| Name | Total |
+-------+-------+
| Mary | 100 |
| Peter | 200 |
| John | 150 |
+-------+-------+
| NULL | 250 |
+-------+-------+4. FULL JOIN: The FULL JOIN retrieves all the rows from both tables based on a specified condition. If there is no match in one of the tables, then NULL values are returned for the other table. However, MySQL does not support the FULL JOIN. We can mimic the FULL JOIN by combining the LEFT JOIN and RIGHT JOIN as follows:
SELECT column1, column2, ...
FROM table1
LEFT JOIN table2 ON condition
UNION
SELECT column1, column2, ...
FROM table1
RIGHT JOIN table2 ON condition;Example: To retrieve all the customers and all the orders even if they have no matching records, we can use the following FULL JOIN query:
SELECT Customers.Name, Orders.Total
FROM Customers
LEFT JOIN Orders
ON Customers.ID = Orders.CustomerID
UNION
SELECT Customers.Name, Orders.Total
FROM Customers
RIGHT JOIN Orders
ON Customers.ID = Orders.CustomerID
WHERE Customers.ID IS NULL;This will result in the following output:
+-------+-------+
| Name | Total |
+-------+-------+
| John | 150 |
| Mary | 100 |
| Peter | 200 |
| NULL | NULL |
| NULL | 250 |
+-------+-------+