SQL 自然连接和内部连接的区别

Difference between natural join and inner join

提问by smith

What is the difference between a natural join and an inner join?

自然连接和内部连接有什么区别？

回答by Jonathan Leffler

One significant difference between INNER JOIN and NATURAL JOIN is the number of columns returned.

INNER JOIN 和 NATURAL JOIN 之间的一个显着区别是返回的列数。

Consider:

考虑：

TableA                           TableB
+------------+----------+        +--------------------+    
|Column1     | Column2  |        |Column1  |  Column3 |
+-----------------------+        +--------------------+
| 1          |  2       |        | 1       |   3      |
+------------+----------+        +---------+----------+

The INNER JOINof TableA and TableB on Column1 will return

在INNER JOIN表A和表B对列1将返回

SELECT * FROM TableA AS a INNER JOIN TableB AS b USING (Column1);
SELECT * FROM TableA AS a INNER JOIN TableB AS b ON a.Column1 = b.Column1;

+------------+-----------+---------------------+    
| a.Column1  | a.Column2 | b.Column1| b.Column3|
+------------------------+---------------------+
| 1          |  2        | 1        |   3      |
+------------+-----------+----------+----------+

The NATURAL JOINof TableA and TableB on Column1 will return:

在NATURAL JOIN表A和表B在列1将返回：

SELECT * FROM TableA NATURAL JOIN TableB
+------------+----------+----------+    
|Column1     | Column2  | Column3  |
+-----------------------+----------+
| 1          |  2       |   3      |
+------------+----------+----------+

The repeated column is avoided.

避免重复列。

(AFAICT from the standard grammar, you can't specify the joining columns in a natural join; the join is strictly name-based. See also Wikipedia.)

（标准语法中的 AFAICT，您不能在自然连接中指定连接列；连接严格基于名称。另请参阅Wikipedia。）

(There's a cheat in the inner join output; the a.and b.parts would not be in the column names; you'd just have column1, column2, column1, column3as the headings.)

（内部连接输出中有一个作弊；a.和b.部分不会出现在列名中；您只需将column1, column2, column1,column3作为标题。）

回答by Bohemian

• An innerjoin is one where the matching row in the joined table is required for a row from the first table to be returned
• An outerjoin is one where the matching row in the joined table is notrequired for a row from the first table to be returned
• A naturaljoin is a join (you can have either natural leftor natural right) that assumes the join criteria to be where same-named columns in both table match

• 一个内连接是一个其中需要对于行从第一表中联接表中的匹配的行要被返回
• 一个外连接是其中在连接表中匹配的行不是所需的行从第一表被返回
• 一个自然的联接是联接（你可以有natural left或natural right），它假定连接标准是其中两个表匹配的同名列

I would avoid using natural joins like the plague, because natural joins are:

我会避免使用像瘟疫一样的自然连接，因为自然连接是：

• notstandard sql [SQL 92] and therefore notportable, not particularly readable (by most SQL coders) and possibly not supported by various tools/libraries
• not informative; you can't tell what columns are being joined on without referring to the schema
• your join conditions are invisibly vulnerable to schema changes - if there are multiple natural join columns and one such column is removed from a table, the query will still execute, but probably not correctly and this change in behaviour will be silent
• hardly worth the effort; you're only saving about 10 seconds of typing

• 不是标准的 sql [SQL 92]，因此不是可移植的，不是特别可读（大多数 SQL 编码器），并且可能不受各种工具/库的支持
• 不提供信息；如果不参考架构，您就无法判断哪些列被加入
• 您的连接条件在无形中容易受到架构更改的影响 - 如果有多个自然连接列并且从表中删除了一个这样的列，查询仍然会执行，但可能不正确并且这种行为变化将是无声的
• 几乎不值得付出努力；你只节省了大约 10 秒的打字时间

回答by MatBailie

A natural join is just a shortcut to avoid typing, with a presumption that the join is simple and matches fields of the same name.

自然连接只是避免键入的快捷方式，假设连接很简单并且匹配同名字段。

SELECT
  *
FROM
  table1
NATURAL JOIN
  table2
    -- implicitly uses `room_number` to join

Is the same as...

是相同的...

SELECT
  *
FROM
  table1
INNER JOIN
  table2
    ON table1.room_number = table2.room_number

What you can't do with the shortcut format, however, is more complex joins...

但是，您不能使用快捷方式格式进行更复杂的连接...

SELECT
  *
FROM
  table1
INNER JOIN
  table2
    ON (table1.room_number = table2.room_number)
    OR (table1.room_number IS NULL AND table2.room_number IS NULL)

回答by onedaywhen

SQL is not faithful to the relational model in many ways. The result of a SQL query is not a relation because it may have columns with duplicate names, 'anonymous' (unnamed) columns, duplicate rows, nulls, etc. SQL doesn't treat tables as relations because it relies on column ordering etc.

SQL 在许多方面并不忠实于关系模型。SQL 查询的结果不是关系，因为它可能包含具有重复名称的列、“匿名”（未命名）列、重复行、空值等。 SQL 不会将表视为关系，因为它依赖于列排序等。

The idea behind NATURAL JOINin SQL is to make it easier to be more faithful to the relational model. The result of the NATURAL JOINof two tables will have columns de-duplicated by name, hence no anonymous columns. Similarly, UNION CORRESPONDINGand EXCEPT CORRESPONDINGare provided to address SQL's dependence on column ordering in the legacy UNIONsyntax.

NATURAL JOINSQL背后的想法是更容易更忠实于关系模型。NATURAL JOIN两个表的结果将具有按名称去重的列，因此没有匿名列。类似地，提供UNION CORRESPONDING和EXCEPT CORRESPONDING是为了解决 SQL 对旧UNION语法中列顺序的依赖。

However, as with all programming techniques it requires discipline to be useful. One requirement for a successful NATURAL JOINis consistently named columns, because joins are implied on columns with the same names (it is a shame that the syntax for renaming columns in SQL is verbose but the side effect is to encourage discipline when naming columns in base tables and VIEWs :)

但是，与所有编程技术一样，它需要纪律才能有用。成功的一个要求NATURAL JOIN是一致命名的列，因为在具有相同名称的列上隐含了连接（遗憾的是，在 SQL 中重命名列的语法很冗长，但副作用是鼓励在命名基表中的列时遵守纪律，并且VIEW:)

