MYSQL LETT/RIGHT/INNER/FUll JOIN 注意事项

 

 1、JOIN ON .....AND 这个and是在连表时起到筛选作用，不作为连表后的整体筛选条件，如下：

　　

select count(*) from 
dmp_company_count 
LEFT JOIN (select * from dmp_data_company where functional=17) dmp_data_company ON dmp_company_count.keyno = dmp_data_company.keyno AND dmp_company_count.patent!=0
LEFT JOIN dmp_company A ON dmp_data_company.keyno=A.keyno 
LEFT JOIN dmp_functional ON dmp_functional.id = dmp_data_company.functional 
WHERE A.type= 1 and A.st= 1

 

2、将连表条件放到 where 关键词后面时是整体筛选，不要搞迷了

select count(*) from 
dmp_company_count 
LEFT JOIN (select * from dmp_data_company where functional=17) dmp_data_company ON dmp_company_count.keyno = dmp_data_company.keyno 
LEFT JOIN dmp_company A ON dmp_data_company.keyno=A.keyno 
LEFT JOIN dmp_functional ON dmp_functional.id = dmp_data_company.functional 
WHERE A.type= 1 and A.st= 1 AND dmp_company_count.patent!=0