mysql的左、右外连接（多个相同字段）

fusm1008

1、原始的两张表
mysql> select * from t1;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 1  | zsb  | 32  |
| 2  | fusm | 31  |
| 3  | zjx  | 3   |
+----+------+-----+
3 rows in set

mysql> select * from t2;
+----+-------+-------+
| id | name  | grade |
+----+-------+-------+
| 1  | zsb2  | 11    |
| 2  | fusm2 | 22    |
| 4  | fusq2 | 44    |
| 5  | fusq2 | 55    |
+----+-------+-------+
4 rows in set
2、左外连接
mysql> select t1.id,t1.name,age,t2.id,t2.name,grade from t1 left join t2 on t1.id=t2.id;
+----+------+-----+------+-------+-------+
| id | name | age | id   | name  | grade |
+----+------+-----+------+-------+-------+
| 1  | zsb  | 32  | 1    | zsb2  | 11    |
| 2  | fusm | 31  | 2    | fusm2 | 22    |
| 3  | zjx  | 3   | NULL | NULL  | NULL  |
+----+------+-----+------+-------+-------+
3 rows in set
3、右外连接
mysql> select t1.id,t1.name,age,t2.id,t2.name,grade from t1 right join t2 on t1.id=t2.id;
+------+------+------+----+-------+-------+
| id   | name | age  | id | name  | grade |
+------+------+------+----+-------+-------+
| 1    | zsb  | 32   | 1  | zsb2  | 11    |
| 2    | fusm | 31   | 2  | fusm2 | 22    |
| NULL | NULL | NULL | 4  | fusq2 | 44    |
| NULL | NULL | NULL | 5  | fusq2 | 55    |
+------+------+------+----+-------+-------+
4 rows in set
4、内连接
mysql> select t1.id,t1.name,age,t2.id,t2.name,grade from t1 inner join t2 on t1.id=t2.id;
+----+------+-----+----+-------+-------+
| id | name | age | id | name  | grade |
+----+------+-----+----+-------+-------+
| 1  | zsb  | 32  | 1  | zsb2  | 11    |
| 2  | fusm | 31  | 2  | fusm2 | 22    |
+----+------+-----+----+-------+-------+
2 rows in set

丝路

谢谢分享！

水晶叶子

多谢