源址:http://bbs.csdn.net/topics/390741161
先说明2点:
1、left join是逻辑操作符,只用于保证“数据正确”
2、优化器才是最终决定执行顺序的地方,一般来说会按照你的意愿顺序去执行,但是并不完全能保证。
回答你的问题:
1. 求教多个left join 怎么执行
eg:
select * from a left join b on a.abid = b.baid left join c on c.cbid = b.bcid
顺序是先a,b组合成一个虚拟表,然后虚拟表再和C表关联
2.多个left join连接,记录条数是不是主表的条数
eg
select * from a
left join b on a.abid = b.baid
left join c on c.cbid = b.bcid
...
没有where语句 那么查询出来的结果集条数是不是等于a的记录条数
左联的意思是返回左表的数据,右表中不能匹配出来的数据以null标识,但是由于你C表是和B左联而不是和A左联,所以数量不一定是A的数量,看清楚,不一定而已
即使是A LOJ B的结果,记录条数也不一定等于A的记录条数,除非join predicate右边(也就是B表)的列是唯一的。
多个left join查询示例(b表id不是唯一)
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>