内连接 vs 自然连接 vs USING子句：有哪些优势？

Question

内连接 vs 自然连接 vs USING子句：有哪些优势？

8

想象一下我有两个简单的表格，例如：

CREATE TABLE departments(dept INT PRIMARY KEY, name);
CREATE TABLE employees(id PRIMARY KEY, fname, gname,
    dept INT REFERENCES departments(dept));

当然，简化了的内容。

我可以有以下任意一种陈述:

SELECT * FROM employees e INNER JOIN departments d ON e.dept=d.dept;
SELECT * FROM employees e NATURAL JOIN departments d;
SELECT * FROM employees e JOIN departments d USING(dept);

这里可以找到一个可行的例子：SQL Fiddle：http://sqlfiddle.com/#!15/864a5/13/10

它们都给出了几乎相同的结果——肯定是相同的行。

我一直更喜欢第一种形式，因为它灵活、易读且可预测性强——你清楚地定义了什么与什么相连。

现在，除了第一种形式有重复列之外，其他两种形式是否真的有优势？还是它们只是语法糖？

我可以看出后面的形式的缺点是你需要将主键和外键命名相同，这并不总是切实可行。

- Manngo

1

你得到了相同数量的列吗？根据ANSI/ISO SQL，NATURAL JOIN和JOIN USING应该删除重复的列。顺便说一下，“NATURAL JOIN”在编写应用程序时与“SELECT *”一样愚蠢，如果一个表添加了一个与另一个表中同名的列，它将会出错... - jarlh

@jarlh 第一个会复制 dept 列，而其他两个不会。 - Manngo

5个回答

2

NATURAL JOIN并不被广泛支持，JOIN USING也是如此（例如在SQL Server中）。

有很多人认为使用NATURAL JOIN是个坏主意。个人认为，不明确命名诸如连接之类的事物会导致灾难的发生。

例如，如果您在不知情的情况下向表中添加一列，恰好符合“自然连接”的条件，那么当自然连接突然执行完全不同的操作时，您可能会遇到意外的代码故障。您可能认为添加一列不会破坏任何东西，但它可能会破坏编写不良视图和自然连接。

在构建系统时，您永远不应该允许这些风险潜入。这就像在多个表上创建视图而没有在每个列上使用表别名，并且使用没有列列表的插入语句一样。

出于这些原因，如果您现在正在学习SQL，请不要养成使用这些方法的习惯。

- Nick.McDermaid

我认为不控制JOIN操作会引发问题。就个人而言，我一直坚持认为一个表中的列名应该独立于另一个表中的列名。这使得自然连接在最好的情况下也是有风险的。 - Manngo

1

打字 JOIN 默认执行 INNER JOIN。因此：

SELECT * FROM employees e INNER JOIN departments d USING(dept);

等同于

SELECT * FROM employees e JOIN departments d USING(dept);

而且你将只在结果中拥有一个部门列。

同样地

SELECT * FROM employees e INNER JOIN departments d ON e.dept=d.dept;

等同于

SELECT * FROM employees e JOIN departments d ON e.dept=d.dept;

但是你将在结果中有一个重复的部门列。

INNER JOIN 更容易阅读，特别是如果您的查询包含其他连接类型（LEFT或RIGHT等）。

自然连接假定两个表中具有相同名称的列匹配。因此，如果例如在员工表中您的连接列名为“department”，而在您的部门表中您的连接列名为“dept”，则无法执行自然连接。

- Thibault Clement

1

自然连接（NATURAL JOIN）和使用连接条件进行连接（JOIN USING）将返回一个部门列，而常规连接（JOIN）将返回两个部门列。 - jarlh

1

NATURAL JOIN的一个主要优点是它是唯一不会生成“重复列”的SQL连接。

如果除了NATURAL JOIN之外的所有连接类型都从SQL语言中删除，它仍然是关系完备的。 NATURAL JOIN是您需要的唯一连接类型。

但是，NATURAL JOIN直到SQL92才被引入SQL中。在SQL的早期，语言设计师选择了其他连接类型，因此不得不找到一种处理“重复列”的方法。

考虑以下结果：

employees e CROSS JOIN departments d

