MySQL a part of the question is used to mix rows from two or more tables based totally on a associated column between them. There are distinctive sorts of JOINs, together with inner be a part of, LEFT be part of, right be a part of, and complete be a part of, every serving a unique motive. Let's go through an example of every sort of being part of:
Consider the following two sample tables:
- Table: employees
+----+----------+-----------+
| id | name | department|
+----+----------+-----------+
| 1 | John | HR |
| 2 | Jane | Finance |
| 3 | Mike | IT |
| 4 | Sarah | HR |
+----+----------+-----------+2. Table: salaries
+----+--------+
| id | amount |
+----+--------+
| 1 | 50000 |
| 2 | 60000 |
| 3 | 75000 |
+----+--------+
INNER JOIN:
The inner join returns simplest the rows which have matching values in both tables.
Example:
SELECT employees.name, employees.department, salaries.amount
FROM employees
INNER JOIN salaries ON employees.id = salaries.id;Result:
+-------+-----------+--------+
| name | department| amount |
+-------+-----------+--------+
| John | HR | 50000 |
| Jane | Finance | 60000 |
| Mike | IT | 75000 |
+-------+-----------+--------+
LEFT JOIN:
The LEFT be part of returns all of the rows from the left desk and the matching rows from the right desk. If there is no suit, it returns NULL for the columns from the right desk.
Example:
SELECT employees.name, employees.department, salaries.amount
FROM employees
LEFT JOIN salaries ON employees.id = salaries.id;Result:
+-------+-----------+--------+
| name | department| amount |
+-------+-----------+--------+
| John | HR | 50000 |
| Jane | Finance | 60000 |
| Mike | IT | 75000 |
| Sarah | HR | NULL |
+-------+-----------+--------+
RIGHT JOIN:
The right be a part of returns all the rows from the proper table and the matching rows from the left table. If there is no in shape, it returns NULL for the columns from the left table.
Example:
SELECT employees.name, employees.department, salaries.amount
FROM employees
RIGHT JOIN salaries ON employees.id = salaries.id;Result:
+-------+-----------+--------+
| name | department| amount |
+-------+-----------+--------+
| John | HR | 50000 |
| Jane | Finance | 60000 |
| Mike | IT | 75000 |
| NULL | NULL | 50000 |
| NULL | NULL | 60000 |
| NULL | NULL | 75000 |
+-------+-----------+--------+
FULL JOIN:
The full be a part of returns all the rows whilst there is a in shape in one of the tables. If there may be no fit, it returns NULL for both the columns from both tables.
Example:
SELECT employees.name, employees.department, salaries.amount
FROM employees
FULL JOIN salaries ON employees.id = salaries.id;Result:
+-------+-----------+--------+
| name | department| amount |
+-------+-----------+--------+
| John | HR | 50000 |
| Jane | Finance | 60000 |
| Mike | IT | 75000 |
| Sarah | HR | NULL |
| NULL | NULL | 50000 |
| NULL | NULL | 60000 |
| NULL | NULL | 75000 |
+-------+-----------+--------+Those are a few examples of the way be part of queries paintings in MySQL. Depending to your specific use case, you may select the proper be part of kind to retrieve the data you want.