blocks|key|3243476|text|使用LEFT+JOIN|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3243477|SELECT++a.*
FROM++++table1+a
++++++++++++LEFT+JOIN+table2+b
++++++++++++++++on+a.ID+=+b.ID
WHERE+++b.id+IS+NULL|code-block|syntax|javascript|3243478|entityMap^0|2|9|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

<p>Use <code>LEFT JOIN</code></p>

<pre><code>SELECT  a.*
FROM    table1 a
            LEFT JOIN table2 b
                on a.ID = b.ID
WHERE   b.id IS NULL
</code></pre>


blocks|key|3456489|text|尝尝这个|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3456490|SELECT+ID,+Name+
FROM+++Table1+
WHERE++ID+NOT+IN+(SELECT+ID+FROM+Table2)|code-block|syntax|javascript|3456491|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

<p>Try this </p>

<pre><code>SELECT ID, Name 
FROM   Table1 
WHERE  ID NOT IN (SELECT ID FROM Table2)
</code></pre>


blocks|key|3456503|text|SELECT+COUNT(ID)+FROM+tblA+a
WHERE+a.ID+NOT+IN+(SELECT+b.ID+FROM+tblB+b)++++--For+count


SELECT+ID+FROM+tblA+a
WHERE+a.ID+NOT+IN+(SELECT+b.ID+FROM+tblB+b)++++--For+results|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|3456504|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>SELECT COUNT(ID) FROM tblA a
WHERE a.ID NOT IN (SELECT b.ID FROM tblB b)    --For count


SELECT ID FROM tblA a
WHERE a.ID NOT IN (SELECT b.ID FROM tblB b)    --For results
</code></pre>


blocks|key|408262|text|快速替代方案|type|unstyled|depth|inlineStyleRanges|entityRanges|data|408263|我运行了一些测试(在postgres+9.5上)，使用了两个表，每个表有大约2M行。以下查询的性能至少比建议的其他查询高出5*：|408264|--+Count
SELECT+count(*)+FROM+(
++++(SELECT+id+FROM+table1)+EXCEPT+(SELECT+id+FROM+table2)
)+t1_not_in_t2;

--+Get+full+row
SELECT+table1.*+FROM+(
++++(SELECT+id+FROM+table1)+EXCEPT+(SELECT+id+FROM+table2)
)+t1_not_in_t2+JOIN+table1+ON+t1_not_in_t2.id=table1.id;|code-block|syntax|javascript|408265|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

<h2>Fast Alternative</h2>

<p>I ran some tests (on postgres 9.5) using two tables with ~2M rows each. This query below performed at least 5* better than the other queries proposed: </p>

<pre><code>-- Count
SELECT count(*) FROM (
    (SELECT id FROM table1) EXCEPT (SELECT id FROM table2)
) t1_not_in_t2;

-- Get full row
SELECT table1.* FROM (
    (SELECT id FROM table1) EXCEPT (SELECT id FROM table2)
) t1_not_in_t2 JOIN table1 ON t1_not_in_t2.id=table1.id;
</code></pre>


blocks|key|410834|text|记住上面@John+Woo的评论/链接中提出的观点，我通常会这样处理：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|410835|SELECT+t1.ID,+t1.Name+
FROM+++Table1+t1
WHERE++NOT+EXISTS+(
++++SELECT+TOP+1+NULL
++++FROM+Table2+t2
++++WHERE+t1.ID+=+t2.ID
)|code-block|syntax|javascript|410836|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

<p>Keeping in mind the points made in @John Woo's comment/link above, this is how I typically would handle it:</p>

<pre class="lang-sql prettyprint-override"><code>SELECT t1.ID, t1.Name 
FROM   Table1 t1
WHERE  NOT EXISTS (
    SELECT TOP 1 NULL
    FROM Table2 t2
    WHERE t1.ID = t2.ID
)
</code></pre>


blocks|key|3243598|text|基本上有3种方法：not+exists，not+in和left+join+/+is+null。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3243599|左联接值为空|3243600|SELECT++l.*
FROM++++t_left+l
LEFT+JOIN
++++++++t_right+r
ON++++++r.value+=+l.value
WHERE+++r.value+IS+NULL|code-block|syntax|javascript|3243601|不在|3243602|SELECT++l.*
FROM++++t_left+l
WHERE+++l.value+NOT+IN
++++++++(
++++++++SELECT++value
++++++++FROM++++t_right+r
++++++++)|3243603|不存在|3243604|SELECT++l.*
FROM++++t_left+l
WHERE+++NOT+EXISTS
++++++++(
++++++++SELECT++NULL
++++++++FROM++++t_right+r
++++++++WHERE+++r.value+=+l.value
++++++++)|3243605|哪一个更好？这个问题的答案可能更好地分解到主要的特定RDBMS供应商。一般来说，当子查询中的记录数量未知时，应该避免使用select+...+where+...+in+(select...)。一些供应商可能会限制大小。例如，甲骨文就有一个limit+of+1,000。最好的做法是尝试所有这三种方法，并显示执行计划。|3243606|具体到PostgreSQL中，NOT+EXISTS和LEFT+JOIN+/+IS+NULL的执行计划是相同的。我个人更喜欢NOT+EXISTS选项，因为它显示了更好的意图。毕竟，语义是您希望在A中查找其pk+在B中不存在的记录。|BOLD|3243607|历史悠久但仍是金色的，虽然是PostgreSQL特有的：https://explainextended.com/2009/09/16/not-in-vs-not-exists-vs-left-join-is-null-postgresql/|3243608|entityMap|0|LINK|mutability|MUTABLE|url|http://www.dba-oracle.com/t_maximum_number_of_sql_in_list_values.htm|1|https://explainextended.com/2009/09/16/not-in-vs-not-exists-vs-left-join-is-null-postgresql/^0|9|A|K|6|R|J|0|0|0|0|0|0|0|1O|Z|3B|E|0|0|F|A|Q|J|1P|A|2W|6|0|S|2K|1|0^^$0|@$1|2|3|4|5|6|7|1B|8|@$9|1C|A|1D|B|C]|$9|1E|A|1F|B|C]|$9|1G|A|1H|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|1I|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|1J|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|1K|8|@]|D|@]|E|$]]|$1|O|3|P|5|J|7|1L|8|@]|D|@]|E|$K|L]]|$1|Q|3|R|5|6|7|1M|8|@]|D|@]|E|$]]|$1|S|3|T|5|J|7|1N|8|@]|D|@]|E|$K|L]]|$1|U|3|V|5|6|7|1O|8|@$9|1P|A|1Q|B|C]]|D|@$9|1R|A|1S|1|1T]]|E|$]]|$1|W|3|X|5|6|7|1U|8|@$9|1V|A|1W|B|C]|$9|1X|A|1Y|B|C]|$9|1Z|A|20|B|C]|$9|21|A|22|B|Y]]|D|@]|E|$]]|$1|Z|3|10|5|6|7|23|8|@]|D|@$9|24|A|25|1|26]]|E|$]]|$1|11|3|-4|5|6|7|27|8|@]|D|@]|E|$]]]|12|$13|$5|14|15|16|E|$17|18]]|19|$5|14|15|16|E|$17|1A]]]]

<p>There are basically 3 approaches to that: <code>not exists</code>, <code>not in</code> and <code>left join / is null</code>. </p>

<h2>LEFT JOIN with IS NULL</h2>

<pre><code>SELECT  l.*
FROM    t_left l
LEFT JOIN
        t_right r
ON      r.value = l.value
WHERE   r.value IS NULL
</code></pre>

<h2>NOT IN</h2>

<pre><code>SELECT  l.*
FROM    t_left l
WHERE   l.value NOT IN
        (
        SELECT  value
        FROM    t_right r
        )
</code></pre>

<h2>NOT EXISTS</h2>

<pre><code>SELECT  l.*
FROM    t_left l
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    t_right r
        WHERE   r.value = l.value
        )
</code></pre>

<p>Which one is better? The answer to this question might be better to be broken down to major specific RDBMS vendors. Generally speaking, one should avoid using <code>select ... where ... in (select...)</code> when the magnitude of number of records in the sub-query is unknown. Some vendors might limit the size. Oracle, for example, has a <a href="http://www.dba-oracle.com/t_maximum_number_of_sql_in_list_values.htm" rel="noreferrer">limit of 1,000</a>. Best thing to do is to try all three and show the execution plan. </p>

<p>Specifically form PostgreSQL, execution plan of <code>NOT EXISTS</code> and <code>LEFT JOIN / IS NULL</code> are the same. I personally prefer the <code>NOT EXISTS</code> option because it shows better the intent. After all the semantic is that <em>you want to find records in A that its pk <strong>do not exist</strong> in B</em>.</p>

<p>Old but still gold, specific to PostgreSQL though: <a href="https://explainextended.com/2009/09/16/not-in-vs-not-exists-vs-left-join-is-null-postgresql/" rel="noreferrer">https://explainextended.com/2009/09/16/not-in-vs-not-exists-vs-left-join-is-null-postgresql/</a></p>


<p>I have two tables with binding primary key in database and I desire to find a disjoint set between them. For example,</p>
<ul>
<li><code>Table1</code> has columns (<code>ID, Name</code>) and sample data: <code>(1 ,John), (2, Peter), (3, Mary)</code></li>
<li><code>Table2</code> has columns (<code>ID, Address</code>) and sample data: <code>(1, address2), (2, address2)</code></li>
</ul>
<p>So how do I create a SQL query so I can fetch the row with ID from <code>table1</code> that is not in <code>table2</code>. In this case, <code>(3, Mary)</code> should be returned?</p>
<p><strong>PS:</strong> The ID is the primary key for those two tables.</p>


SQL query to find record with ID not in another table

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我在数据库中有两个具有绑定主键的表，我希望在它们之间找到一个不相交的集合。例如,Table1有列(ID, Address)和样本数据：(1, address2), (2, address2)有列(ID, Name)和样本数据那么，如何创建一个SQL查询，以便可以从不在table2中的table1中提取ID为的行。在这种...

问用于查找ID不在另一个表中的记录的SQL查询
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问用于查找ID不在另一个表中的记录的SQL查询EN