自然连接必须在共享主键上吗? [英] Must a natural join be on a shared primary key?

问题描述

假设我执行了 A 自然连接 B，其中:

Suppose I perform A natural join B, where:

A 的架构是:(aID)，其中 (aID) 是主键.
B的schema是:(aID,bID)，其中(aID,bID)是复合主键.

A's schema is: (aID), where (aID) is a primary key.
B's schema is: (aID,bID), where (aID, bID) is a composite primary key.

在这种情况下会执行自然连接吗?还是 A 必须同时拥有 aID 和 bID 才能工作?

Would performing the natural join work in this case? Or is it necessary for A to have both aID and bID for this to work?

推荐答案

NATURAL JOIN 返回行，其中每个公共输入表列名称各一份副本，每个输入表唯一的列名称各一份副本.它返回一个包含所有这些行的表，这些行可以通过组合每个输入表中的一行来制作.这与有多少公共列名无关，包括零.当没有共同的列名时，这是一种 CROSS JOIN 又名 CARTESIAN JOIN.当所有列名都相同时，这就是一种 INTERSECTION.所有这一切都与 PK、UNIQUE、FK 和 PK 无关.其他限制条件.

NATURAL JOIN returns rows with one copy each of the common input table column names and one copy each of the column names that are unique to an input table. It returns a table with all such rows that can be made by combining a row from each input table. That is regardless of how many common column names there are, including zero. When there are no common column names, that is a kind of CROSS JOIN aka CARTESIAN JOIN. When all the column names are common, that is a kind of INTERSECTION. All this is regardless of PKs, UNIQUE, FKs & other constraints.

NATURAL JOIN 作为关系代数运算符很重要.在 SQL 中，它可以用于某种关系编程风格，在某种意义上比平常更简单.

NATURAL JOIN is important as a relational algebra operator. In SQL it can be used in a certain style of relational programming that is in a certain sense simpler than usual.

对于真正的关系结果，您将选择 DISTINCT.关系也没有特殊的 NULL 值，而 SQL JOIN 将 NULL 视为不等于 NULL；因此，如果我们将 NULL 视为另一个关系值，那么 SQL 有时不会返回真正的关系结果.(当两个参数对于某些共享列中的每一个都具有 NULL 并且对于每个其他共享列都具有相同的非 NULL 值时.)

For a true relational result you would SELECT DISTINCT. Also relations have no special NULL value whereas SQL JOINs treat a NULL as not equal to a NULL; so if we treat NULL as just another value relationally then SQL will sometimes not return the true relational result. (When both arguments have a NULL for each of some shared columns and both have the same non-NULL value for each other shared column.)

这篇关于自然连接必须在共享主键上吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！