从一个表中选择记录,这些记录在另一个表中不存在 [英] Select records from a table, which don't exist in another table

查看:90
本文介绍了从一个表中选择记录,这些记录在另一个表中不存在的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我在sqlite DB中有2个表TABLE1TABLE2.表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 NULLs 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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