sql：多个left join 执行

最新推荐文章于 2023-10-18 11:33:23 发布

xinyihhh

最新推荐文章于 2023-10-18 11:33:23 发布

阅读量2.1k

点赞数 2

分类专栏：数据库文章标签： sql

原文链接：https://blog.csdn.net/pasken/article/details/49098421

版权

数据库专栏收录该内容

4 篇文章 1 订阅

订阅专栏

例如：

select * from a left join b on a.abid = b.baid left join c on c.cbid = b.bcid

顺序是先a,b组合成一个虚拟表，然后虚拟表再和c表关联

mysql> select * from a;
+----+------+
| id | name |
+----+------+
| 1  | a1   |
| 2  | a2   |
| 3  | a3   |
+----+------+
3 rows in set
 
mysql> select * from b;
+----+------+
| id | name |
+----+------+
| 1  | b1   |
| 2  | b2   |
| 2  | b3   |
+----+------+
3 rows in set
 
mysql> select * from c;
+----+------+-------+
| id | name | name2 |
+----+------+-------+
| 1  | c11  | c12   |
| 2  | c21  | c22   |
| 3  | c31  | c32   |
+----+------+-------+
3 rows in set
 
mysql> select a.*,b.* from a left join b on a.id=b.id ;
+----+------+------+------+
| id | name | id   | name |
+----+------+------+------+
| 1  | a1   | 1    | b1   |
| 2  | a2   | 2    | b2   |
| 2  | a2   | 2    | b3   |
| 3  | a3   | NULL | NULL |
+----+------+------+------+
4 rows in set
 
mysql> select a.*,b.*,c.* from a left join b on a.id=b.id left join c on b.id=c.id;
+----+------+------+------+------+------+-------+
| id | name | id   | name | id   | name | name2 |
+----+------+------+------+------+------+-------+
| 1  | a1   | 1    | b1   | 1    | c11  | c12   |
| 2  | a2   | 2    | b2   | 2    | c21  | c22   |
| 2  | a2   | 2    | b3   | 2    | c21  | c22   |
| 3  | a3   | NULL | NULL | NULL | NULL | NULL  |
+----+------+------+------+------+------+-------+
4 rows in set
 
mysql>