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)

 

 

    一目了然，不必多说。