sql - INNER JOIN ON vs WHERE 子句

  显示原文与译文双语对照的内容

为简单起见,假设所有相关字段都不是空的。

你可以:


SELECT
 table1.this, table2.that, table2.somethingelse
FROM
 table1, table2
WHERE
 table1.foreignkey = table2.primarykey
 AND (some other conditions)

或者:


SELECT
 table1.this, table2.that, table2.somethingelse
FROM
 table1 INNER JOIN table2
 ON table1.foreignkey = table2.primarykey
WHERE
 (some other conditions)

这两个是由MySQL以同样方式工作的?

时间:

INNER JOINANSI 语法,你应该使用它。

通常认为更易读,尤其是当你加入大量表格时。

当需要时,也可以用 OUTER JOIN 轻松替换它。

WHERE 语法是面向关系模型的更多。

由于两个表 JOIN ed是一个笛卡儿积的表一个过滤器应用只选择那些行与加入列匹配。

使用 WHERE 语法来查看它更容易。

对于你的示例,在 MySQL ( 在 SQL 中通常) 中这两个查询是同义词。

还要注意 MySQL 也有一个 STRAIGHT_JOIN 子句。

使用这里子句,你可以控制 JOIN 顺序: 在外部循环中扫描哪个表,哪个在内部循环中扫描。

你不能在 MySQL 中使用 WHERE 语法控制这里操作。

另一些人指出内部联接有助于提高可读性,这是一个最高优先级;我同意。 让我试着解释为什么连接语法更具可读性。

基本选择查询如下:


SELECT stuff
FROM tables
WHERE conditions

select子句告诉我们我们回到;from子句告诉我们我们得到它,和where子句告诉我们的我们。

JOIN是关于表的声明,它们如何结合在一起( 概念上,实际上,转换成单个表) 。 控制表的任何查询元素- 我们从中获取内容- 语义上属于 from ( 当然,这就是加入元素的地方) clause子句。 把joining-elements到where子句 conflates 和 where-from;这就是为什么加入语法优先。

在/应用条件语句

这里我解释了逻辑查询处理步骤。



发布者:微软出版社
约会日期:2006年03月07日
打印 ISBN-10: 0 -7356-2313-9
打印 ISBN-13: 978 -0-7356-2313-2
页面:640


(8) SELECT (9) DISTINCT (11) TOP <top_specification> <select_list>
(1) FROM <left_table>
(3) <join_type> JOIN <right_table>
(2) ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(6) WITH {CUBE | ROLLUP}
(7) HAVING <having_condition>
(10) ORDER BY <order_by_list>

其他编程语言不同的第一个值得注意的方面是代码的处理顺序。 在大多数编程语言中,代码按照写入的顺序进行处理。 在SQL中,处理的第一个子句是from子句,而最先出现的SELECT子句几乎是最后一次处理。

每个步骤生成一个虚拟表,用作以下步骤的输入。 这些虚拟表对调用者( 客户端应用程序或者外部查询) 不可用。 只有最后一步生成的表才返回给调用者。 如果查询中未指定某个子句,则只跳过相应的步骤。

逻辑查询处理阶段的简短描述不会太多,如果步骤的描述对现在没有意义的话。 这些是作为参考提供的。 在场景示例之后的部分将更详细地介绍步骤。

  1. 从:一个笛卡尔乘积( 交叉联接) 在FROM子句的前两个表之间执行,因此生成虚拟表 VT1.

  2. 启用:将ON上的筛选ON应用到 VT1. 仅将为真的行插入到 VT2.

  3. 外( 加入) :如果一个( 与交叉联接或者内部联接相反) 指定外连接,从保存表行或表的匹配不存在被添加到行从VT2外行,生成 VT3 。 如果在from子句,中出现了两个上的表,则在最后一个联接的结果之间重复第 1到 3步的步骤,直到处理所有表。

  4. 其中:将过滤器应用于 VT3 。 仅将为真的行插入到 VT4.

  5. GROUP BY: VT4的行根据 GROUP BY 子句中指定的列列表排列在组中。 生成 VT5.

  6. 多维数据集| 汇总:Supergroups ( 组分组) 被添加到VT5的行,生成 VT6.

  7. 拥有:将筛选器应用到 VT6. 仅将为真的组插入到 VT7.

  8. 选择:处理选择列表,生成 VT8.

  9. DISTINCT: 从VT8删除重复的行。 生成 VT9.

  10. ORDER BY: VT9的行根据 ORDER BY 子句中指定的列列表排序。 生成游标( VC10 ) 。

  11. 顶部:从VC10开始选择指定的行数或者百分比。 表VT11生成并返回给调用者。



