如何排除与其他表不连接的行?

I have two tables, one has primary key other has it as a foreign key.

我有两个表，一个是主键，另一个是外键。

I want to pull data from the primary table, only if the secondary table does not have an entry containing it's key. Sort of an opposite of a simple inner join, which returns only rows that join together by that key.

我想从主表中提取数据，只有当辅助表没有包含它的键的条目时。类似于简单的内部连接，它只返回由该键连接在一起的行。

6 个解决方案

#1

SELECT <select_list> 
FROM Table_A A
LEFT JOIN Table_B B
ON A.Key = B.Key
WHERE B.Key IS NULL

Full image of join

完整的形象加入

From aticle : http://www.codeproject.com/KB/database/Visual_SQL_Joins.aspx

从得出:http://www.codeproject.com/KB/database/Visual_SQL_Joins.aspx

#2

SELECT
   *
FROM
   primarytable P
WHERE
   NOT EXISTS (SELECT * FROM secondarytable S
     WHERE
         P.PKCol = S.FKCol)

Generally, (NOT) EXISTS is a better choice then (NOT) IN or (LEFT) JOIN

通常，(不)存在比(不)在或(左)加入更好

#3

use a "not exists" left join:

使用“不存在”左连接:

SELECT p.*
FROM primary_table p LEFT JOIN second s ON p.ID = s.ID
WHERE s.ID IS NULL

#4

SELECT P.*
FROM primary_table P
LEFT JOIN secondary_table S on P.id = S.p_id
WHERE S.p_id IS NULL

#5

If you want to select the columns from First Table "which are also present in Second table, then in this case you can also use EXCEPT. In this case, column names can be different as well but data type should be same.

如果您想要从第一个表中选择列，而第二个表中也有列，那么在这种情况下，您也可以使用EXCEPT。在这种情况下，列名也可以不同，但是数据类型应该相同。

Example:

例子:

select ID, FName
from FirstTable
EXCEPT
select ID, SName
from SecondTable

#6

This was helpful to use in COGNOS because creating a SQL "Not in" statement in Cognos was allowed, but it took too long to run. I had manually coded table A to join to table B in in Cognos as A.key "not in" B.key, but the query was taking too long/not returning results after 5 minutes.

这有助于在COGNOS中使用，因为在COGNOS中创建SQL“Not in”语句是允许的，但是运行时间太长了。我手工编写了表A，将它作为A加入到Cognos中的表B中。关键的”而不是“B。关键字，但查询时间太长/ 5分钟后没有返回结果。

For anyone else that is looking for a "NOT IN" solution in Cognos, here is what I did. Create a Query that joins table A and B with a LEFT JOIN in Cognos by selecting link type: table A.Key has "0 to N" values in table B, then added a Filter (these correspond to Where Clauses) for: table B.Key is NULL.

对于其他在Cognos中寻找“不在”解决方案的人，我做了以下工作。通过选择link类型:table a，在Cognos中创建一个将表a和表B左连接的查询。Key在表B中有“0到N”的值，然后为表B添加了一个过滤器(这些过滤器对应于Where子句)。关键是NULL。

Ran fast and like a charm.

跑得飞快，像个魔咒一样。