Mysql connection query left join, right join, inner join example detailed explanation

This article mainly introduces the relevant information of mysql connection query (left connection, right connection, inner connection). Here are examples of how to use it and attached example code. Friends who need it can refer to it. I hope it can help everyone.

1. Commonly used mysql connections

• INNER JOIN (inner join, or equivalent join): Get the matching relationship between fields in two tables Record.

• LEFT JOIN (left join): Get all the records in the left table, even if there are no corresponding matching records in the right table.

• RIGHT JOIN (right join): Contrary to LEFT JOIN, it is used to obtain all records in the right table, even if there are no corresponding matching records in the left table.

mysql> select * from name_address;
+----------+------+----+
| address | name | id |
+----------+------+----+
| 西北一路 | 张三 | 1 |
| 西北二路 | 李四 | 2 |
| 西北三路 | 王五 | 3 |
+----------+------+----+
3 rows in set

mysql> select * from name_age;
+-----+--------+----+
| age | name  | id |
+-----+--------+----+
| 18 | 张三  | 1 |
| 20 | 王五  | 2 |
| 21 | 路人甲 | 3 |
+-----+--------+----+
3 rows in set

1. INNER JOIN

INNER JOIN is consistent with the general join table query , that is, query using commas.

mysql> SELECT a.`name`,a.age,b.address FROM name_age a INNER JOIN name_address b WHERE（on） a.`name`=b.`name`;
+------+-----+----------+
| name | age | address |
+------+-----+----------+
| 张三 | 18 | 西北一路 |
| 王五 | 20 | 西北三路 |
+------+-----+----------+
2 rows in set

2. LEFT JOIN

Subject to the data table on the left

mysql> SELECT a.`name`,a.age,b.address FROM name_age a left JOIN name_address b on
 a.`name`=b.`name`;
+--------+-----+----------+
| name  | age | address |
+--------+-----+----------+
| 张三  | 18 | 西北一路 |
| 王五  | 20 | 西北三路 |
| 路人甲 | 21 | NULL   |
+--------+-----+----------+
3 rows in set

3. RIGHT JOIN

is the opposite of LEFT JOIN, that is, the data on the right shall prevail

mysql> SELECT b.`name`,a.age,b.address FROM name_age a right JOIN name_address b on a.`name`=b.`name`;
+------+------+----------+
| name | age | address |
+------+------+----------+
| 张三 | 18  | 西北一路 |
| 王五 | 20  | 西北三路 |
| 李四 | NULL | 西北二路 |
+------+------+----------+
3 rows in set

Related recommendations:

The importance of indexes in MySQL connection queries

mysql connection query classic examples_MySQL

Classic mysql connection query example_MySQL

The above is the detailed content of Mysql connection query left join, right join, inner join example detailed explanation. For more information, please follow other related articles on the PHP Chinese website!