WHERE 子句最好在 IN 和 JOIN 之前或之后执行 [英] WHERE clause better execute before IN and JOIN or after

问题描述

我读了这篇文章:SELECT 语句的逻辑处理顺序

文末写了 ON 和 JOIN 子句考虑在 WHERE 之前.

in end of article has been write ON and JOIN clause consider before WHERE.

假设我们有一个包含 1000 万条记录的主表和一个包含 5000 万条记录的详细表(引用了主表(FK)).我们有一个查询，根据 PK 中的 PK 只需要 100 条详细表记录主表.

Consider we have a master table that has 10 milion recored and a detail table (that has reference to master table(FK)) with 50 milion record.we have a query that whant just 100 record of detail table according a PK in master table.

在这种情况下，ON 和 JOIN 在 WHERE 之前执行?我的意思是在 JOIN 之后我们有 5 亿条记录然后 WHERE 应用到它吗?还是先 WHERE 应用然后 JOIN 和 ON 考虑?如果第二个答案是真的，它有与顶级文章不一致?

In this situation ON and JOIN execute before WHERE?I mean that do we have 500 milion record after JOIN and then WHERE apply to it?or first WHERE apply and then JOIN and ON Consider?If second answer is true do it has incoherence with top article?

谢谢

推荐答案

没关系

始终遵循逻辑处理顺序:无论实际处理顺序如何

Logical processing order is always honoured: regardless of actual processing order

INNER JOIN 和 WHERE 条件是有效的关联和可交换的(因此是 ANSI-89在 where 中加入"语法)，因此实际顺序并不重要

INNER JOINs and WHERE conditions are effectively associative and commutative (hence the ANSI-89 "join in the where" syntax) so actual order doesn't matter

对于外连接和更复杂的查询，逻辑顺序变得很重要:在 OUTER 表上应用 WHERE 完全改变了逻辑.

Logical order becomes important with outer joins and more complex queries: applying WHERE on an OUTER table changes the logic completely.

同样，只要查询语义是按照逻辑处理顺序维护的，优化器内部如何做并不重要.

Again, it doesn't matter how the optimiser does it internally so long as the query semantics are maintained by following logical processing order.

这里的关键词是优化器":它完全按照它所说的去做

And the key word here is "optimiser": it does exactly what it says

这篇关于WHERE 子句最好在 IN 和 JOIN 之前或之后执行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！