在阅读它之后,这不是显式与隐式SQL连接的副本。 答案可能相关(甚至相同),但问题是不同的。
它们之间有什么不同?每一种都应该有什么不同?
如果我正确地理解了这个理论,那么查询优化器应该能够互换地使用这两种方法。
在阅读它之后,这不是显式与隐式SQL连接的副本。 答案可能相关(甚至相同),但问题是不同的。
它们之间有什么不同?每一种都应该有什么不同?
如果我正确地理解了这个理论,那么查询优化器应该能够互换地使用这两种方法。
当前回答
从字面上看,它们是等价的。
在大多数开源数据库(最著名的例子是MySql和postgresql)中,查询计划是出现在关系数据库管理系统中的访问路径选择(Selinger et al, 1979)中的经典算法的变体。在这种方法中,条件有两种类型
引用单个表的条件(用于筛选) 引用两个表的条件(不管它们出现在哪里,都被视为连接条件)
特别是在MySql中,通过跟踪优化器,您可以看到自己的join ..On条件在解析过程中被等价的where条件替换。类似的事情也发生在postgresql中(虽然没有办法通过日志看到它,你必须阅读源代码描述)。
无论如何,主要的一点是,这两种语法变体之间的差异在解析/查询-重写阶段就消失了,甚至没有达到查询计划和执行阶段。因此,就性能而言,它们是否相同是毫无疑问的,它们在进入执行阶段之前就已经完全相同了。
你可以用explain来验证他们的计划是否相同。例如,在postgres中,计划将包含一个join子句,即使你没有在语法中使用join..on。
Oracle和SQL server不是开源的,但是,据我所知,它们基于等价规则(类似于关系代数中的规则),并且它们在这两种情况下也产生相同的执行计划。
显然,对于外部连接,这两种语法风格是不相等的,对于那些你必须使用join…在语法
其他回答
通常,一旦两个表已经连接,就在WHERE子句中处理筛选。这是有可能的,不过您可能希望在连接它们之前过滤一个或两个表。 也就是说,where子句适用于整个结果集,而on子句只适用于相关的连接。
让我们考虑一下这些表格:
A
id | SomeData
B
id | id_A | SomeOtherData
id_A是表a的外键
写这个查询:
SELECT *
FROM A
LEFT JOIN B
ON A.id = B.id_A;
将提供如下结果:
/ : part of the result
B
+---------------------------------+
A | |
+---------------------+-------+ |
|/////////////////////|///////| |
|/////////////////////|///////| |
|/////////////////////|///////| |
|/////////////////////|///////| |
|/////////////////////+-------+-------------------------+
|/////////////////////////////|
+-----------------------------+
A中有而B中没有的东西意味着B中有空值。
现在,让我们考虑B.id_A中的一个特定部分,并从前面的结果中突出显示它:
/ : part of the result
* : part of the result with the specific B.id_A
B
+---------------------------------+
A | |
+---------------------+-------+ |
|/////////////////////|///////| |
|/////////////////////|///////| |
|/////////////////////+---+///| |
|/////////////////////|***|///| |
|/////////////////////+---+---+-------------------------+
|/////////////////////////////|
+-----------------------------+
写这个查询:
SELECT *
FROM A
LEFT JOIN B
ON A.id = B.id_A
AND B.id_A = SpecificPart;
将提供如下结果:
/ : part of the result
* : part of the result with the specific B.id_A
B
+---------------------------------+
A | |
+---------------------+-------+ |
|/////////////////////| | |
|/////////////////////| | |
|/////////////////////+---+ | |
|/////////////////////|***| | |
|/////////////////////+---+---+-------------------------+
|/////////////////////////////|
+-----------------------------+
因为这将在内部连接中删除不在B.id_A = SpecificPart中的值
现在,让我们把查询改为这样:
SELECT *
FROM A
LEFT JOIN B
ON A.id = B.id_A
WHERE B.id_A = SpecificPart;
结果是:
/ : part of the result
* : part of the result with the specific B.id_A
B
+---------------------------------+
A | |
+---------------------+-------+ |
| | | |
| | | |
| +---+ | |
| |***| | |
| +---+---+-------------------------+
| |
+-----------------------------+
因为整个结果是根据B. id_a = SpecificPart过滤的,删除了B. id_a为NULL的部分,这些部分在A中,但不在B中
为了补充Joel Coehoorn的回答,我将添加一些特定于sqlite的优化信息(其他SQL风格的行为可能不同)。在原来的例子中,LEFT JOIN有不同的结果,这取决于你是否使用JOIN on…在哪里或加入…和。下面是一个稍微修改过的例子:
SELECT *
FROM Orders
LEFT JOIN OrderLines ON Orders.ID = OrderLines.OrderID
WHERE Orders.Username = OrderLines.Username
与
SELECT *
FROM Orders
LEFT JOIN OrderLines ON Orders.ID = OrderLines.OrderID
AND Orders.Username = OrderLines.Username
现在,最初的答案指出,如果使用普通的内部连接而不是左连接,两个查询的结果将是相同的,但执行计划将有所不同。我最近意识到,两者之间的语义差异是,前者强制查询优化器使用与ON子句关联的索引,而后者允许优化器选择ON子句中的任何索引……AND子句,这取决于它认为最有效的方式。
有时,优化器会猜错,您会想强制执行某个执行计划。在本例中,假设SQLite优化器错误地得出结论,认为执行此连接的最快方法是使用Orders上的索引。用户名,当您从经验测试中知道,对订单的索引。ID可以更快地传递查询。
在这种情况下,前者JOIN ON…WHERE语法实际上允许您强制对ID参数执行主连接操作,只有在主连接完成之后才对Username执行辅助过滤。相反,JOIN ON…AND语法允许优化器选择是否在Orders上使用索引。ID或订单。用户名,理论上有可能它会选择一个速度较慢的。
在INNER join中,它们是可互换的,优化器会随意重新排列它们。
在OUTER连接上,它们不一定是可互换的,这取决于它们所依赖的连接的哪一侧。
我把它们放在任意一个位置,取决于可读性。
为了获得更好的性能,表应该有一个用于join的特殊索引列。
所以如果你的条件列不是那些索引列之一,那么我怀疑最好把它保存在WHERE。
所以你使用索引列来JOIN,然后在JOIN之后你在无索引列上运行条件。