Note a SQL NATURAL JOINis an equi-join**, however this is no bar to usefulness. Consider that if NATURAL JOINwas the only join type supported in SQL it would still be relationally complete.

请注意，SQLNATURAL JOIN是等连接**，但这并不是有用的障碍。考虑一下，如果NATURAL JOIN是 SQL 中支持的唯一连接类型，它仍然是关系完整的。

While it is indeed true that any NATURAL JOINmay be written using INNER JOINand projection (SELECT), it is also true that any INNER JOINmay be written using product (CROSS JOIN) and restriction (WHERE); further note that a NATURAL JOINbetween tables with no column names in common will give the same result as CROSS JOIN. So if you are only interested in results that are relations (and why ever not?!) then NATURAL JOINis the only join type you need. Sure, it is true that from a language design perspective shorthands such as INNER JOINand CROSS JOINhave their value, but also consider that almost any SQL query can be written in 10 syntactically different, but semantically equivalent, ways and this is what makes SQL optimizers so very hard to develop.

虽然 any 确实NATURAL JOIN可以使用INNER JOIN和投影 ( SELECT)编写，但 anyINNER JOIN也可以使用乘积 ( CROSS JOIN) 和限制 ( WHERE)编写；进一步注意，NATURAL JOIN没有共同列名的表之间的a将给出与CROSS JOIN. 因此，如果您只对关系结果感兴趣（为什么不感兴趣？！）那么NATURAL JOIN是您唯一需要的连接类型。当然，从语言设计的角度来看，INNER JOIN和等速记确实CROSS JOIN有其价值，但也要考虑到几乎任何 SQL 查询都可以用 10 种语法不同但语义相同的方式编写，这就是使 SQL 优化器如此困难的原因发展。

Here are some example queries (using the usual parts and suppliers database) that are semantically equivalent:

以下是一些语义等效的示例查询（使用常用的部件和供应商数据库）：

SELECT *
  FROM S NATURAL JOIN SP;

-- Must disambiguate and 'project away' duplicate SNO attribute
SELECT S.SNO, SNAME, STATUS, CITY, PNO, QTY
  FROM S INNER JOIN SP 
          USING (SNO);                        

-- Alternative projection
SELECT S.*, PNO, QTY
  FROM S INNER JOIN SP 
          ON S.SNO = SP.SNO;

-- Same columns, different order == equivalent?!
SELECT SP.*, S.SNAME, S.STATUS, S.CITY
  FROM S INNER JOIN SP 
      ON S.SNO = SP.SNO;

-- 'Old school'
SELECT S.*, PNO, QTY
  FROM S, SP 
 WHERE S.SNO = SP.SNO;

---

---

** Relational natural join is not an equijoin, it is a projection of one. – philipxy

** 关系自然连接不是等连接，它是一个的投影。– 飞利浦

回答by onedaywhen

A NATURALjoin is just short syntax for a specificINNERjoin -- or "equi-join" -- and, once the syntax is unwrapped, both represent the same Relational Algebra operation. It's not a "different kind" of join, as with the case of OUTER(LEFT/RIGHT) or CROSSjoins.

一个NATURAL连接是一个只是短期的语法具体INNER加盟-或“相等连接” -而且，一旦语法是解开，既代表相同的关系代数操作。它不是一种“不同类型”的连接，就像OUTER( LEFT/ RIGHT) 或CROSS连接的情况一样。

See the equi-joinsection on Wikipedia:

请参阅维基百科上的equi-join部分：

A natural join offers a further specialization of equi-joins. The join predicate arises implicitly by comparing all columns in both tables that have the same column-namesin the joined tables.The resulting joined table contains only one column for each pair of equally-named columns.

