mysql的inner join, left join, right join

  1. 云栖社区>
  2. 博客>
  3. 正文

mysql的inner join, left join, right join

优惠码发放 2019-07-03 18:52:21 浏览409
展开阅读全文

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
版权声明:本文为博主原创文章,转载请附上博文链接!

网友评论

作者关闭了评论
优惠码发放
+ 关注