1 两表join时要小表驱动大表,为什么?
user表10000条数据,class表20条数据
select * from user u left join class c on u.userid=c.userid;
上面的结果是循环10000次,每次获取user的userid到class中找与user表userid相同的记录
,但是如果写成下面的sql,仅仅需要循环20次
select * from class c left join user u on u.userid=c.userid;
怎么知道谁驱动谁
left和right的正确理解: left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录。right join返回包括又表中的所有记录和坐标中连接字段相等的记录
right和left不影响谁驱动谁,即:
a left join b和a right join b都是a表驱动b表;所以如果a相对b表较小,基于小表驱动大表的原则 ,推荐上面两种写法
2 正确理解join后面的on条件
下面四个sql的结果和效率区分,a 1千条,b 1万条记录
1)select a.col1,a.col2 from a,b where a.uid=b.uid and a.col1='col1';
2)select a.col1,a.col2 from a inner join b on a.uid=b.uid and a.col1='col1';
注意2是inner join:1和2输出结果一样,并且查询效率也一样,上面两个sql完全等价;
3)select a.col1,a.col2 from a left join b on a.uid=b.uid and a.col1='col1';
3和2的区别是3的结果会更多,多的是a.uid存在但b.uid不存在的部分
4)select a.col1,a.col2 from a left join b where a.uid=b.uid and a.col1='col1';
4和3,都要做笛卡尔积,这里3中on的含义:
以a.uid的值为标准,如果b表uid没有对应的值,则直接返回a的内容并且不做a.col1='col1'的验证;
如果b.uid有值,那么会进一步要求a.col1='col1';
4中where条件是在临时表生成好后,再对临时表进行过滤的条件,条件不为真的就全部过滤掉,所以a表存在但b表不存在的并不会返回,所以where的结果会更少
如:
table a(id, type):
id type
----------------------------------
1 1
2 1
3 2
table b(id, class):
id class
---------------------------------
1 1
2 2
sql语句1:select a.*, b.* from a left join b on a.id = b.id and a.type = 1;
sql语句2:select a.*, b.* from a left join b on a.id = b.id where a.type = 1;
sql语句1的执行结果为:
a.id a.type b.id b.class
----------------------------------------
1 1 1 1
2 1 2 2
3 2
sql语句2的执行结果为:
a.id a.type b.id b.class
----------------------------------------
1 1 1 1
2 1 2 2
所以sql1中的条件a.type=1只有在a、b两表记录能对应的时候(既满足 a.id = b.id )才会生效。注意sql语句1中返回的 3 2
a.id = b.id and a.type = 1两个条件都不满足