因此,( 内部联接) 在应用where子句clause之前将过滤数据( VT的数据计数将在这里减少) 。 随后的联接条件将使用筛选数据执行,这将提高性能。 之后,只有条件才会应用筛选条件。

( 在/处应用条件语句在少数情况下不会产生很大的差异) 。 这取决于你已经加入的表数和每个联接表中可用的行数

隐式联接ANSI语法较旧,不太明显,不推荐使用。

此外,关系代数允许互换性的谓词 WHERE 条款和 INNER JOIN, 所以即使 INNER JOINWHERE 条款可以查询谓词rearrranged优化器。

我建议你以尽可能多的readble方式编写查询。

有时这包括使 INNER JOIN 相对"不完整",并将一些条件放在 WHERE 中,使筛选条件列表更易于维护。

例如不是:


SELECT *
FROM Customers c
INNER JOIN CustomerAccounts ca
 ON ca.CustomerID = c.CustomerID
 AND c.State = 'NY'
INNER JOIN Accounts a
 ON ca.AccountID = a.AccountID
 AND a.Status = 1

写入:


SELECT *
FROM Customers c
INNER JOIN CustomerAccounts ca
 ON ca.CustomerID = c.CustomerID
INNER JOIN Accounts a
 ON ca.AccountID = a.AccountID
WHERE c.State = 'NY'
 AND a.Status = 1

但这当然取决于。

隐式连接( 这就是你的第一个查询被称为) 变得更加混乱,难以阅读,很难维持一旦你需要开始添加多个表查询。 假设在四个或者五个不同的表上执行相同的查询和类型的联接。。 这是一个噩梦。

使用显式联接( 你的第二个示例) 更易于阅读和维护。

我还将指出,使用旧语法更容易出错。 如果使用不带on子句的内部联接,则会得到语法错误。 如果你使用旧的语法和忘记的加入条件之一在where子句中,你会得到一个交叉连接。 开发人员常常解决这个问题通过添加不同的关键字( 因为它们仍然没有实现联接本身,而不是修复联接) 可能出现治愈问题,但将大大减缓查询。

另外维护如果你有一个交叉连接的语法,维护人员如何知道如果你想有一个( 在某些情况下需要交叉联接) 或者意外,应该是固定的?

让我指出这个问题,看看如果使用左联接,隐式语法是不好的。 *= 为Ansi标准,使用 2个不同的外部表,用于相同的内部表

加上( 这里的个人声音),使用显式联接的标准超过 20年,这意味着隐式连接语法已经过时了 20年。 你会使用已经过时 20年的语法编写应用程序代码? 你为什么要编写以下数据库代码?

他们的human-readable意思不同。

但是,根据查询优化器,它们可能对机器有相同的意义。

你应该总是代码易读。

也就是说,如果这是一个内置关系,使用显式联接。 如果你匹配弱相关数据,请使用where子句clause子句。

SQL:2003 标准更改了一些优先级规则,因此联接语句优先于"逗号"联接。 这实际上可以改变查询的结果,具体取决于它是如何设置的。 当 MySQL 5.0.12切换到标准时,一些人会遇到一些问题。

所以在你的例子中,你的查询会在。 但是如果你添加了第三个表: 从 table1.。中选择,table2在。上加入 table3 。JOIN 。 哪里。。

在 MySQL 5.0.12之前,table1和table2将首先加入,然后是 table3 。 现在( 5.0.12和上),table2和table3被加入,然后是 table1. 它并不总是改变结果,但它可以而且你甚至不能实现它。

我不再使用"逗号"语法,选择第二个示例。 这是很多更具可读性,连接的连接条件,不是分离到一个单独的查询部分。

我知道你在谈论 MySQL,但是无论如何: 在 Oracle 9中显式联接和隐式联接将生成不同的执行计划。 在 Oracle 10 + 中已经解决了这个问题: 不再有这样的差异。

ANSI连接语法绝对更具可移植性。

我正在升级微软 SQL Server,而且我还提到,SQL Server 中的外部联接的=*和 *= 语法在 2005 SQL Server 和更高版本中不支持( 没有兼容模式) 。

...