mysql的inner join, left join, right join

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;
	score
	100
	200
	300
5	500

     1. inner join最简单，我们之前说过， 来看下：

mysql> select * from a inner join b;

16 rows in set (0.00 sec)

    可以用on/where过滤下：

mysql> select * from a inner join b on a.id = b.id;

3 rows in set (0.00 sec)

mysql> select * from a inner join b where a.id = b.id;

      之所以可以用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;
		NULL

     3. right join和left join类似，来看看right join的结果：

mysql> select * from a right join;

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;

    一目了然，不必多说。

作者：stpeace

来源：CSDN

原文：

https://blog.csdn.net/stpeace/article/details/91507571

版权声明：本文为博主原创文章，转载请附上博文链接！