SQL 如何排除不与另一个表连接的行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4560471/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to exclude rows that don't join with another table?
提问by Chaddeus
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 nothave 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.
我想从主表中提取数据，只有当辅助表没有包含它的键的条目时。与简单的内部连接相反，它只返回通过该键连接在一起的行。
回答by Pranay Rana
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
回答by gbn
SELECT
*
FROM
primarytable P
WHERE
NOT EXISTS (SELECT * FROM secondarytable S
WHERE
P.PKCol = S.FKCol)
Generally, (NOT) EXISTSis a better choice then (NOT) INor (LEFT) JOIN
一般来说，(NOT) EXISTS是更好的选择，然后(NOT) IN或(LEFT) JOIN
回答by The Scrum Meister
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
回答by Tommi
SELECT P.*
FROM primary_table P
LEFT JOIN secondary_table S on P.id = S.p_id
WHERE S.p_id IS NULL
回答by Ali Akbar
Another solution is:
另一种解决方案是：
SELECT * FROM TABLE1 WHERE id NOT IN (SELECT id FROM TABLE2)
回答by Anil Soman
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
回答by JennyB
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 以加入 Cognos 中的表 B 作为 A.key "not in" B.key，但查询耗时太长/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 中寻找“NOT IN”解决方案的其他人，这就是我所做的。通过选择链接类型，在 Cognos 中创建一个使用 LEFT JOIN 连接表 A 和 B 的查询：表 A.Key 在表 B 中具有“0 到 N”值，然后为：表 B 添加过滤器（这些对应于 Where 子句） .Key 为 NULL。
Ran fast and like a charm.
跑得很快，就像一个魅力。
