使用where代替在SQL查询的join子句中添加条件会更好吗? [英] Is it better to use where instead of adding conditions into join clause in SQL queries?

问题描述

你好:)我对MySQL查询有疑问. 哪个更快，为什么? 有什么区别吗?

Hello :) I've got a question on MySQL queries. Which one's faster and why? Is there any difference at all?

select tab1.something, tab2.smthelse
from tab1 inner join tab2 on tab1.pk=tab2.fk 
WHERE tab2.somevalue = 'value'

或者这个:

select tab1.something, tab2.smthelse
from tab1 inner join tab2 on tab1.pk=tab2.fk 
AND tab2.somevalue = 'value'

推荐答案

正如Simon所言，性能差异应该可以忽略不计.主要要考虑的是确保您的查询正确表达了您的意图，并且(特别是)您获得了预期的结果.

As Simon noted, the difference in performance should be negligible. The main concern would be ensuring your query correctly expresses your intent, and (especially) you get the expected results.

通常，仅当过滤器是联接的条件时，才希望将过滤器添加到JOIN子句中.在大多数(并非全部)情况下，应将过滤器应用于WHERE子句，因为它是整体查询的过滤器，而不是联接本身的过滤器.

Generally, you want to add filters to the JOIN clause only if the filter is a condition of the join. In most (not all) cases, a filter should be applied to the WHERE clause, as it is a filter of the overall query, not of the join itself.

AFAIK，唯一真正影响查询结果的实例是使用OUTER JOIN时.

AFAIK, the only instance where this really affects the outcome of the query is when using an OUTER JOIN.

请考虑以下查询:

SELECT *
FROM Customer c
LEFT JOIN Orders o ON c.CustomerId = o.CustomerId
WHERE o.OrderType = "InternetOrder"

vs.

SELECT *
FROM Customer c
LEFT JOIN Orders o ON c.CustomerId = o.CustomerId AND o.OrderType = "InternetOrder"

对于每个具有"Internet Order"订单类型的客户订单，第一个将返回一行.实际上，由于已将过滤器应用于整个查询，因此您的左联接已成为内部联接(即，根本不会返回没有"InternetOrder"的客户).

The first will return one row for each customer order that has an order type of "Internet Order". In effect, your left join has become an inner join because of the filter that was applied to the whole query (i.e. customers who do not have an "InternetOrder" will not be returned at all).

第二个将为每个客户返回至少一行.如果客户没有订单类型为"Internet订单"的订单，则它将为所有订单表字段返回空值.否则，它将为"Internet Order"类型的每个客户订单返回一行.

The second will return at least one row for each customer. If the customer has no orders of order type "Internet Order", it will return null values for all order table fields. Otherwise it will return one row for each customer order of type "Internet Order".

这篇关于使用where代替在SQL查询的join子句中添加条件会更好吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！