relational-database - theta join 和 inner join 有什么区别？

Question

theta join和 inner join 看起来和我一样:它们是笛卡尔积，然后是任意选择。还是我错过了他们的不同之处？谢谢。

上面的维基百科链接说 theta-join 有一个比较和两个属性。但这是不正确的。相反，theta-join 可以采用任何选择条件。

来自应该遵循SQL标准并且比维基百科更连贯和可靠的数据库系统概念:

The theta join operation is a variant of the natural-join operation that allows us to combine a selection and a Cartesian product into a single operation. Consider relations r(R) and s(S), and let theta be a predicate on attributes in the schema R ∪ S. The theta join operation r join_theta s is defined as follows:

r join_theta s = sigma_theta(r × s)

....

the join operations we studied earlier that do not preserve nonmatched tuples are called inner join operations, to distinguish them from the outer-join operations.

我仍然觉得它们是同一个概念。

Difference between a theta join, equijoin and natural join没有解释 theta 联接和内部联接之间的区别。

Answer 1

没有单一的“关系代数”。他们甚至在关系是什么方面也不同。 Codd 最初将 theta join 定义为采用二元运算符 (theta) 和两个属性。这就是人们通常所说的术语。摘自 Codd 1992 年出版的 The Relational Model for Database Management Version 2:

RB-14-RB-23 The Theta-Join Operator

The theta-join operator employs two R-tables as its operands. It generates as a result an R-table that contains rows of one operand (say S) concatenated with rows of the second operand (say T), but only where the specified condition is found to hold true. For brevity, this operator is often referred to as join.

The condition expressed in the join operator involves comparing each value from a column of S with each value from a column of T. The columns to be compared are indicated explicitly in the join command; these columns are called the comparand columns. This condition can involve any of the 10 comparators cited in the list [...]

(该术语通常用于此类运算符，但值可以代替属性。)

但 Codd 在那里自相矛盾，因为有“两个操作数”但也有“指定条件”。后来他又自相矛盾，说有一堆join统称为theta-joins，equi-join就是其中之一:

RB-25 The Natural Join Operator

As described in the last section, an equi-join generates a result in which two of the columns are identical in values, although different in column names. These two columns are derived from the comparand columns of the operands; of course, the columns may be either simple or composite. Of the 10 types of theta-join, equi-join is the only one that yields a result in which the comparand columns are completely redundant, one with the other. The natural join behaves just like the equi-join except that one of the redundant columns, simple or composite, is omitted from the result.

您引用的教科书使用该术语来指代不同的事物，其中 theta 是结合比较的 bool 表达式。这让人想起 SQL INNER JOIN ON complex conditions。对于 Codd，它是:

RB-24 The Boolean Extension of Theta-Join

请注意，您引用的教科书的 theta-join 不是代数运算符(将参数值映射到结果值)!它是一种非终结语言(其中比较表达式不表示值)。教科书实际上没有定义足够的代数运算符来实现其代数式查询语言。据称一组基本代数运算符包括 select 但它实际上只将其定义为一种语言非终端，因为一个操作数是一个条件表达式。

(这种草率是关系数据库教科书的典型特征。关系数据库领域存在一种模糊和困惑的文化。与该领域一样，该领域也是由 Codd 开创的。)