将 sql 连接的 "ON" 语句与 "AND" 而不是 "WHERE" 一起使用

Question

所以我想知道哪一个在性能方面最好，而且在这里使用and是否只是一种糟糕的做法。

比较以下两个查询结束：

在末尾使用"WHERE"：

select c.cust_last_name,
       o.order_total,
       oi.quantity
from   customers c 
       join orders o on (c.customer_id = o.customer_id)
       join order_items oi on (o.order_id = oi.order_id)
where  c.GENDER='M';

在末尾使用"AND"：

select c.cust_last_name,
       o.order_total,
       oi.quantity
from   customers c 
       join orders o on (c.customer_id = o.customer_id)
       join order_items oi on (o.order_id = oi.order_id and c.GENDER='M');

和基于最后一个on的条件来检索与第一个查询完全相同的数据集。这样可以吗？

Answer 1

在这种情况下，我怀疑您使用的查询版本对Oracle有多大影响。您可以查看每个查询的解释计划进行检查。

但是，将c.gender = 'M'谓词移动到这里的联接条件中是"安全的"，因为您正在执行内部联接。如果您正在执行外部联接，您会看到不同的结果，这取决于谓词是否在where或联接子句中。

Answer 2

将条件放入WHERE子句还是ON子句没有区别。

但是，是的，您向显示的是错误做法，因为and c.GENDER='M'与从表order_items加入哪些记录无关。ON子句中的条件应始终属于其表。

订单项目表上有附加标准的一个例子是

join order_items oi on (o.order_id = oi.order_id and and oi.price > 50)

如果您想在ON子句或WHERE子句中看到这一点，这或多或少是个人偏好的问题。您可能会争辩说，您根据订单ID连接表，然后只有以高于50的价格保留结果，因此连接仅针对ID。或者你可以争辩说，你加入订购价格>50的商品。这两种说法在语义上都是正确的。

然而，在on子句中总是将所有标准都放在表上是一个好习惯。当您更改时

inner join order_items oi on (o.order_id = oi.order_id)
where oi.price > 50

至

left join order_items oi on (o.order_id = oi.order_id)
where oi.price > 50

这实际上仍然是一个内部联接，因为外部联接的记录的价格为NULL，不符合WHERE子句的条件，所以您应该在创建记录后立即删除这些记录：-）因此，由于其他联接类型，您必须将条件移到ON子句。把它放在那里不是更好吗？

Answer 3

我认为，除非表中有大量记录，否则性能上的差异不会太大。

第一种情况是首先根据内部联接获取所有相关记录，然后根据男性进行过滤。您正在加载所有记录，其中哪些部分不相关（女性），然后进行筛选。

在第二种情况下，根本不会收集不相关的记录，过滤是作为联接操作的一部分完成的。

我同意@Radu Gheorghiu的观点，你可能想把c.GENDER='M'条件提高一级。