理解mysql中的join

深碍√TFBOYSˉ_ 2024-04-17 06:25 77阅读 0赞

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和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两个条件都不满足