mysql join query (left join, right join, inner join)

1. Common connections for mysql

INNER JOIN (inner join, or equijoin): Get records with matching fields in two tables.
LEFT JOIN: Get all records in the left table, even if there are no corresponding matching records in the right table.
RIGHT JOIN: The opposite of LEFT JOIN, 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 |
+----------+------+----+
| Northwest Road | Zhang San | 1 |
| Northwest Second Road | Li Si | 2 |
| Northwest Third Road | Wang Wu | 3 |
+----------+------+----+
3 rows in set

mysql> select * from name_age;
+-----+--------+----+
| age | name | id |
+-----+--------+----+
| 18 | Zhang San | 1 |
| 20 | Wang Wu | 2 |
| 21 | Passerby | 3 |
+-----+--------+----+
3 rows in set

1. INNER JOIN

INNER JOIN is consistent with general table join query, that is, the query method is separated by 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 |
+------+-----+----------+
| Zhang San | 18 | Northwest Road |
| Wang Wu| 20 | Northwest Third Road|
+------+-----+----------+
2 rows in set

2. LEFT JOIN

Please refer 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 |
+--------+-----+----------+
| Zhang San | 18 | Northwest Road |
| Wang Wu| 20 | Northwest Third Road|
| Passerby| 21 | NULL |
+--------+-----+----------+
3 rows in set

3. RIGHT JOIN

The opposite of LEFT JOIN, that is, the data on the right is used as the basis

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 San | 18 | Northwest Road |
| Wang Wu| 20 | Northwest Third Road|
| Li Si | NULL | Northwest Second Road |
+------+------+----------+
3 rows in set

The above is the information compilation of MySQL connection query. If you have any questions, you can leave a message to discuss and make progress together. Thank you for reading and hope to help everyone. Thank you for your support of this site!

You may also be interested in: