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

PostgreSQL

数据库

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

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

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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