自然连接和内部连接有什么区别?
INNER JOIN 和 NATURAL JOIN 之间的一个显着区别是返回的列数。
考虑:
TableA TableB
+------------+----------+ +--------------------+
|Column1 | Column2 | |Column1 | Column3 |
+-----------------------+ +--------------------+
| 1 | 2 | | 1 | 3 |
+------------+----------+ +---------+----------+
Column1 上的 TableA 和 TableB 的 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 |
+------------+-----------+----------+----------+
Column1 上的 TableA 和 TableB 的 NATURAL JOIN
将返回:
SELECT * FROM TableA NATURAL JOIN TableB
+------------+----------+----------+
|Column1 | Column2 | Column3 |
+-----------------------+----------+
| 1 | 2 | 3 |
+------------+----------+----------+
避免了重复的列。
(标准语法中的 AFAICT,您不能在自然连接中指定连接列;连接严格基于名称。另请参见 Wikipedia。)
(内部连接输出中有一个作弊;a.
和 b.
部分不会出现在列名中;您只有 column1
、column2
、column1
、column3
作为标题。)
内连接是需要连接表中的匹配行才能返回第一个表中的行
外连接是连接表中的匹配行不需要返回第一个表中的行
自然连接是一种连接(您可以使用自然左或自然右),它假定连接条件是两个表中的同名列匹配的位置
我会避免使用像瘟疫一样的自然连接,因为自然连接是:
不是标准 sql [SQL 92],因此不可移植,不是特别可读(大多数 SQL 编码人员),并且可能不受各种工具/库的支持
不提供信息;如果不参考架构,您将无法分辨正在加入哪些列
您的连接条件很容易受到架构更改的影响 - 如果有多个自然连接列并且从表中删除了一个这样的列,则查询仍将执行,但可能不正确,并且这种行为更改将保持沉默
几乎不值得努力;你只节省了大约 10 秒的打字时间
NATURAL JOIN Checkouts
”..."
id
无处不在,加入它也无用;通常的外键名称是 tablename_id
。自然连接是一个坏、坏、坏的主意。
自然连接只是避免键入的快捷方式,假设连接很简单并且匹配同名字段。
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 JOIN ... USING ()
?标准是 a NATURAL JOIN b
或 a JOIN b USING (c)
room_number
的列,而您的内部连接将有两个名为 room_number
的列。
SQL 在很多方面并不忠实于关系模型。 SQL 查询的结果不是关系,因为它可能包含具有重复名称的列、“匿名”(未命名)列、重复行、空值等。SQL 不将表视为关系,因为它依赖于列排序等。
SQL 中 NATURAL JOIN
背后的想法是更容易更忠实于关系模型。两个表的 NATURAL JOIN
的结果将包含按名称去重的列,因此没有匿名列。同样,提供 UNION CORRESPONDING
和 EXCEPT CORRESPONDING
是为了解决 SQL 对旧 UNION
语法中列顺序的依赖性。
但是,与所有编程技术一样,它需要纪律才能有用。成功的 NATURAL JOIN
的一个要求是一致地命名列,因为连接隐含在具有相同名称的列上(遗憾的是,在 SQL 中重命名列的语法很冗长,但副作用是在命名列时鼓励纪律基表和 VIEW
s :)
请注意,SQL NATURAL JOIN
是一个等值连接**,但这并没有妨碍其有用性。考虑一下,如果 NATURAL JOIN
是 SQL 中唯一支持的连接类型,它仍然是 relationally complete。
虽然确实可以使用 INNER JOIN
和投影 (SELECT
) 编写任何 NATURAL JOIN
,但也可以使用乘积 (CROSS JOIN
) 和限制 (WHERE
);进一步注意,没有共同列名的表之间的 NATURAL JOIN
将给出与 CROSS JOIN
相同的结果。因此,如果您只对关系的结果感兴趣(为什么不?!)那么 NATURAL JOIN
是您需要的唯一联接类型。当然,从语言设计的角度来看,INNER JOIN
和 CROSS JOIN
等速记确实有其价值,但也考虑到几乎任何 SQL 查询都可以用 10 种语法不同但语义相同的方式编写,这就是使得 SQL 优化器很难开发。
以下是一些语义等效的示例查询(使用 the usual parts and suppliers database):
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;
** 关系自然连接不是等值连接,它是一个的投影。 – 菲利普西
NATURAL
连接只是 特定 INNER
连接的简短语法 - 或“等连接” - 并且,一旦语法被解包,两者都表示相同的关系代数运算。这不是一种“不同类型”的连接,例如 OUTER
(LEFT
/RIGHT
) 或 CROSS
连接。
请参阅 Wikipedia 上的 equi-join 部分:
自然连接提供了等连接的进一步专业化。连接谓词通过比较两个表中所有在连接表中具有相同列名的列而隐式出现。生成的连接表只包含每对同名列的一列。大多数专家都认为 NATURAL JOIN 很危险,因此强烈反对使用它们。危险来自于无意中添加了一个新列,与另一列命名相同......
也就是说,所有的 NATURAL
连接都可以写成 INNER
连接(但反过来不成立)。为此,只需显式地创建谓词——例如 USING
或 ON
——并且,正如 Jonathan Leffler 指出的那样,如果需要,选择所需的结果集列以避免“重复” .
快乐编码。
(NATURAL
关键字也可以应用于 LEFT
和 RIGHT
连接,同样适用。NATURAL LEFT/RIGHT
连接只是 特定 LEFT/RIGHT
连接的简短语法。 )
自然连接:是两个表中所有列的组合或组合结果。它将返回第一个表相对于第二个表的所有行。
内部联接:除非任何列名在两个表中都相同,否则此联接将起作用
自然连接是在所有公共列的基础上连接 2 个表。
公共列:是在两个表中具有相同名称的列+在两个表中具有兼容的数据类型。您只能使用 = 运算符
内连接是基于 ON 子句中提到的公共列连接 2 个表的地方。
公共列:是在两个表中具有兼容数据类型但不需要具有相同名称的列。您只能使用任何比较运算符,例如 =
、<=
、>=
、<
、>
、<>
自然联接:SQL 联接子句将关系数据库中 2 个或更多表的字段组合在一起。自然连接基于两个表中具有相同名称的所有列以及从两个表中选择的在所有匹配列中具有相同值的行。
--- 两列的名称和数据类型必须相同。
使用子句:在自然连接中,如果表中有同名但数据类型不同的列,则连接会导致错误。为避免这种情况,可以使用 USING 子句修改连接子句。 USING 子句指定应该用于连接的列。
不同之处在于 int 内(等/默认)连接和自然连接中的自然连接公共列 win 将单次显示,但内部/等/默认/简单连接公共列将显示两次。
内连接和自然连接几乎相同,但它们之间存在细微差别。不同之处在于自然连接不需要指定条件,但内部连接条件是强制性的。如果我们确实在内部连接中指定了条件,那么结果表就像一个笛卡尔积。
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)
内连接,连接两个列名相同的表。
自然连接,连接两个列名和数据类型相同的表。
NATURAL JOIN
(正如若干人多年前指出的那样)是列名相同的列名。数据类型不必相同。用于 INNER JOIN
的字段不必具有相同的名称。
NATURAL JOIN
究竟会毁掉什么,为什么会出人意料,以及您所处的世界是什么?Customers
和Employees
之间有一个自然连接,在EmployeeID
上连接。Employees
也有一个ManagerID
字段。一切安好。然后,有一天,有人在Customers
表中添加了一个ManagerID
字段。您的加入不会中断(那将是一个怜悯),相反,它现在将包含第二个字段,并且不正确地工作。因此,一个看似无害的变化可能会破坏一些只是遥远相关的东西。很坏。自然连接的唯一好处是节省了一点打字,而缺点是很大的。SELECT * FROM TableA INNER JOIN TableB USING (Column1)
给出了 4 列。这是不正确的,因为SELECT * FROM TableA INNER JOIN TableB USING (Column1)
和SELECT * FROM TableA NATURAL JOIN TableB
相等,它们都给出 3 列。