Most experts agree that NATURAL JOINs are dangerous and therefore strongly discourage their use.The danger comes from inadvertently adding a new column, named the same as another column ...

自然连接提供了等连接的进一步专业化。连接谓词是通过比较连接表中具有相同列名的两个表中的所有列而隐式出现的。生成的连接表中每对同名列仅包含一列。

大多数专家都同意NATURAL JOIN 是危险的，因此强烈建议不要使用它们。危险来自于无意中添加了一个与另一列同名的新列......

That is, all NATURALjoins may be written as INNERjoins(but the converse is not true). To do so, just create the predicate explicitly-- e.g. USINGor ON-- and, as Jonathan Leffler pointed out, select the desired result-set columns to avoid "duplicates" if desired.

也就是说，所有NATURAL连接都可以写为INNER连接（但反之则不然）。为此，只需显式创建谓词 ——例如USING或ON——并且，正如 Jonathan Leffler 指出的那样，如果需要，选择所需的结果集列以避免“重复”。

Happy coding.

快乐编码。

---

---

(The NATURALkeyword can also be applied to LEFTand RIGHTjoins, and the same applies. A NATURAL LEFT/RIGHTjoin is just a short syntax for a specificLEFT/RIGHTjoin.)

（NATURAL关键字也可以应用于LEFT和RIGHT连接，同样适用。NATURAL LEFT/RIGHT连接只是特定LEFT/RIGHT连接的简短语法。）

回答by Victor Bhatti

Natural Join: It is combination or combined result of all the columns in the two tables. It will return all rows of the first table with respect to the second table.

自然连接：是两个表中所有列的组合或组合结果。它将返回第一个表相对于第二个表的所有行。

Inner Join: This join will work unless if any of the column name shall be sxame in two tables

内部联接：除非任何列名在两个表中都是相同的，否则此联接将起作用

回答by Suchitra Phadke

A Natural Join is where 2 tables are joined on the basis of all common columns.

自然连接是基于所有公共列连接 2 个表。

common column : is a column which has same name in both tables + has compatible datatypes in both the tables. You can use only = operator

公共列：是在两个表中具有相同名称的列+在两个表中具有兼容的数据类型。您只能使用 = 运算符

A Inner Join is where 2 tables are joined on the basis of common columns mentioned in the ON clause.

内部连接是基于 ON 子句中提到的公共列连接 2 个表的地方。

common column : is a column which has compatible datatypes in both the tables but need not have the same name. You can use only any comparision operator like =, <=, >=, <, >, <>

公共列：是在两个表中具有兼容数据类型但不必具有相同名称的列。您只能使用任何比较运算符，例如=, <=, >=, <, >,<>

回答by Rajat Gupta

difference is that int the inner(equi/default)join and natural join that in the natuarl join common column win will be display in single time but inner/equi/default/simple join the common column will be display double time.

不同之处在于intinner(equi/default)join和natural join，在natuarl join公共列win中将显示单次，而inner/equi/default/simple join公共列将显示双倍。

回答by rashedcs

Inner join and natural join are almost same but there is a slight difference between them. The difference is in natural join no need to specify condition but in inner join condition is obligatory. If we do specify the condition in inner join , it resultant tables is like a cartesian product.

内连接和自然连接几乎相同，但它们之间略有不同。区别在于自然连接不需要指定条件，而内部连接条件是强制性的。如果我们在 inner join 中指定条件，则结果表就像笛卡尔积。

回答by zloctb

mysql> SELECT  * FROM tb1 ;
+----+------+
| id | num  |
+----+------+
|  6 |   60 |
|  7 |   70 |
|  8 |   80 |
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
+----+------+
6 rows in set (0.00 sec)

mysql> SELECT  * FROM tb2 ;
+----+------+
| id | num  |
+----+------+
|  4 |   40 |
|  5 |   50 |
|  9 |   90 |
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
+----+------+
6 rows in set (0.00 sec)

INNER JOIN :

内部联接 ：

mysql> SELECT  * FROM tb1 JOIN tb2 ; 
+----+------+----+------+
| id | num  | id | num  |
+----+------+----+------+
|  6 |   60 |  4 |   40 |
|  7 |   70 |  4 |   40 |
|  8 |   80 |  4 |   40 |
|  1 |    1 |  4 |   40 |
|  2 |    2 |  4 |   40 |
|  3 |    3 |  4 |   40 |
|  6 |   60 |  5 |   50 |
|  7 |   70 |  5 |   50 |
|  8 |   80 |  5 |   50 |
.......more......
return 36 rows in set (0.01 sec) 
AND NATURAL JOIN :

    mysql> SELECT  * FROM tb1 NATURAL JOIN tb2 ;
    +----+------+
    | id | num  |
    +----+------+
    |  1 |    1 |
    |  2 |    2 |
    |  3 |    3 |
    +----+------+
    3 rows in set (0.01 sec)