1)区别:左表 left join 右表 on/where
left join 是以左表为主,on是不管条件满不满足,都会保留左表的内容,而where,则是将不满足条件的过滤掉了;
2)左表 left join 右表 on 的时候,左表和右表都必须去重,并且都必须保证on条件后面用到的列不会为null,否则,你left join得到的样本数永远不会是左表样本数,得到一个奇怪的数。特别注意,left join 对两个表的要求比较高:去重,无null值;
select * from student join takes on students.ID = takes.ID与
select × from student,takes where students.ID = takes.ID 等价;
但与select * from student join takes where students.ID = takes.ID 有区别
dfh71 = sqlContext.sql("select h7._c0 as company_name,h6.company_name as add_company_name from h7 left join h6 on h7._c0 = h6.company_name ")
dfh71.createOrReplaceTempView('h71')
dfh72 = sqlContext.sql("select h71.company_name from h71 where add_company_name != company_name ")
dfh72.repartition(1).write.csv(os.path.join(myfeature_path, "temp_the_lost_shixin_features.csv"),
mode='overwrite', header=True)
dfh72 = sqlContext.sql("select h71.company_name from h71 where add_company_name != company_name ")
语句用 不等于 !=最后得到个空表;需要用add_company_name is null