从一个表中选择记录,这些记录在另一个表中不存在 [英] Select records from a table, which don't exist in another table
本文介绍了从一个表中选择记录,这些记录在另一个表中不存在的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我在sqlite DB中有2个表TABLE1
和TABLE2
.表2具有TABLE1的一些记录.我想要的是从表1中选择所有记录,这些记录在表2中不存在.所以我编码:
I have 2 tables TABLE1
and TABLE2
in sqlite DB. TABLE 2 has some of the records of the TABLE1. What I want is to select all the records from TABLE 1, which don't exist in TABLE2. So I coded:
String sq = "SELECT TABLE1.name, TABLE1.surname, TABLE1.id FROM TABLE1"
+ "LEFT JOIN TABLE1"
+ "ON TABLE1.id <> TABLE2.id";
但是此查询返回的内容我没有得到..哪个是正确的查询?
However this query returns something that i don't get it.. Which is the right query?
推荐答案
在第二个表中检查NULL
,例如:
Check for NULL
s in second table like:
SELECT TABLE1.name, TABLE1.surname, TABLE1.id
FROM TABLE1
LEFT JOIN TABLE2 ON TABLE1.id = TABLE2.id
WHERE TABLE2.id IS NULL
带有NOT EXISTS
的替代解决方案:
SELECT TABLE1.name, TABLE1.surname, TABLE1.id
FROM TABLE1
WHERE NOT EXISTS(SELECT * FROM TABLE2 WHERE TABLE1.id = TABLE2.id)
再加上NOT IN
:
SELECT TABLE1.name, TABLE1.surname, TABLE1.id
FROM TABLE1
WHERE TABLE1.id NOT IN(SELECT id FROM TABLE2)
这篇关于从一个表中选择记录,这些记录在另一个表中不存在的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文