内部联接vs自然联接vs USING子句:有什么优势吗? [英] Inner Join vs Natural Join vs USING clause: are there any advantages?

问题描述

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

Imagine I have two simple tables, such as:

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

(当然是简化的).

我可能有以下任何陈述:

I could have any of the following statements:

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

A working example can be found here: SQL Fiddle: http://sqlfiddle.com/#!15/864a5/13/10

它们都给出几乎相同的结果-肯定是相同的行.

They all give nearly the same results — certainly the same rows.

我一直首选第一种形式，因为它具有灵活性，可读性和可预测性-您可以清楚地定义什么与什么相关.

I have always preferred the first form because of its flexibility, readability and predictability — you clearly define what is connected to what.

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

Now, apart from the fact that the first form has a duplicated column, is there a real advantage to the other two forms? Or are they just syntactic sugar?

我可以看到后一种形式的缺点是期望您将主键和外键命名为相同的名称，但这并不总是可行的.

I can see the disadvantage in the latter forms is that you are expected to have named your primary and foreign keys the same, which is not always practical.

推荐答案

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

Now, apart from the fact that the first form has a duplicated column, is there a real advantage to the other two forms? Or are they just syntactic sugar?

TL; DR NATURAL JOIN用于某种关系编程风格，比通常的SQL风格更简单. (尽管当嵌入到SQL中时，它会负担其余的SQL查询语法.)这是因为1.它直接 使用 谓词的 simple 运算符逻辑 ，工程学(包括软件工程)，科学(包括计算机科学)和数学以及另外两种语言.同时和 em>它直接 使用 关系代数 的简单运算符.

TL;DR NATURAL JOIN is used in a certain style of relational programming that is simpler than the usual SQL style. (Although when embedded in SQL it is burdened with the rest of SQL query syntax.) That's because 1. it directly uses the simple operators of predicate logic, the language of precision in engineering (including software engineering), science (including computer science) and mathematics, and moreover 2. simultaneously and alternatively it directly uses the simple operators of relational algebra.

有关NATURAL JOIN的常见抱怨是，由于共享列不是显式的，因此在模式更改后，可能会发生不合适的列配对.在特定的开发环境中可能就是这种情况.但是在那种情况下，有一个要求，仅某些列被连接，而没有PROJECT 的NATURAL JOIN是不合适的.因此，这些论据假定，认为NATURAL JOIN的使用不当.此外，论者甚至没有意识到他们正在忽略要求.这种抱怨是似是而非的. (此外，合理的软件工程设计原则导致不具有此类规范的接口.)

The common complaint about NATURAL JOIN is that since shared columns aren't explicit, after a schema change inappropriate column pairing may occur. And that may be the case in a particular development environment. But in that case there was a requirement that only certain columns be joined and NATURAL JOIN without PROJECT was not appropriate. So these arguments assume that NATURAL JOIN is being used inappropriately. Moreover the arguers aren't even aware that they are ignoring requirements. Such complaints are specious. (Moreover, sound software engineering design principles lead to not having interfaces with such specificiatons.)

同一阵营的另一个相关的误解性似是而非的抱怨是，.但是 任何连接存在是因为表的意义，而不是约束.不需要查询约束.如果添加了约束，则查询保持正确.如果删除了约束，则对该查询依赖的查询会出错，并且必须更改为不依赖该查询的措辞，即不必更改.这与NATURAL JOIN无关.

Another related misconceived specious complaint from the same camp is that "NATURAL JOIN does not even take foreign key relationships into account". But any join is there because of the table meanings, not the constraints. Constraints are not needed to query. If a constraint is added then a query remains correct. If a constraint is dropped then a query relying on it becomes wrong and must be changed to a phrasing that doesn't rely on it that wouldn't have had to change. This has nothing to do with NATURAL JOIN.

您已经描述了效果上的区别:每个公共列仅返回一个副本.

You have described the difference in effect: just one copy of each common column is returned.

来自是否有任何经验法则可以从易于理解的描述中构造SQL查询?:

事实证明，自然语言表达式，逻辑表达式，关系代数表达式和SQL表达式(后两者的混合)以相当直接的方式对应.

It turns out that natural language expressions and logical expressions and relational algebra expressions and SQL expressions (a hybrid of the last two) correspond in a rather direct way.

例如，来自科德1970 :

所描述的关系称为 component . [...] component ( x ， y ， z )的意思是该部分 x 是零件 y 的直接组件(或子装配体)，需要组装零件 x 的 z 个单元部分 y .

The relation depicted is called component. [...] The meaning of component(x, y,z) is that part x is an immediate component (or subassembly) of part y, and z units of part x are needed to assemble one unit of part y.

来自此答案:

每个基表都有一个语句模板，又称为 predicate ，该语句模板由列名进行参数化，通过该模板，我们可以在行中放入一行或将其保留.

Every base table has a statement template, aka predicate, parameterized by column names, by which we put a row in or leave it out.

将行放在谓词中会给出一个命题即命题.构成真实命题的行将放入表中，而构成虚假命题的行将保留在表中. (因此，一个表陈述了当前行的命题，而不陈述每一个不存在的行的命题.)

Plugging a row into a predicate gives a statement aka proposition. The rows that make a true proposition go in a table and the rows that make a false proposition stay out. (So a table states the proposition of each present row and states NOT the proposition of each absent row.)

