SQL JOIN - WHERE 子句与 ON 子句 [英] SQL JOIN - WHERE clause vs. ON clause

问题描述

阅读后，这不是Explicit vs隐式 SQL 连接.答案可能相关(甚至相同)，但问题是不同的.

After reading it, this is not a duplicate of Explicit vs Implicit SQL Joins. The answer may be related (or even the same) but the question is different.

有什么区别，每个应该包含什么?

What is the difference and what should go in each?

如果我正确理解了理论，查询优化器应该能够交替使用两者.

If I understand the theory correctly, the query optimizer should be able to use both interchangeably.

推荐答案

它们不是一回事.

考虑这些查询:

SELECT *
FROM Orders
LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID
WHERE Orders.ID = 12345

和

SELECT *
FROM Orders
LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID 
    AND Orders.ID = 12345

第一个将返回订单号12345 的订单及其行(如果有).第二个将返回所有订单，但只有订单 12345 将有任何与之关联的行.

The first will return an order and its lines, if any, for order number 12345. The second will return all orders, but only order 12345 will have any lines associated with it.

使用INNER JOIN，子句有效等效.但是，仅仅因为它们在功能上相同，产生相同的结果，并不意味着这两种子句具有相同的语义.

With an INNER JOIN, the clauses are effectively equivalent. However, just because they are functionally the same, in that they produce the same results, does not mean the two kinds of clauses have the same semantic meaning.

这篇关于SQL JOIN - WHERE 子句与 ON 子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！