JOIN 和 WHERE 中过滤查询的区别? [英] Difference between filtering queries in JOIN and WHERE?

问题描述

在 SQL 中，我试图根据 ID 过滤结果，并想知道两者之间是否存在任何逻辑差异

SELECT 值从表 1JOIN table2 ON table1.id = table2.id哪里 table1.id = 1

和

SELECT 值从表 1JOIN table2 ON table1.id = table2.id AND table1.id = 1

对我来说，似乎逻辑是不同的，虽然你总是会得到相同的结果集，但我想知道是否有任何条件你会得到两个不同的结果集(或者它们总是返回完全相同的结果)两个结果集)

解决方案

答案是NO，但是:

我总是喜欢做以下事情.

• 始终保持ON 子句中的加入条件
• 总是把过滤器放在where子句

这使查询更可读.

所以我将使用这个查询:

SELECT 值从表 1内连接表2ON table1.id = table2.id哪里 table1.id = 1

但是，当您使用 OUTER JOIN'S 时，将过滤器保持在 ON 条件和 Where 条件下有很大的不同.>

逻辑查询处理

以下列表包含查询的一般形式，以及根据逻辑处理不同子句的顺序分配的步骤编号.

(5) SELECT (5-2) DISTINCT (5-3) TOP() (5-1) (1) FROM (1-J) <join_type>加入ON |(1-A)<left_table><apply_type>应用<right_table_expression>AS <别名>|(1-P) <left_table>PIVOT() AS <别名>|(1-U) <left_table>UNPIVOT() AS <别名>(2) WHERE<where_predicate>(3) GROUP BY<group_by_specification>(4) HAVING<have_predicate>(6) ORDER BY<order_by_list>;

流程图逻辑查询处理

• (1) FROM:FROM 阶段识别查询的源表和处理表操作符.每个表运算符应用一系列子阶段.例如，连接中涉及的阶段是 (1-J1)笛卡尔积，(1-J2) ON 过滤器，(1-J3) 添加外行.发件人阶段生成虚拟表VT1.

• (1-J1)笛卡尔积:此阶段执行笛卡尔积(交叉连接)表操作符涉及的两个表之间，生成 VT1-J1.

• (1-J2) ON Filter:这个阶段过滤来自 VT1-J1 的行基于出现在 ON 子句中的谓词 ().仅有的将谓词评估为 TRUE 的行插入到VT1-J2.
• (1-J3) 添加外行:如果指定了外连接(而不是CROSS JOIN 或 INNER JOIN)，保留表中的行未找到匹配项的行添加到来自 VT1-J2 的行中，作为外行，生成 VT1-J3.
• (2) WHERE:此阶段根据出现在 WHERE 子句中的谓词 ().仅有的将谓词评估为 TRUE 的行插入到 VT2 中.
• (3) GROUP BY:此阶段将来自 VT2 的行基于组进行排列在 GROUP BY 子句中指定的列列表上，生成 VT3.最终，每组将有一个结果行.
• (4) HAVING:此阶段根据出现在 HAVING 子句中的谓词 ().仅插入谓词评估为 TRUE 的组进入 VT4.
• (5) SELECT:这个阶段处理SELECT子句中的元素，生成 VT5.
• (5-1) 评估表达式:这个阶段评估表达式SELECT 列表，生成 VT5-1.
• (5-2) DISTINCT:这个阶段从 VT5-1 中删除重复的行，生成 VT5-2.
• (5-3) TOP:此阶段过滤指定的顶部数字或百分比来自 VT5-2 的行基于 ORDER 定义的逻辑顺序BY 子句，生成表 VT5-3.
• (6) ORDER BY:这个阶段根据 VT5-3 的行进行排序ORDER BY 子句中指定的列列表，生成游标VC6.

引用自《T-SQL Querying (Developer参考)"

In SQL I am trying to filter results based on an ID and wondering if there is any logical difference between

SELECT value 
FROM table1 
JOIN table2 ON table1.id = table2.id 
WHERE table1.id = 1

and

SELECT value 
FROM table1 
JOIN table2 ON table1.id = table2.id AND table1.id = 1

To me, it seems as if the logic is different though you will always get the same set of results but I wondered if there were any conditions under which you would get two different result sets (or would they always return the exact same two result sets)

解决方案

The answer is NO difference, but:

I will always prefer to do the following.

• Always keep the Join Conditions in ON clause
• Always put the filter's in where clause

This makes the query more readable.

So I will use this query:

SELECT value
FROM table1
INNER JOIN table2
        ON table1.id = table2.id
WHERE table1.id = 1

However when you are using OUTER JOIN'S there is a big difference in keeping the filter in the ON condition and Where condition.

Logical Query Processing

The following list contains a general form of a query, along with step numbers assigned according to the order in which the different clauses are logically processed.

(5) SELECT (5-2) DISTINCT (5-3) TOP(<top_specification>) (5-1) <select_list>
(1) FROM (1-J) <left_table> <join_type> JOIN <right_table> ON <on_predicate>
| (1-A) <left_table> <apply_type> APPLY <right_table_expression> AS <alias>
| (1-P) <left_table> PIVOT(<pivot_specification>) AS <alias>
| (1-U) <left_table> UNPIVOT(<unpivot_specification>) AS <alias>
(2) WHERE <where_predicate>
(3) GROUP BY <group_by_specification>
(4) HAVING <having_predicate>
(6) ORDER BY <order_by_list>;

Flow diagram logical query processing

• (1) FROM: The FROM phase identifies the query’s source tables and processes table operators. Each table operator applies a series of sub phases. For example, the phases involved in a join are (1-J1) Cartesian product, (1-J2) ON Filter, (1-J3) Add Outer Rows. The FROM phase generates virtual table VT1.

• (1-J1) Cartesian Product: This phase performs a Cartesian product (cross join) between the two tables involved in the table operator, generating VT1-J1.

• (1-J2) ON Filter: This phase filters the rows from VT1-J1 based on the predicate that appears in the ON clause (<on_predicate>). Only rows for which the predicate evaluates to TRUE are inserted into VT1-J2.
• (1-J3) Add Outer Rows: If OUTER JOIN is specified (as opposed to CROSS JOIN or INNER JOIN), rows from the preserved table or tables for which a match was not found are added to the rows from VT1-J2 as outer rows, generating VT1-J3.
• (2) WHERE: This phase filters the rows from VT1 based on the predicate that appears in the WHERE clause (). Only rows for which the predicate evaluates to TRUE are inserted into VT2.
• (3) GROUP BY: This phase arranges the rows from VT2 in groups based on the column list specified in the GROUP BY clause, generating VT3. Ultimately, there will be one result row per group.
• (4) HAVING: This phase filters the groups from VT3 based on the predicate that appears in the HAVING clause (<having_predicate>). Only groups for which the predicate evaluates to TRUE are inserted into VT4.
• (5) SELECT: This phase processes the elements in the SELECT clause, generating VT5.
• (5-1) Evaluate Expressions: This phase evaluates the expressions in the SELECT list, generating VT5-1.
• (5-2) DISTINCT: This phase removes duplicate rows from VT5-1, generating VT5-2.
• (5-3) TOP: This phase filters the specified top number or percentage of rows from VT5-2 based on the logical ordering defined by the ORDER BY clause, generating the table VT5-3.
• (6) ORDER BY: This phase sorts the rows from VT5-3 according to the column list specified in the ORDER BY clause, generating the cursor VC6.

it is referred from book "T-SQL Querying (Developer Reference)"

这篇关于JOIN 和 WHERE 中过滤查询的区别?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！