SQL1
SELECT
a.AptOrgNum,
a.iocValue,
a.iocType,
b.ReportName,
b.ReportPublishTime,
b.ReportPubOrg
FROM
extract_ioc_infor_table a
LEFT JOIN extract_report_table b ON (a.sourceUrl = b.pdf_file and a.sourceUrl !='' and b.pdf_file !='' )
SQL2
SELECT
a.AptOrgNum,
a.iocValue,
a.iocType,
b.ReportName,
b.ReportPublishTime,
b.ReportPubOrg
FROM
extract_ioc_infor_table a
LEFT JOIN extract_report_table b ON a.sourceUrl = b.pdf_file
WHERE
a.sourceUrl != ''
AND b.pdf_file != ''
SQL3 不做过滤空值
SELECT
a.AptOrgNum,
a.iocValue,
a.iocType,
b.ReportName,
b.ReportPublishTime,
b.ReportPubOrg
FROM
extract_ioc_infor_table a
LEFT JOIN extract_report_table b ON a.sourceUrl = b.pdf_file
执行结果
SQL1:1543888条
SQL2:85756条
SQL3:1543888条
结论
1.对于a表 left join b表 ,过滤条件在on中:忽略a表的过滤条件,b表的过滤条件只会导致不满足条件的记录在b表的所有字段上都为空;过滤条件在where中:过滤掉所有不满足条件的记录。
2.left join时on中条件无过滤记录作用,后做where条件过滤记录。on 优先级高于 where