如何获取表1中存在但表2中不存在的记录

Question

我有两个表:excluded和kagglerresults。我试图找到excluded中存在但kaggleresults中不存在的记录

数量:

scala> spark.sql("select * from excluded").count()
res136: Long = 4652
scala> spark.sql("select * from kaggleresults").count()
res137: Long = 4635

差异为17

scala> res136-res137
res139: Long = 17

我想要那17张唱片。我写了下面的查询，但它返回38代替。

scala> spark.sql("select * from excluded left join kaggleresults on kaggleresults.subject_id = excluded.subject_id where kaggleresults.subject_id is null").count()
res135: Long = 38

我需要写什么查询来获得这17条记录?

Answer 1

这不是LEFT_ANTI join的情况吗?

scala> val excluded = (0 to 5).toDS
left: org.apache.spark.sql.Dataset[Int] = [value: int]
scala> val kaggleresults = (3 to 10).toDS
right: org.apache.spark.sql.Dataset[Int] = [value: int]
scala> excluded.join(kaggleresults, Seq("value"), "leftanti").show
+-----+
|value|
+-----+
|    0|
|    1|
|    2|
+-----+

Answer 2

对于实际记录(而不是计数)，您可以使用

SELECT * FROM excluded
WHERE subject_id NOT IN ( SELECT subject_id FROM kaggleresults )

但是，如果结果行计数与两个表的单独行计数之差不匹配，您不应该感到惊讶。

例如:假设table1的id为1、2、3、4和5,table2的id为3、4、5和6。表1和表2的行数分别为5和4，差值为1，但实际上在表1中有两条记录的id在表2中不存在。

Answer 3

使用Not In语法和子查询。

SELECT ID, Name 
FROM   Table1 
WHERE  ID NOT IN (SELECT ID FROM Table2)

Answer 4

使用not in关键字您可以使用下面的查询

Select * from excluded where subject_id not in (select subject_id from  kaggleresults)