Self join&的区别相关查询 [英] Difference between Self join & correlated query

问题描述

请指出差异

Self join & correlated query

哪一个更好用？



怎么样决定使用哪个？

Which one is better to use?

how to decide which is to be used?

推荐答案

你在哪里学到这些术语？你知道他们的意思吗？答案一如既往，取决于它。

Where did you learn these terms ? Do you know what they mean ? The answer, as always, is that it depends.

SELECT
   employee_number,
   name,
   (SELECT AVG(salary)
      FROM employees
      WHERE department = Bob.department) AS department_average
   FROM employees AS Bob;

第二个选择是子查询。



自联接是表连接自身的地方。两者都很贵。两者都有用途，有时是正确的选择，具体取决于您的情况。我几乎从不使用它，我使用CTE，我的文章是这里 [ ^ ]。如果您有特定问题，请尝试发布您正在处理的特定SQL的详细信息。如果这是一个普遍的问题，它没有真正的答案，答案是知道许多方法的缺陷和好处，并为每个任务选择正确的方法。

The second select is a subquery.

A self join is where a table joins to itself. Both are expensive. Both have uses and are sometimes the right choice, depending on your situation. I almost never use either, I use CTEs, my article on those is here[^]. If you have a specific question, try posting the details of the specific SQL you're working on. If this is a general question, it has no real answer, the answer is to know the pitfalls and benefits of many approaches, and choosing the right approach for each task.

取决于你的确切查询。

但一般的答案是查询优化器会意识到相关的子查询可以与连接交换，并且会这样做。然后它就没关系，只会是语法问题。

Depends on your exact query.
But the general answer is that the query optimizer will realize that the correlated subquery can be exchanged with a join and will do so. And then it won't matter, it will only be a matter of syntax.

这篇关于Self join&的区别相关查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！