当我运行 left join 时，内部发生了什么？
我有一个关于 SQL 左连接的问题：我想知道 SQL 服务器如何执行左连接？
假设我有两张桌子。
PEOPLE
- id
- name
PHONE
- id
- person_id
- phone
当我执行：
select name, phone
from people
left join phone on people.id = phone.person_id
...我想知道SQL服务器如何处理查询字符串。
我的猜测是：
- 选择所有行的人
- 开始匹配电话行与条件。在本例中，people.id =phone_person_id。
- 如果未找到，则将“phone”值显示为 null，因为它是左连接。
我说得对吗？？
另外，我应该看什么书才能获得这些信息呢？
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
发生的情况（至少在 postgresql 中，但可能其他类似）是查询规划器将接受您的查询并生成多个执行策略，然后它将根据其对表的了解（通过统计）对这些策略进行排名，然后选择最佳的执行策略并执行。
在这里查看几种非常常见的策略：http://en.wikipedia.org/wiki/ Join_%28SQL%29
基本上：
What happens (at least in postgresql, but probably other are similar) is that the query planner will take your query and generate multiple strategies for execution, then it will rank those strategy depending on the knowledge it has of the tables (through statistics), and then choose the best execution strategy and execute it.
Look here for several very common strategies : http://en.wikipedia.org/wiki/Join_%28SQL%29
Basically:
对于所有联接，查询处理器首先查看正在联接的两个集合，并根据这些表中的数据（由缓存的数据库静态数据表示），使用一种可用的合并技术组合两个表中的行( 合并连接，散列连接，或嵌套循环）。完成后，组合集中对于一侧的行与另一侧的行的每个组合都有一行，这满足连接条件。
然后，如果是外连接，则连接的“内”侧不满足连接条件（“外”侧没有匹配记录）的所有行都将添加回组合集中，其中 null来自外侧的所有列的值。
For all joins, first the query processor looks at the two sets that are being joined, and based on the data in those tables (as represented by cached database statictics), it combines the rows in both tables, using one of the available merge techniques ( Merge Join, Hash Join, or nested Loops). When done the combined set has a row in it for every combination of a row from one side with a row fropm the other side, which satisfies the join condition.
Then, if it's an outer join, all the rows from the 'Inner' side of the join which did not satisfy the join condition (there was no matching record on the 'Outer' Side) are added back into the combined set, with null values for all columns which would have come from the outer side..
在 SQL Server Management Studio 中执行查询，然后在“查询”菜单下选择用于获取实际执行计划的选项。
执行查询后，执行计划将以图表形式返回到单独的选项卡上。它是从左到右读的。该计划将向您展示幕后发生的事情。
阅读更多信息
http://msdn.microsoft.com /en-us/library/aa178423%28SQL.80%29.aspx
http://www.simple-talk.com/sql/performance/execution-plan-basics/
Execute your query in SQL Server Management Studio and under the Query menu, choose the option to get the Actual Execution Plan.
After your execute your query, the execution plan will come back as a diagram on a separate tab. It reads from left to right. This plan will show you what happens under the hood.
Read more here
http://msdn.microsoft.com/en-us/library/aa178423%28SQL.80%29.aspx
http://www.simple-talk.com/sql/performance/execution-plan-basics/