但是每个表表达式值的每个表达式都有一个谓词.设计关系模型，以便如果表T和U分别包含T(...)和U(...)所在的行，则:

But every table expression value has a predicate per its expression. The relational model is designed so that if tables T and U hold rows where T(...) and U(...) (respectively) then:

• T NATURAL JOIN U保留其中T(...)和U(...)
的行
• T WHERE condition 保存的行中T(...)和条件
• T UNION CORRESPONDING U保留其中T(...)或U(...)
的行
• T EXCEPT CORRESPONDING U保存的行中T(...)而不是U(...)
• SELECT DISTINCT columns to keep FROM T保存其中
  的行 存在列掉落 T(...)
• 等
• T NATURAL JOIN U holds rows where T(...) AND U(...)
• T WHEREcondition holds rows where T(...) AND condition
• T UNION CORRESPONDING U holds rows where T(...) OR U(...)
• T EXCEPT CORRESPONDING U holds rows where T(...) AND NOT U(...)
• SELECT DISTINCTcolumns to keepFROM T holds rows where
  THERE EXISTS columns to drop SUCH THAT T(...)
• etc

关于SQL的推理是……不是自然的":

Whereas reasoning about SQL otherwise is... not "natural":

可以将SQL SELECT语句代数视为1.将具有相关名称T的表的每个列C隐式重命名为T.C，然后2.重命名CROSS JOINing，然后3.重新命名每个INNER ON，然后4.在每个位置进行限制，然后5.在每个SELECT上投影，然后6.在每个SELECT上重命名，删除T. s，然后7.隐式重命名以在T. -RENAMEs之间删除其余的T.也可以将代数运算符视为逻辑运算符，并将表名视为谓词:T JOIN ...与Employee T.EMPLOYEE has name T.NAME ... AND ....但是从概念上讲，SELECT语句内部是一个引起双重RENAME的CROSS JOIN表，其中列名称为T.C，而外部表中列名称为C.

An SQL SELECT statement can be thought of algebraically as 1. implicitly RENAMEing each column C of a table with (possibly implicit) correlation name T to T.C, then 2. CROSS JOINing, then 3. RESTRICTing per INNER ON, then 4. RESTRICTing per WHERE, then 5. PROJECTing per SELECT, then 6. RENAMEing per SELECT, dropping T.s, then 7. implicitly RENAMEing to drop remaining T.s Between the T.-RENAMEings algebra operators can also be thought of as logic operators and table names as their predicates: T JOIN ... vs Employee T.EMPLOYEE has name T.NAME ... AND .... But conceptually inside a SELECT statement is a double-RENAME-inducing CROSS JOIN table with T.Cs for column names while outside tables have Cs for column names.

或者，SQL SELECT语句在逻辑上可以认为是1.在每个语句中为每个相关名称T和基本名称或子查询E引入FORSOME T IN E，然后2.引用量化的通过使用T.C引用其C部分，然后3.从FROM等等从T.C s构建结果行，然后4.根据SELECT子句命名结果行列，然后4.离开范围FORSOME s.再次将代数运算符视为逻辑运算符，并将表名视为谓词.同样，从概念上讲，这在SELECTs内部具有T.C，而在外部具有相关名称的C.

Alternatively an SQL SELECT statement can be thought of logically as 1. introducing FORSOME T IN E around the entire statement per correlation name T and base name or subquery E, then 2. referring to the value of quantified T by using T.C to refer to its C part, then 3. building result rows from T.Cs per FROM etc, then 4. naming the result row columns per the SELECT clause, then 4. leaving the scope of the FORSOMEs. Again the algebra operators are being thought of as logic operators and table names as their predicates. Again though, this conceptually has T.C inside SELECTs but C outside with correlation names coming and going.

这两个SQL解释远不及使用可互换使用JOIN或AND等简单. (您不必同意它更简单，但是这种理解就是为什么NA​​TURAL JOIN和UNION/EXCEPT CORRESPONDING出现的原因.)(在预期使用范围之外批评这种风格的说法很奇怪.)

These two SQL interpretations are nowhere near as straightforward as just using JOIN or AND, etc, interchangeably. (You don't have to agree that it's simpler, but that perception is why NATURAL JOIN and UNION/EXCEPT CORRESPONDING are there.) (Arguments criticizing this style outside the context of its intended use are specious.)

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

USING is a kind of middle ground orphan with one foot in the NATURAL JOIN camp and one in the CROSS JOIN. It has no real role in the former because there are no duplicate column names there. In the latter it is more or less just abbreviating JOIN conditions and SELECT clauses.

我可以看到后一种形式的缺点是期望您将主键和外键命名为相同的名称，但这并不总是可行的.

I can see the disadvantage in the latter forms is that you are expected to have named your primary and foreign keys the same, which is not always practical.

PK(主键)，FK(外键)和查询不需要其他约束. (知道列是其他函数的功能，允许标量子查询，但您始终可以不使用短语.)此外，任何两个表都可以有意义地连接在一起.如果您需要两列名称与NATURAL JOIN相同，则可以通过SELECT AS重命名.

PKs (primary keys), FKs (foreign keys) & other constraints are not needed for querying. (Knowing a column is a function of others allows scalar subqueries, but you can always phrase without.) Moreover any two tables can be meaningfully joined. If you need two columns to have the same name with NATURAL JOIN you rename via SELECT AS.

这篇关于内部联接vs自然联接vs USING子句:有什么优势吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！