在 SQL 语句中使用子查询的替代方法？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5639744/
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
Alternative to using subqueries in SQL statements?
提问by Swati
I have two tables:
我有两个表：
TableA: (a temporary table)
ItemId (int)
TableB:
ItemId (int), ParentID (int)
I want to retrieve all items in Table A where the ParentID of any of the items in Table A doesn't exist as an ItemID. (i.e. I want to get the root of the items in TableA)
我想检索表 A 中的所有项目，其中表 A 中任何项目的 ParentID 都不作为 ItemID 存在。（即我想获取 TableA 中项目的根）
This query does what I want:
这个查询做我想要的：
SELECT a.ItemID
FROM TableA a
INNER JOIN TableB b ON a.ItemId = b.ItemID
WHERE b.ParentID NOT IN ( SELECT * from TableA )
as does this one:
就像这个：
SELECT b.ItemID
FROM TableB b
WHERE b.ItemID IN ( SELECT * FROM TableA)
AND b.ParentID NOT IN ( SELECT * FROM TableA )
I am not satisfied with either of the queries, particularly because of the use of NOT IN/IN. Is there a way to do this without them? Perhaps a cleaner way that doesn't require subqueries?
我对任何一个查询都不满意，特别是因为使用了 NOT IN/IN。没有它们，有没有办法做到这一点？也许一种不需要子查询的更简洁的方式？
Sample Data:
样本数据：
Table A
-------
2
3
5
6
Table B
--------
1 | NULL
2 | 1
3 | 1
4 | 3
5 | 3
6 | 3
Desired Result:
预期结果：
2
3
Thanks
谢谢
回答by SQLMenace
Take a look at Select all rows from one table that don't exist in another tableto see 5 different ways to do this kind of query by using
看一看Select all rows from one table that don't exist in another table以查看 5 种不同的方法来执行此类查询
NOT IN
NOT EXISTS
LEFT and RIGHT JOIN
OUTER APPLY (2005+)
EXCEPT (2005+)
NOT IN
NOT EXISTS
LEFT 和 RIGHT JOIN
OUTER APPLY (2005+)
EXCEPT (2005+)
Here is a script that you can run
这是您可以运行的脚本
CREATE TABLE #TableA( ItemId int)
INSERT #TableA values(1)
INSERT #TableA values(2)
INSERT #TableA values(3)
INSERT #TableA values(4)
INSERT #TableA values(5)
INSERT #TableA values(6)
CREATE TABLE #TableB( ItemId int, ParentID int)
INSERT #TableB values(1,1)
INSERT #TableB values(2,2)
INSERT #TableB values(4,3)
INSERT #TableB values(5,4)
this will do it for parent
这将为父母做
SELECT a.ItemID
FROM #TableA a
LEFT JOIN #TableB b ON a.ItemId = b.ParentID
WHERE b.ItemID IS NULL
SELECT a.ItemID
FROM #TableA a
WHERE NOT EXISTS (SELECT 1 FROM #TableB b WHERE a.ItemId = b.ParentID)
Output
输出
ItemID
5
6
回答by onedaywhen
Without subqueries:
没有子查询：
SELECT ItemID
FROM TableA
INTERSECT
SELECT b.ItemID
FROM TableB AS b
LEFT OUTER JOIN TableA AS a
ON b.ParentID = a.ItemID
WHERE a.ItemID IS NULL;
...but is your fear of subqueries rational? :) I'd find this equivalent query easier to read and understand:
...但是你对子查询的恐惧是理性的吗？:) 我会发现这个等效的查询更容易阅读和理解：
SELECT ItemID
FROM TableA
INTERSECT
SELECT ItemID
FROM TableB
WHERE NOT EXISTS (
SELECT *
FROM TableA AS a
WHERE a.ItemID = TableB.ParentID
);
回答by trytilmaster
Your Tables A and B seem to store a tree structure. I'd interpret table A as "Nodes" (storing elements of the tree) and Table B as "Edges" (linking a node to it's parent). The inner join variant is very elegant since it covers all cases of "no edge to parent", "edge to PrantID null" and "edge to non existant parent" at once. cheers
您的表 A 和 B 似乎存储树结构。我将表 A 解释为“节点”（存储树的元素），将表 B 解释为“边”（将节点链接到它的父节点）。内连接变体非常优雅，因为它一次涵盖了“无边到父”、“边到 PrantID 为空”和“边到不存在的父”的所有情况。干杯
回答by Andrew Bezzub
You can use outer joins. Something like this:
您可以使用外部联接。像这样的东西：
SELECT a.ItemID
FROM TableA a
INNER JOIN TableB b ON a.ItemId = b.ItemID
LEFT JOIN TableB parentB on a.ItemID = parentB.ParentID
WHERE parentB.ParentID IS NULL
