mysql Join Query (Left Join Right Join Inner Join)

1. mysql Common Connections

INNER JOIN (Internal Join, or Equivalent Join): Gets a record of the matching relationship between fields in two tables. LEFT JOIN (Left Join): Gets all records in the left table, even if there are no corresponding matching records in the right table. RIGHT JOIN (right join): As opposed to LEFT JOIN, it is used to fetch all records in the right table, even if there is no corresponding matching record in the left table.

mysql> select * from name_address;
+----------+------+----+
| address | name | id |
+----------+------+----+
|  Northwest 1 Road  |  Zhang 3 | 1 |
|  Northwest 2 Road  |  Li 4 | 2 |
|  Northwest 3 Road  |  Wang 5 | 3 |
+----------+------+----+
3 rows in set

mysql> select * from name_age;
+-----+--------+----+
| age | name  | id |
+-----+--------+----+
| 18 |  Zhang 3  | 1 |
| 20 |  Wang 5  | 2 |
| 21 |  Passer-by A  | 3 |
+-----+--------+----+
3 rows in set

1. INNER JOIN

INNER JOIN and 1-like join table query 1, that is, using comma-separated query mode.

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 |
+------+-----+----------+
|  Zhang 3 | 18 |  Northwest 1 Road  |
|  Wang 5 | 20 |  Northwest 3 Road  |
+------+-----+----------+
2 rows in set

2. LEFT JOIN

The data table on the left shall prevail

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 |
+--------+-----+----------+
|  Zhang 3  | 18 |  Northwest 1 Road  |
|  Wang 5  | 20 |  Northwest 3 Road  |
|  Passer-by A  | 21 | NULL   |
+--------+-----+----------+
3 rows in set

3. RIGHT JOIN

Contrary to 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 |
+------+------+----------+
|  Zhang 3 | 18  |  Northwest 1 Road  |
|  Wang 5 | 20  |  Northwest 3 Road  |
|  Li 4 | NULL |  Northwest 2 Road  |
+------+------+----------+
3 rows in set

The above is the MySQL connection query information collation, if you have questions can leave a message to discuss, progress together, thank you for reading, hope to help you, thank you for your support to this site!