mysql的inner join, left join, right join

最新推荐文章于 2025-06-30 14:53:57 发布

原创最新推荐文章于 2025-06-30 14:53:57 发布 · 7.2k 阅读

25 ·

CC 4.0 BY-SA版权

s2: 软件进阶同时被 2 个专栏收录

1053 篇文章

订阅专栏

s4: 数据库

101 篇文章

订阅专栏

本文深入解析SQL中的三种连接类型：inner join、left join和right join的使用方法与区别。通过具体示例，展示了如何利用on和where子句来精确控制连接结果，帮助读者更好地理解和运用SQL连接。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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)

一目了然，不必多说。