查找不在另一个表中的多个字段的记录 [英] Find records on multiple fields not in another table

问题描述

假设我有 2 个表(tb1、tb2)，它们都具有以下架构:

Let's say I have 2 tables (tb1, tb2) with both the following schema:

CREATE TABLE tb1 (
  col1 INT NOT NULL,
  col2 TEXT NOT NULL,
  col3 TEXT NOT NULL,
  col4 REAL
);

如何在col1、col2列的tb2中查找tb1的记录，col3?

How do I find records of tb1 which are not present in tb2 on columns col1, col2, col3?

我研究了 这个，this 和 this 但到目前为止，他们都只在一列上查找记录.我还使用了这些链接中的代码/逻辑，但最终返回了错误的结果，性能非常差(tb1 上有 45K 记录，tb2 上有 170 万条记录).我正在尝试在 SQLite 上实现这一点.

I researched on this, this and this but so far they're all finding records only on one column. I've also used the codes/logic in these links but ended up returning the wrong result with really bad performance (45K records on tb1, 1.7M records on tb2). I'm trying to implement this on SQLite.

如果你想看，这是我的示例代码(使用左连接w/where is null)，但不要依赖它:

If you wanna see, here's my sample code (using left join w/ where is null), but don't rely on it:

SELECT *
FROM tb1
LEFT JOIN tb2
ON
tb1.col1 = tb2.col1 AND
tb1.col2 = tb2.col2 AND
tb1.col3 = tb2.col3
WHERE
tb2.col1 IS NULL AND
tb2.col2 IS NULL AND
tb2.col3 IS NULL

推荐答案

尝试 NOT EXISTS 代替，当然性能可能取决于现有索引...

Try NOT EXISTS instead, of course performance might depend on existing indexes...

SELECT *
FROM tb1
WHERE NOT EXISTS
 ( 
   SELECT *
   FROM tb2
   WHERE
      tb1.col1 = tb2.col1 AND
      tb1.col2 = tb2.col2 AND
      tb1.col3 = tb2.col3
 ) 

这篇关于查找不在另一个表中的多个字段的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！