mysql的inner join, left join, right join
0. 先看表数据：
| mysql> select * from a; | |
|---|---|
| id | age |
| 1 | 10 |
| 2 | 20 |
| 3 | 30 |
| 4 | 40 |
4 rows in set (0.00 sec)
| mysql> select * from b; | |
|---|---|
| id | score |
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
| 5 | 500 |
4 rows in set (0.00 sec)
1. inner join最简单，我们之前说过， 来看下：
| mysql> select * from a inner join b; | |||
|---|---|---|---|
| id | age | id | score |
| 1 | 10 | 1 | 100 |
| 2 | 20 | 1 | 100 |
| 3 | 30 | 1 | 100 |
| 4 | 40 | 1 | 100 |
| 1 | 10 | 2 | 200 |
| 2 | 20 | 2 | 200 |
| 3 | 30 | 2 | 200 |
| 4 | 40 | 2 | 200 |
| 1 | 10 | 3 | 300 |
| 2 | 20 | 3 | 300 |
| 3 | 30 | 3 | 300 |
| 4 | 40 | 3 | 300 |
| 1 | 10 | 5 | 500 |
| 2 | 20 | 5 | 500 |
| 3 | 30 | 5 | 500 |
| 4 | 40 | 5 | 500 |
16 rows in set (0.00 sec)
可以用on/where过滤下：
| mysql> select * from a inner join b on a.id = b.id; | |||
|---|---|---|---|
| id | age | id | score |
| 1 | 10 | 1 | 100 |
| 2 | 20 | 2 | 200 |
| 3 | 30 | 3 | 300 |
3 rows in set (0.00 sec)
| mysql> select * from a inner join b where a.id = b.id; | |||
|---|---|---|---|
| id | age | id | score |
| 1 | 10 | 1 | 100 |
| 2 | 20 | 2 | 200 |
| 3 | 30 | 3 | 300 |
3 rows in set (0.00 sec)
之所以可以用where, 是因为where之前本身就有结果。
2. 再看left join:
mysql> select * from a left join b;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
所以很显然不能用where:
mysql> select * from a left join b where a.id = b.id;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where a.id = b.id' at line 1
只能用on:
| mysql> select * from a left join b on a.id = b.id; | |||
|---|---|---|---|
| id | age | id | score |
| 1 | 10 | 1 | 100 |
| 2 | 20 | 2 | 200 |
| 3 | 30 | 3 | 300 |
| 4 | 40 | NULL | NULL |
4 rows in set (0.00 sec)
3. right join和left join类似，来看看right join的结果：
mysql> select * from a right join;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
mysql> select * from a right join where a.id = b.id;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where a.id = b.id' at line 1
| mysql> select * from a right join b on a.id = b.id; | |||
|---|---|---|---|
| id | age | id | score |
| 1 | 10 | 1 | 100 |
| 2 | 20 | 2 | 200 |
| 3 | 30 | 3 | 300 |
| NULL | NULL | 5 | 500 |
4 rows in set (0.00 sec)
一目了然，不必多说。
作者：stpeace
来源：CSDN
原文：https://blog.csdn.net/stpeace/article/details/91507571
版权声明：本文为博主原创文章，转载请附上博文链接！