涉及两个名为dept的列，因为每个表都有一个名为dept的列，即“重复列”。

解决“重复列”问题的选择是范围变量，在上面的示例中为e和d。 [大多数SQL人称它们为“表别名”，这很令人困惑，因为范围变量代表的是一行，而不是一个表）。 SQL标准称它们为“关联名称”，但在上下文中并不清楚“关联”是什么意思。 Chris Date和LINQ将其称为范围变量，我也是！] 当使用“传统”的连接类型并省略范围变量时，将使用与表名相等的范围变量名称（也许是“表别名”谬论的根源？）。

使用OP的示例：

SELECT * FROM employees e INNER JOIN departments d ON e.dept=d.dept;

投影SELECT *将包含“重复列”。列的名称及其在结果中的顺序是SQL标准不尝试解决的事情之一；已经有太多分歧的实现，无法尝试标准化。在SQL Server中，我得到了重复的名称[sigh]。

也许出于这些原因，大多数SQL专家都说，“永远不要使用SELECT *”等。通常的方法是指定每个列并“投影掉”任何重复项。[我认为一个选择除了<一组列>功能会很好，但我认为SQL专家赢得了这一天。]

当使用NATURAL JOIN时，没有需要“投影掉”的“重复列”和连接谓词，因此不需要范围变量。因此，OP的示例应该重写为：

SELECT * FROM employees NATURAL JOIN departments;

尽管如果一个人专门使用自然连接并回避所有其他连接类型，那么范围变量就不再需要了，但 SQL 有时仍然需要它们。使用 @philipxy 的场景，“如果你需要两列具有相同的名称，使用 NATURAL JOIN，则可以通过 SELECT AS 进行重命名” - 如果你的 SQL 实现缺乏常见的表达式，并且你被迫在派生表中进行重命名，则 SQL 要求为派生表分配一个范围变量，即使你以后永远不会再引用它！假设 OP 示例中的每个表都有一个无意义的 guid 列，需要为 NATURAL JOIN 目的“投影掉”：

SELECT *
  FROM ( SELECT dep, name, FROM employees ) e 
       NATURAL JOIN ( SELECT dept, fname, gname departments ) d;

在这种情况下，如果没有范围变量e和d，SQL将无效（您将获得解析错误）。SQL实现不需要范围变量（即使它需要，它也可以在内部生成占位符！），但是由于SQL的“兼容性枷锁”，SQL语言要求使用它们，其中没有任何功能被删除或弃用。

- onedaywhen

0

来自Oracle文档：

NATURAL JOIN是一种JOIN操作，它基于两个表中的公共列为您创建一个隐式的JOIN子句。公共列是在两个表中具有相同名称的列。

NATURAL JOIN可以是INNER JOIN、LEFT OUTER JOIN或RIGHT OUTER JOIN。默认值为INNER JOIN。

该子句

TableA JOIN tableB USING(column)

正如你所指出的那样，它只是一种语法糖

TableA JOIN tableB ON tableA.column = tableB.column

- Pieter Geerkens

2

除了 JOIN USING 只返回一次列，而 JOIN 返回 tableA.column 和 tableB.column 两个表的列。 - jarlh

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- philipxy · Accepted Answer

除了第一个表单有重复的列之外，其他两个表单是否真的有优势？还是它们只是语法糖？NATURAL JOIN在某种简化常规SQL风格的关系编程中使用。这是因为1.它直接使用谓词逻辑的简单运算符，这是工程（包括软件工程）、科学（包括计算机科学）和数学中精度语言，而且2.同时并且交替地直接使用关系代数的简单运算符。（尽管在嵌入SQL时会加上其余的SQL查询语法。）

常见的关于NATURAL JOIN的抱怨是，由于共享列不是显式的，在模式更改后可能会出现不适当的列配对。在特定的开发环境中可能会出现这种情况。但在这种情况下，有一个要求，即只连接某些列，并且没有PROJECT的NATURAL JOIN是不合适的。因此，这些论点假设NATURAL JOIN被不当使用。而且，争论者甚至没有意识到他们正在忽略需求。这样的抱怨是牵强附会的。（此外，良好的软件工程设计原则不会有这样具体规格的接口。）

