SQL 使用内联查询或连接从另一个表中获取数据？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14188668/
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
Use inline query or join to get data from another table?
提问by user1589188
Using SQL server 2008 R2 which one is better (quicker, less intermediate tables, less memory usage) and the usual practice?
使用 SQL Server 2008 R2 哪个更好（更快、更少的中间表、更少的内存使用）和通常的做法？
SELECT staff.id, staff.name, details.address, salary.hourly_rate,
manager.name, boss.name
FROM STAFF_TABLE staff
LEFT JOIN STAFF_DETAILS_TABLE details on staff.id = details.id
LEFT JOIN STAFF_SALARY_TABLE salary on staff.id = salary.id
LEFT JOIN STAFF_TABLE manager on staff.manager_id = manager.id
LEFT JOIN STAFF_TABLE boss on staff.boss_id = boss.id
or
或者
SELECT staff.id, staff.name,
(SELECT address FROM STAFF_DETAILS_TABLE id = staff.id) [address],
(SELECT hourly_rate FROM STAFF_SALARY_TABLE WHERE id = staff.id) [hourly_rate],
(SELECT name FROM STAFF_TABLE WHERE id = staff.manager_id) [manager_name],
(SELECT name FROM STAFF_TABLE WHERE id = staff.boss_id) [boss_name]
FROM STAFF_TABLE staff
Thank you!
谢谢！
回答by Gordon Linoff
If you have indexes on all the appropriate ids in all the tables, the queries should generate the same execution plan. One slight difference is that the optimizer might treat the in-line queries differently, because they must return only one row (else it generates an error).
如果所有表中的所有适当 id 都有索引，则查询应生成相同的执行计划。一个细微的区别是优化器可能会以不同的方式处理内联查询，因为它们必须只返回一行（否则会产生错误）。
I much prefer to keep all table references in the fromclause, where possible. I find that it makes it easier to maintain queries -- all the table references are in one place. So, I much prefer the format with the explicit joins.
我更喜欢from在可能的情况下将所有表引用保留在子句中。我发现这样可以更轻松地维护查询——所有的表引用都集中在一个地方。所以，我更喜欢带有显式joins.
回答by Peter Wooster
The only real way to get an answer to that is to time it and examine the execution plans of the various queries. I believe that SQL Server will try to optimize the query so there may be very little difference in the time. The version with the JOIN is much more readable and would be my preference if there isn't a huge difference in the performance.
获得答案的唯一真正方法是计时并检查各种查询的执行计划。我相信 SQL Server 会尝试优化查询，因此时间上的差异可能很小。带有 JOIN 的版本更具可读性，如果性能没有太大差异，这将是我的首选。
