29.Mysql之join(left join and right join)浅谈

1.前言

　　在Mysql中多表关联查询一般我们会经常遇到，因此这里会简单的浅谈一下join操作，其中包括left join  、 right join  、inner join等操作

2.操作

　　首先这里有两种表如下：

root@localhost 21:16:  [liulin]> select * from t2;
+----+--------+
| id | kemu   |
+----+--------+
|  1 | 语文   |
|  2 | 数学   |
|  3 | 英语   |
+----+--------+
3 rows in set (0.00 sec)


root@localhost 21:16:  [liulin]> select * from t3;
+----+-------+
| id | score |
+----+-------+
|  2 |    60 |
|  3 |    70 |
+----+-------+

• select * from t2 left join t3 on t2.id=t3.id;

root@localhost 21:14:  [liulin]> select * from t2 left join t3 on t2.id=t3.id;
+----+--------+------+-------+
| id | kemu   | id   | score |
+----+--------+------+-------+
|  2 | 数学   |    2 |    60 |
|  3 | 英语   |    3 |    70 |
|  1 | 语文   | NULL |  NULL |
+----+--------+------+-------+
3 rows in set (0.00 sec)

• select * from t2 right join t3 on t2.id=t3.id;

root@localhost 21:20:  [liulin]> select * from t2 right join t3 on t2.id=t3.id;
+------+--------+----+-------+
| id   | kemu   | id | score |
+------+--------+----+-------+
|    2 | 数学   |  2 |    60 |
|    3 | 英语   |  3 |    70 |
+------+--------+----+-------+

• select * from t2  inner join t3 on t2.id=t3.id;

root@localhost 21:26:  [liulin]> select * from t2  join t3 on t2.id=t3.id;
+----+--------+----+-------+
| id | kemu   | id | score |
+----+--------+----+-------+
|  2 | 数学   |  2 |    60 |
|  3 | 英语   |  3 |    70 |
+----+--------+----+-------+

主要前面都是t2在前面而t3表在后面的情况，接下来是t3在前面而t2在后面的情况

• select * from t3 left join t2 on t3.id=t2.id;

root@localhost 21:30:  [liulin]> select * from t3 left join t2 on t3.id=t2.id;
+----+-------+------+--------+
| id | score | id   | kemu   |
+----+-------+------+--------+
|  2 |    60 |    2 | 数学   |
|  3 |    70 |    3 | 英语   |

• select * from t3 right join t2 on t3.id=t2.id;

root@localhost 21:30:  [liulin]> select * from t3 right join t2 on t3.id=t2.id;
+------+-------+----+--------+
| id   | score | id | kemu   |
+------+-------+----+--------+
|    2 |    60 |  2 | 数学   |
|    3 |    70 |  3 | 英语   |
| NULL |  NULL |  1 | 语文   |
+------+-------+----+--------+

• select * from t3 inner join t2 on t3.id=t2.id;

root@localhost 21:35:  [liulin]> select * from t3 inner join t2  on t3.id=t2.id;
+----+-------+----+--------+
| id | score | id | kemu   |
+----+-------+----+--------+
|  2 |    60 |  2 | 数学   |
|  3 |    70 |  3 | 英语   |
+----+-------+----+--------+

参考：

       https://segmentfault.com/a/1190000017369618    这篇文章有图解！