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

自然连接和内部连接之间的区别是什么?

313591 次浏览
小开

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

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

和……一样。

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

然而，你不能用快捷格式做的是更复杂的连接…

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)
小开
  • 内心的联接是这样一种联接，联接表中的匹配行对于返回的第一个表中的一行是必需的
  • 外联接是这样一种联接，联接表中的匹配行是返回的第一个表中的一行所需的不
  • 自然连接是一个连接(你可以有natural left或natural right)，它假设连接条件是两个表中同名列匹配的位置

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

  • 不标准sql [sql 92]和因此不可移植，不是特别可读(大多数sql编码器)，可能不支持各种工具/库
  • 没有信息;如果不参考模式，就无法知道哪些列正在被连接
  • 您的连接条件对模式更改是无形的脆弱的——如果有多个自然连接列，并且从表中删除了一个这样的列，查询仍然会执行，但可能不正确，这种行为的变化将是无声的
  • 几乎不值得这么努力;你只节省了大约10秒的打字时间
小开
最佳答案

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

考虑:

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

TableA和TableB在column n1上的INNER JOIN将返回

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      |
+------------+-----------+----------+----------+

TableA和TableB在Column1上的NATURAL JOIN将返回:

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

避免了重复列。

(AFAICT从标准语法来看，您不能在自然连接中指定连接列;连接严格基于名称。另见维基百科。)

(在内部连接输出中有一个欺骗;__ABC0和__ABC1部分将不在列名中;你只需要用__ABC2， __ABC3， __ABC2， column3作为标题。)

小开

NATURAL连接只是具体的 INNER连接的简短语法——或“相等连接”;——并且，一旦语法被打开，两者都表示相同的关系代数操作。这不是一个“不同的类型”;的连接，如OUTER (LEFT/RIGHT)或CROSS连接的情况。

参见Wikipedia上的等值连接部分:

自然连接提供了等价连接的进一步专门化。通过比较两个表中具有相同列名的所有列，隐式地产生连接谓词。结果的连接表对于每对相同名称的列只包含一个列。

大多数专家同意天然接头是危险的，因此强烈反对使用。危险来自于无意中添加了一个新列，命名为另一个列…

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

快乐的编码。

(NATURAL关键字也可以应用于LEFT和RIGHT连接，同样适用。NATURAL LEFT/RIGHT连接只是具体的 LEFT/RIGHT连接的一个简短语法。)

小开

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

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

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

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

虽然确实可以使用INNER JOIN和投影(SELECT)来编写任何NATURAL JOIN，但也可以使用product (CROSS JOIN)和restriction (WHERE)来编写任何INNER JOIN;进一步注意，在没有相同列名的表之间使用NATURAL JOIN将得到与CROSS JOIN相同的结果。因此，如果你只对关系类型的结果感兴趣(为什么不呢?!)，那么NATURAL JOIN是你唯一需要的连接类型。当然，从语言设计的角度来看，像INNER JOIN和CROSS JOIN这样的简写确实有其价值，但也要考虑到几乎任何SQL查询都可以用10种语法不同但语义等效的方式编写，这就是SQL优化器很难开发的原因。

下面是一些语义等效的查询示例(使用常用零件和供应商数据库):

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;

**关系自然连接不是一种均连接，而是一种投影。——philipxy

小开

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

内部连接:这种连接将工作，除非任何列名将在两个表中same

小开

内联接，联接两个列名相同的表。

自然连接，连接两个列名和数据类型相同的表。

小开

自然连接是在所有公共列的基础上连接两个表。

common column:在两个表中具有相同名称的列+在两个表中具有兼容的数据类型。 你只能使用= operator

内部连接是在on子句中提到的公共列的基础上连接两个表。

common column:在两个表中具有兼容数据类型的列，但不需要具有相同的名称。 你只能使用任何比较运算符，比如=， <=， >=， <， >， <>

小开 
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)

内连接:

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)
小开

不同的是，内部(equi/default)连接和自然连接，自然连接公共列中的win将在单次显示，但内部/equi/default/简单连接公共列将显示两次。

小开

内连接和自然连接基本相同，但有细微的区别。不同的是，在自然连接中不需要指定条件，但在内部连接中条件是必须的。如果我们在内连接中指定条件，则生成的表就像笛卡尔积。

小开

自然连接:SQL Join子句组合关系数据库中2个或多个表的字段。自然连接基于两个表中具有相同名称的所有列，以及两个表中所有匹配列中具有相等值的选定行。

—两个列的名称和数据类型必须相同。

使用子句:在自然连接中，如果表具有相同名称但数据类型不同的列，则连接会导致错误。为了避免这种情况，可以使用USING子句修改join子句。USING子句指定应用于连接的列。


提交答案