同一阵营的另一个相关的错误抱怨是"NATURAL JOIN甚至不考虑外键关系"。但是任何连接都是因为表的含义而存在，而不是因为约束条件。查询不需要约束条件。如果添加约束条件，则查询仍然正确。如果删除约束条件，则依赖它的查询将变得错误，必须更改为不依赖它的措辞，而这种措辞本来不需要更改。这与NATURAL JOIN无关。

你已经描述了影响的不同之处：每个公共列只返回一份副本。

来自是否有任何经验法则可以从可读的描述构建SQL查询？：

事实证明，自然语言表达式、逻辑表达式、关系代数表达式和SQL表达式（最后两者的混合体）在相当直接的方式上对应。

例如来自Codd 1970：

所描绘的关系称为组件。[...] component（x，y，z）的意思是部件x是部件y的直接组成部分（或子装配件），并且需要z个部件x才能组装一个部件y。

来自这个答案：

每个基本表都有一个语句模板，也称为谓词，由列名参数化，通过它我们可以将一行加入或者排除。

将一行插入到谓词中会产生一个陈述，也称为命题。使命题为真的行进入表格，而使命题为假的行留在外面。（因此，表格陈述了每个存在行的命题，并且未存在行的命题并未被陈述。）

每个表达式值都有一个谓词，根据其表达式。关系模型的设计使得如果表T和U分别保存T(...)和U(...)行，则：

- T NATURAL JOIN U 保存T(...) AND U(...)行 - T WHERE condition 保存T(...) AND condition行 - T UNION CORRESPONDING U 保存T(...) OR U(...)行 - T EXCEPT CORRESPONDING U 保存T(...) AND NOT U(...)行 - SELECT DISTINCT columns to keep FROM T 保存THERE EXISTS columns to drop SUCH THAT T(...)行 - 等等

否则，对SQL进行推理是不“自然”的：

SQL SELECT语句可以代数地理解为：1. 隐式地将表的每个列C与（可能隐含的）关联名称T重命名为T.C，然后2. CROSS JOIN，然后3. 根据INNER ON进行RESTRICT，然后4. 根据WHERE进行RESTRICT，然后5. 根据SELECT进行PROJECT，然后6. 根据SELECT进行RENAME，删除T.，然后7. 隐式地重命名以删除剩余的T.。在T.-RENAMEings之间，代数运算符也可以被视为逻辑运算符，表名则是它们的谓词：T JOIN ... vs Employee T.EMPLOYEE has name T.NAME ... AND ...。但是，在SELECT语句内部的概念上，存在一个双重重命名引起的CROSS JOIN表，其中列名为T.C，而外部表的列名为C。

或者，可以将SQL SELECT语句在逻辑上视为：1. 在每个相关名称T和基本名称或子查询E周围引入，然后2. 使用T.C引用量化的T的值来引用其C部分，然后3. 根据FROM等构建结果行从T.C中，然后4. 根据SELECT子句命名结果行列，然后4. 离开的范围。再次，代数运算符被认为是逻辑运算符，表名是它们的谓词。但是，这在概念上具有SELECT中的T.C，但是在相关名称的C之外进出。

这两个SQL解释远不如仅使用JOIN或AND等“可互换”的方式直接明了。（您不必同意它更简单，但是这种感知是NATURAL JOIN和UNION / EXCEPT CORRESPONDING存在的原因。）（在预期用途之外批评此样式的论点是牵强附会的。）

USING是一种中间地带的孤儿，一只脚在NATURAL JOIN阵营，另一只脚在CROSS JOIN阵营。它在前者中没有真正的作用，因为那里没有重复的列名。在后者中，它或多或少只是缩写JOIN条件和SELECT子句。

“我可以看到后者的缺点在于你需要将主键和外键命名相同，这并不总是切实可行。”

PK（主键）、FK（外键）和其他约束并不需要进行查询。（知道某个列是其他列的函数允许标量子查询，但你总是可以重新构造。）此外，任意两个表都可以有意义地连接。如果你需要两个列具有相同的名称，则可以通过SELECT AS进行重命名。